Search Multiple Text Fields In a Table from a Form

[mdc218]

Please bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :

Private Sub SearchButton_Click()
DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"

End Sub

The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .

 

[ShaneMan]

Please bare with me as I am an Absolute Access VB code novice . I have a Form in my Access DB and I found the Following code on this site and placed it behind my search button :

Private Sub SearchButton_Click()
DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "'"

End Sub

The Search works great but only searches on the 1 Field "First Name" . How would I expand the VB code to include another 2 fields within my table . ( the other 2 fields I want to serach appear on the Form ) .

Hello mdc,

In order for someone to lend you a hand, you need to provide a little more information. What are the field types? Number or Text. The field names and what controls on your form are being used for criteria. ie in your above code. FirstName is the field name. It's type is text and it's criteria is coming from [Text34] on your form.

Good Luck,
Shane

 

[mdc218]

Thanks Shane

One of the other fields I need to search on is called "Last Name" and its criteria is coming from Text7

The other field I would like to search on is called Department and the criteria is coming from department . The Department field is a drop down list on the form so I do not know if the has any implications on the coding ....

Hope this sheds some light and please advise if you need anymore information .

thanks In Advance
MDC

 

[Adeptus]

You may have problems with the department control having the same name as its data source. I'd recommend changing it to cboDepartment (cbo = Combo Box) or something like that.


DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "' and [AUP].[Last Name] = '" & Me.[Text7] & "' and [AUP].[Department] = '" & Me.[cboDepartment] & "'"


(I'm assuming Department is a text field, otherwise you don't need the single-quotes around it.)

 

[mdc218]

thanks Adeptus that makes sense ......

just 1 question .... If the Department combo box data is coming from a separate table called Department would the following code work ?

DoCmd.OpenReport "All users", acViewPreview, , "[AUP].[First Name] = '" & Me.[Text34] & "' and [AUP].[Last Name] = '" & Me.[Text7] & "' and [Department].[Department] = '" & Me.[cboDepartment] & "'"

thanks in Advance
MDC