How to specify *MULTIPLE CRITERIA* for filtering records 1

[FuzzyBear9]

Help!

I am a newbie to Access 97 and am stuck!!! Access allows me to use the toolbox wizard to open a form to display specific records. This is fine if you are matching one field in the current form to a field in the form that you wish to open.

My problem is I want to match more than one field!!!

Here is the VBA code for the command button that opens the form OUTCOME from the current form (CLIENT):

Private Sub frmClientOpenFormOutcome_Click()
On Error GoTo Err_frmClientOpenFormOutcome_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmOutcome"

stLinkCriteria = "[ClientID]=" & Me![Client]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_frmClientOpenFormOutcome_Click:
Exit Sub

Err_frmClientOpenFormOutcome_Click:
MsgBox Err.Description
Resume Exit_frmClientOpenFormOutcome_Click

End Sub


Is there a way of specifying more than the field 'Client'??? For example would it be possible to match fields 'Survey' and 'Location' as well???

Any help would be greatly appreciated. In fact, the selfless help and guidance offered by members of Tek-Tips has been invaluable.

 

[jebry]

Hi!

Make this change:

stLinkCriteria = "[ClientID]=" & Me![Client]

To

stLinkCriteria = "[ClientID]=" & Me![Client] & " And [Survey] = '" & Me![Survey] & "' And [Location] = '" & Me![Location] & "'"

Note, you will need to use the names of the fields and controls as they appear in your Db and the above code assumes that Survey and Location are text fields. If they are number fields, leave out the single quotes.

hth

Jeff Bridgham
bridgham@purdue.edu

 

[FuzzyBear9]

Hey!!! It worked!!!!

This is the VBA code I used:

stLinkCriteria = "[SurveyID]=" & Me![SurveyID] & "And[ClientID]=" & Me![Client]

The important thing for me was the inclusion of 'And' INSIDE the opening quotation marks of the second matching field 'Client'.

Thank you! Thank you! Thank you!