Opening a form to filter Data (VBA)

[CT_AccessHelp]

I have an old database we are trying to resurrect.



The database has 2 forms, one is used to open the other based on selected criteria.

I have added a Practice list box to the form used to open the second form. The button on the first form currently opens the second form and queries the data to show up in the second form by county based on the county selected in the combo box. I have added the second practice field on the first form but I cannot figure out how to edit the code so it now queries the data in the second form by county and practice. Basically if I pick Anoka and CP23 I only want to see the entered CP23s entered for Anoka on the second form. Any Ideas? Thank you!





Current Code tied to the button on the first form:


Private Sub Command3_Click()
On Error GoTo Err_Command3_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_CountyRecords"
stLinkCriteria = "[County]=" & "'" & Me![Combo1] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command3_Click:
Exit Sub

Err_Command3_Click:
MsgBox Err.Description
Resume Exit_Command3_Click

End Sub

[pbaldy]

Add the second field to the stLinkCriteria variable, separated from the first field by AND.

[CT_AccessHelp]

Add the second field to the stLinkCriteria variable, separated from the first field by AND.

I'm just winging it on the VBA stuff, so any help you can provide is helpful - Thank you

Like this? stLinkCriteria = "[County]=" And "[Practice]=" & "'" & Me![Combo1] & "'" And "'" & Me!
[List20] & "'"

I tried that and got a "mismatched type" message

[pbaldy]

Well, the final outcome you're looking for is:

"Field1 = 'abc' AND Field2 = 'def'"

so more like

stLinkCriteria = "[County]= '" & Me![Combo1] & "' And [Practice]='" & Me!

[List20] & "'"

[CT_AccessHelp]

Well, the final outcome you're looking for is:

"Field1 = 'abc' AND Field2 = 'def'"

so more like

stLinkCriteria = "[County]= '" & Me![Combo1] & "' And [Practice]='" & Me!

[List20] & "'"

Thank you for the example - I tired it and got the type mismatched message again

[pbaldy]

Try this to see what the finished string is:

http://www.baldyweb.com/ImmediateWindow.htm

and what are the data types of the 2 fields? They look like text, but are they lookup fields or anything?

[CT_AccessHelp]

Try this to see what the finished string is:

http://www.baldyweb.com/ImmediateWindow.htm

and what are the data types of the 2 fields? They look like text, but are they lookup fields or anything?

Both Combo boxes look up to separate tables and both data types are text. Super frustrating I can't get it to work out

[pbaldy]

So you didn't try the debugging method? Can you attach the db here?

[CT_AccessHelp]

So you didn't try the debugging method? Can you attach the db here?

Yea, it did nothing, maybe I did it wrong. Is there a way to attached it? It is actually 2 databases, tables are split in the backend. Thing is a mess LOL

I have tried the code with only "[County]=" & "'" & Me![Combo1] & "'" and it works with the County and then with stLinkCriteria = "[Practice]=" & "'" & Me![Combo22] & "'" (I changed it to a combo box to see if that helped, didn't help) only and it works with the Practice but together stLinkCriteria = "[County]=" & "'" & Me![Combo1] & "'" And "[Practice]=" & "'" & Me![Combo22] & "'" they won't work together, I get the type mismatched error.

[pbaldy]

It can be attached in the "Go Advanced" area. If each worked separately my code earlier should have worked. You've got the quotes off. Try

stLinkCriteria = "[County]=" & "'" & Me![Combo1] & "' And [Practice]=" & "'" & Me![Combo22] & "'"

[CT_AccessHelp]

It can be attached in the "Go Advanced" area. If each worked separately my code earlier should have worked. You've got the quotes off. Try

stLinkCriteria = "[County]=" & "'" & Me![Combo1] & "' And [Practice]=" & "'" & Me![Combo22] & "'"

OMG!!!! IT WORKED!!! Thank you so much!!!!

[pbaldy]

Whew! Happy to help.