Using multiple comboboxes to filter data

[Dan Neely]

I'm trying to use two comboboxes to filter date on a subform. Both
the parent and child forms have the same table as their data source.
The cb's on the parent are set to the two FKs I want to use to filter
the data on, and those FKs are used to link data between the forms.
To filter on a single parameter, with a single field linked I can use
the wizard generated code below.

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FK1ID] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

With two fields linked though it always uses the first FK for the
second field, instead of ignoring it entirely.

My attempt to modify the code to filter on both CB values has been
unsuccessful. Adding the & to the second half of the expression got
it to compile without error, but it always returns the rows that have
1 for both keys regardless of the actual selections.

rs.FindFirst "[FK1ID] = " & Str(Nz(Me![Combo11], 0)) & "& [FK2ID]
= " & Str(Nz(Me![Combo6], 0))

 

[Marshall Barton]

I'm trying to use two comboboxes to filter date on a subform. Both
the parent and child forms have the same table as their data source.
The cb's on the parent are set to the two FKs I want to use to filter
the data on, and those FKs are used to link data between the forms.
To filter on a single parameter, with a single field linked I can use
the wizard generated code below.

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FK1ID] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

With two fields linked though it always uses the first FK for the
second field, instead of ignoring it entirely.

My attempt to modify the code to filter on both CB values has been
unsuccessful. Adding the & to the second half of the expression got
it to compile without error, but it always returns the rows that have
1 for both keys regardless of the actual selections.

rs.FindFirst "[FK1ID] = " & Str(Nz(Me![Combo11], 0)) & "& [FK2ID]
= " & Str(Nz(Me![Combo6], 0))

Use AND instead of &

rs.FindFirst "[FK1ID] = " & Str(Nz(Me![Combo11], 0)) & " AND
[FK2ID] = " & Str(Nz(Me![Combo6], 0))

 

[Dan Neely]

Private Sub Combo11_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FK1ID] = " & Str(Nz(Me![Combo11], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Use AND instead of &

DOH!

One more question, how can I modify this to show no records if the
filters fail to match anything? I tried removing the conditional
from the bookmark assignment, but it still fails to update the child
form to show zero records if there aren't any matches.

 

[Marshall Barton]

One more question, how can I modify this to show no records if the
filters fail to match anything? I tried removing the conditional
from the bookmark assignment, but it still fails to update the child
form to show zero records if there aren't any matches.

Whoa there. That code is not filtering anything, it is
navigating to the first matching record. Maybe you should
back up a step and describe your objective.

I just noticed that using FindFirst menas that you are using
DAO, but the check for no matching records is the ADO way of
doing it. Presuming that you are using DAO, the line should
be:
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

 

[Dan Neely]

Whoa there. That code is not filtering anything, it is
navigating to the first matching record. Maybe you should
back up a step and describe your objective.

I just noticed that using FindFirst menas that you are using
DAO, but the check for no matching records is the ADO way of
doing it. Presuming that you are using DAO, the line should
be:
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

What I was doing was to have a parent form with 2 combo boxes, and to
have the child only show records that matched the selections. As long
as it was finding at least one record though it did filter the child
datasheet form. Why/how I don't know, i coaxed that code out of a
wizard somehow.

I ended up getting it to work as intended by using rs.NoMatch to turn
on/off a filter that will never match anything (an autonumber field
being equal to -1).

 

[Marshall Barton]

What I was doing was to have a parent form with 2 combo boxes, and to
have the child only show records that matched the selections. As long
as it was finding at least one record though it did filter the child
datasheet form. Why/how I don't know, i coaxed that code out of a
wizard somehow.

I ended up getting it to work as intended by using rs.NoMatch to turn
on/off a filter that will never match anything (an autonumber field
being equal to -1).

This is spinning out of control. If you are using both a
Filter and the code you posted, you should scrap this code
and use just the filter.

On the other hand, as I read your situation, this kind of
thing does not require a filter or any code. Instead, you
should be using the Link Master/Child Fields properties. I
think you should at least try setting the Link Master
property to:
combo1,combo2
and the Link Child property to:
FK1ID,FK2ID