search with 2 criteria

[Mark Carlyle via AccessMonster.com]

I made drop down boxes that search by a criteria and then filters by that
selection.. but I need the second to only search the filtered data.

What I have so far is each searches the entire recordset.

first search and filter


' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Bank State] = '" & Me![Combo19] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

DoCmd.GoToControl "bank state"

DoCmd.RunCommand (acCmdFilterBySelection)



second search and filter

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Bank Name] = '" & Me![Combo30] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

DoCmd.GoToControl "bank name"

DoCmd.RunCommand (acCmdFilterBySelection)

each of these are from drop down boxes... I would like to make the second one
only search the data that was filtered by the first one.

 

[Guest]

As you want to filter the form rather than just navigate to a record I'd
suggest different approach, setting the form's Filter property and then
activating the filter by setting the FilterOn property to True. So the code
for the first combo box would be:

Dim strCriteria As String

strCriteria = "[Bank State] = '" & Me![Combo19] & "'"

Me.Filter = strCriteria
Me.FilterOn = True

For the second combo box extend the filter so that it combines the
selections from both combo boxes:

Dim strCriteria As String

' first make sure a selection has been made in Combo 19
If Not IsNull(Me.[Combo 19] Then
strCriteria = "[Bank State] = '" & Me![Combo19] & "' And "
End If

' add selection in Combo 30 to filter
strCriteria = strCritera & "[Bank Name] = '" & Me![Combo30] & "'"

Me.Filter = strCriteria
Me.FilterOn = True

To deactivate the filter and show all records you can either use the built
in toolbar button or add a 'Show All' button to your form with the following
in its Click event procedure:

Me.FilterOn = False

BTW I'd advise that when you add controls to a form or report you rename
them to something meaningful rather than accept the defaults which Access
gives them, e.g. cboBankState and cboBankName. Do this before you write any
event procedure for the control, however; if you do it afterwards the link
between the code and the control will be lost.

 

[Mark C via AccessMonster.com]

I changed the code to this; however, now when I search for state it filters
by state, but then if the bank isnt in the state it finds it anyway. I only
want it to return banks in that state. If there are none I can do a MSGbox
or something.

any ideas?

As you want to filter the form rather than just navigate to a record I'd
suggest different approach, setting the form's Filter property and then
activating the filter by setting the FilterOn property to True. So the code
for the first combo box would be:

Dim strCriteria As String

strCriteria = "[Bank State] = '" & Me![Combo19] & "'"

Me.Filter = strCriteria
Me.FilterOn = True

For the second combo box extend the filter so that it combines the
selections from both combo boxes:

Dim strCriteria As String

' first make sure a selection has been made in Combo 19
If Not IsNull(Me.[Combo 19] Then
strCriteria = "[Bank State] = '" & Me![Combo19] & "' And "
End If

' add selection in Combo 30 to filter
strCriteria = strCritera & "[Bank Name] = '" & Me![Combo30] & "'"

Me.Filter = strCriteria
Me.FilterOn = True

To deactivate the filter and show all records you can either use the built
in toolbar button or add a 'Show All' button to your form with the following
in its Click event procedure:

Me.FilterOn = False

BTW I'd advise that when you add controls to a form or report you rename
them to something meaningful rather than accept the defaults which Access
gives them, e.g. cboBankState and cboBankName. Do this before you write any
event procedure for the control, however; if you do it afterwards the link
between the code and the control will be lost.

I made drop down boxes that search by a criteria and then filters by that
selection.. but I need the second to only search the filtered data.

[quoted text clipped - 30 lines]

each of these are from drop down boxes... I would like to make the second one
only search the data that was filtered by the first one.

 

[Guest]

The state must still be selected in Combo 19 when you select a bank in Combo
30. The string expression for the Filter property should then evaluate to
something like:

[Bank State] = 'CA' And [Bank Name] = 'HSBC'

which by virtue of the Boolean 'And' should only return rows where both
criteria apply, i.e. the bank name must be HSBC and it must be in California.
You can check that this is happening by debugging the code. Set a break
point somewhere early in Combo 30's event and step into the code, making sure
all the lines you expect to execute do so, and examining the value of the
strCriteria variable as its built up.

To make sure you can only select a bank in the selected state you can
correlate the two combo boxes so that Combo 30 only lists banks in the
selected State, or if no state is selected, in all states. To do this make
the RowSource property of Combo 30 a query which references Combo 19, e.g.

SELECT [Bank Name]
FROM Banks
WHERE [Bank State] = Forms![Your Form]![Combo 19]
OR Forms![Your Form]![Combo 19] IS NULL
ORDER BY [Bank Name];

In the AfterUpdate event procedure of Combo 19 requery Combo 30 with:

Me.[Combo 30].Requery

 

[Mark Carlyle via AccessMonster.com]

Ok, I put this in... but my form [ACH Lookup] is a subform of a form called
[greybar]. When I change the [your form] box in your example to [ACJ Lookup]
it does not find the control. I think it cannot find it because it is a
subform. How can I fix this?

The state must still be selected in Combo 19 when you select a bank in Combo
30. The string expression for the Filter property should then evaluate to
something like:

[Bank State] = 'CA' And [Bank Name] = 'HSBC'

which by virtue of the Boolean 'And' should only return rows where both
criteria apply, i.e. the bank name must be HSBC and it must be in California.
You can check that this is happening by debugging the code. Set a break
point somewhere early in Combo 30's event and step into the code, making sure
all the lines you expect to execute do so, and examining the value of the
strCriteria variable as its built up.

To make sure you can only select a bank in the selected state you can
correlate the two combo boxes so that Combo 30 only lists banks in the
selected State, or if no state is selected, in all states. To do this make
the RowSource property of Combo 30 a query which references Combo 19, e.g.

SELECT [Bank Name]
FROM Banks
WHERE [Bank State] = Forms![Your Form]![Combo 19]
OR Forms![Your Form]![Combo 19] IS NULL
ORDER BY [Bank Name];

In the AfterUpdate event procedure of Combo 19 requery Combo 30 with:

Me.[Combo 30].Requery

I changed the code to this; however, now when I search for state it filters
by state, but then if the bank isnt in the state it finds it anyway. I only
want it to return banks in that state. If there are none I can do a MSGbox
or something.

any ideas?

 

[Guest]

A subform is not part of the Forms collection, in which only open parent
forms are included. You can refer to a subform, however, via the Form
property of the subform control in the parent form, so you could use:

SELECT [Bank Name]
FROM Banks
WHERE [Bank State] = Forms![GreyBar]![SubFormControl].Form![Combo 19]
OR Forms![GreyBar]![SubFormControl].Form![Combo 19] IS NULL
ORDER BY [Bank Name];

Note that SubFormControl here would be the name of the subform control in
the parent form GreyBar, i.e. the control which contains the subform, not the
name of the underlying form object, though both can have the same name.

However, a simpler way is just to use the Form property on its own. As the
query is called from within the subform this returns a reference to it. The
query would not open independently of the subform, however, but that's not a
problem:

SELECT [Bank Name]
FROM Banks
WHERE [Bank State] = Form![Combo 19]
OR Form![Combo 19] IS NULL
ORDER BY [Bank Name];

 

[Mark Carlyle via AccessMonster.com]

Ahhh finally thanks... the subform thing was messing me up before as well.
Great info for me to know.

for future reference i just use

forminfo.subforminfo![control]

thanks for the help!

A subform is not part of the Forms collection, in which only open parent
forms are included. You can refer to a subform, however, via the Form
property of the subform control in the parent form, so you could use:

SELECT [Bank Name]
FROM Banks
WHERE [Bank State] = Forms![GreyBar]![SubFormControl].Form![Combo 19]
OR Forms![GreyBar]![SubFormControl].Form![Combo 19] IS NULL
ORDER BY [Bank Name];

Note that SubFormControl here would be the name of the subform control in
the parent form GreyBar, i.e. the control which contains the subform, not the
name of the underlying form object, though both can have the same name.

However, a simpler way is just to use the Form property on its own. As the
query is called from within the subform this returns a reference to it. The
query would not open independently of the subform, however, but that's not a
problem:

SELECT [Bank Name]
FROM Banks
WHERE [Bank State] = Form![Combo 19]
OR Form![Combo 19] IS NULL
ORDER BY [Bank Name];

Ok, I put this in... but my form [ACH Lookup] is a subform of a form called
[greybar]. When I change the [your form] box in your example to [ACJ Lookup]
it does not find the control. I think it cannot find it because it is a
subform. How can I fix this?

 

[Guest]

That works because the Form property is the default property of a subform
control, so can be omitted.