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

The combo can filter on 2 fields if you build the Filter string for the
form, but probably not if you put the criteria in the query.

To filter your form so that Combo1 matches the value in either Field1 or
Field2, you would use code like this:
Dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "(Field1 = " & Me.Combo1 & ") OR (Field2 = " & Me.Comob1
& ")"
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

--
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:802BD423-9E61-4769-9355-130D785A1F87@xxxxxxxxxxxxxxxx
Allen, thanks for getting back to me on this. I have another concern that
may or may not be an issue regarding the design of this database...

First, I created a select query called Delivery Summary ALL, which is
based
on an Invoice table and a Delivery Time table. Next, I created another
select query called Delivery Group based on the Group Name table and the
Delivery Summary ALL select query. From there, I created the Delivery By
Group Form (autoform format), which is based on the Delivery Group query
where the Group Name is to be filtered by the combo box dropdown list.
It's
Rowsource (in the combo box) points to the Group Name table (for the
account
# and group name).

Question - can the combo box filter on group name on the form if both
fields
are pointing to the Group Name table??? Please advise...


Thanks Lesley


"Allen Browne" wrote:

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

"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