Open form filtered with multipl conditions

[tt1611]

Hi All
I have spent the last 2 hours trying to troubleshoot this problem. Please help.

I have a form with 3 combo boxes where a user selects information they want loaded on a second form.

Form A has site, builidng and facility represented by these combo boxes.
When the user clicks load facility info, the idea is that the second form Form B opens with the filtered info.

I have the following code running on Form A on the button click event

Private Sub cmdfacmgmt_Click()
If ValidateRecord = False Then
Cancel = True
Else
Dim stLinkCriteria, stLinkCriteria1, stLinkCriteria2 As String


stLinkCriteria = "[Community]=" & "'" & Me![cmbcomm] & "'"
stLinkCriteria1 = "[Building]=" & "'" & Me![cmbbldg] & "'"
stLinkCriteria2 = "[Facility]=" & "'" & Me![cmbfac] & "'"


DoCmd.OpenForm "Fac_Mgmt", , , stLinkCriteria And stLinkCriteria1 And stLinkCriteria2


End If
End Sub

The event is causing a type mismatch error

If the WHERE criteria in the open even is run one condition at a time, the form opens. When i connect them all together with "And" the even fails.

I have checked the data types in the underlying table of Form B (which is a bound form) and no problems are noticed.
I prefer this method over the idea of a sub form so I really would like this to work.

Please help. This is quite urgent.

 

[jdraw]

I'm sure there is more, but as a start

Dim stLinkCriteria, stLinkCriteria1, stLinkCriteria2 As String

will result in
stLinkCriteria, stLinkCriteria1 being variant

You must explicitly dim variables as string
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim stLinkCriteria2 As String
to make them all string type

 

[tt1611]

Hey JDraw
Tried that scenario too and that also generates a type mismatch

 

[Kryst51]

Don't you need something like:

DoCmd.OpenForm "Fac_Mgmt", , , '" & stLinkCriteria & "' And '" & stLinkCriteria1 & "' And '" & stLinkCriteria2 & "'

 

[Kryst51]

Or actually something like this maybe?:

Dim stLinkCriteria As String
 
stLinkCriteria = "[Community]=" & "'" & Me![cmbcomm] & "'"
stLinkCriteria = " '" & stLinkCriteria & "' & [Building]=" & "'" & Me![cmbbldg] & "'"
stLinkCriteria = " '" & stLinkCriteria & "' & [Facility]=" & "'" & Me![cmbfac] & "'"

Then:

DoCmd.OpenForm "Fac_Mgmt", , , stLinkCriteria

 

[tt1611]

Thanks Kryst.

Ill test this out on Monday and give you feedback.

 

[boblarson]

Or actually something like this maybe?:

Dim stLinkCriteria As String
 
stLinkCriteria = "[Community]=" & "'" & Me![cmbcomm] & "'"
stLinkCriteria = " '" & stLinkCriteria & "' & [Building]=" & "'" & Me![cmbbldg] & "'"
stLinkCriteria = " '" & stLinkCriteria & "' & [Facility]=" & "'" & Me![cmbfac] & "'"

close but not quite right there either. It should be:

Dim stLinkCriteria As String
 
stLinkCriteria = "[Community]=" & "'" & Me![cmbcomm] & "'"
stLinkCriteria = " " & stLinkCriteria & " [B][COLOR=red]AND[/COLOR][/B] [Building]=" & "'" & Me![cmbbldg] & "'"
stLinkCriteria = " " & stLinkCriteria & " [COLOR=red][B]AND[/B][/COLOR] [Facility]=" & "'" & Me![cmbfac] & "'"

Kryst51: Notice you had some single quotes around stLinkCriteria which were not supposed to be there and you didn't include the AND's in there but had ampersands instead which is not correct.

 

[Kryst51]

Ha, the exact thing I was trying to point out, doh (the Ands I mean, hence my first posting)! I was using something that someone had given me to help with this sort of thing.... so the single quotes were part of an example I had been given, probably which I applied incorrectly.

 

[tt1611]

Kryst, Bob
You guys are life savers. This works like a charm...Thank you for your help..VBA continues to puzzle me. 3 criteria coded the same but generates a mismatch. At least now I know to declare only one string as supposed to 3.