Re: use combo box to filter data in a form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Presumably you have tables like this:
Company table (one record for each company), with fields:
CompanyID AutoNumber primary key
CompanyName Text name of the company
...
Delivery table (one record for each delivery), with fields:
DeliveryID AutoNumber primary key
DeliveryDate Date/Time when delivered
CompanyID Number relates to Company.CompanyID
...
You probably have other tables, such as DeliveryDetail table where you list
the items in the delivery.

In the Form Header of your delivery form, you have an unbound combo to
filter it to just one company. The combo will have properties:
Control Source {This must be blank!}
Row Source SELECT CompanyID, CompanyName FROM Company;
Bound Column 1
Column Count 2
Column Widths 0
After Update [Event Procedure]
Name cboFilterCompany

Then you clicked the Build button (...) beside the AfterUpdate property, and
added code to set the Filter of the form:
Private Sub cboFilterCompany_AfterUpdate
If Not IsNull(Me.cboFilterCompany) Then
If Me.Dirty Then Me.Dirty = False
Me.Filter = "[CompanyID] = " & Me.cboFilterCompany
Me.FilterOn = True
End If
End Sub

Once you have that working, you can open Report1 with the same filter, by
adding a command button, and putting code like this into its On Click event
procedure:
Private Sub cmdPreview_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lesley" <Lesley@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0C22972C-2F62-47E7-80E8-220F5035A3E8@xxxxxxxxxxxxxxxx
Hi! I need to create a report to show deliveries by a company name. I
started this process by using a form displaying all deliveries and added a
combo box in the header to filter all deliveries made to a specific
company.
I'm having trouble linking the selected company name to the records
displayed
in the form to filter out just those deliveries made to the selected
company.


I later need this to be in report format. Am I doing this right? Is
there
an easier way to just do this in report format or do I need to start with
a
form and later convert the data into a report?


.

Quantcast