How to search multiple values in the database using a command button

hri

New member
Local time
Today, 14:58
Joined
Oct 13, 2011
Messages
7
Hi
I am a new programmer in ms access. I have developed a search form that takes users first name, last name, postcode and phone number and when i click a command button it should have to search the database and retrive the records.
Can anyone help me to do this.
Thank you
 
How are you planning on displaying the records, are you planning on opening a form or a report, perhaps displaying a qwuery directly to the end user?

Personally the way I'd do this is by constructing a string in VBA based on the values of the controls you mentioned, then apply that string as the .filter when opening a form / report.

Something like this:

Code:
Dim strFilter As String
strFilter = ""
If Len(Nz(txtFirstName,""))>0 then
   strFilter = strFilter & "FirstName = '" & txtFirstName & "' And "
End If
 
If Len(Nz(txtLastName,""))>0 then
   strFilter = strFilter & "LastName = '" & txtLastName & "' And "
End If
 
'Trim the trailing " And " from the string 
If Len(strFilter)>0 then
   strFilter = Left(strFilter,Len(strFilter)-5)
End If
 
DoCmd.OpenForm "frmSearchResults",,,strFilter

Note that the above is untested code written as an example. It also makes assumptions on your field & control names as well as the form being loaded.
 
Thanks for the reply Actually I have designed all the search fields in the header tab and i want to display the search results in the detail tab.I have created the text boxes to display the search results and bound the controls to the database.

The click fuction I have designed is
Private Sub btnFind_Click()

Dim strfilter As String
strfilter = ""

If Len(Nz(Me.txtLastName, "")) > 0 Then
strfilter = strfilter & "([surname]= """ & Me.txtLastName & """) AND "
End If

If Len(Nz(Me.txtFirstName, "")) > 0 Then
strfilter = strfilter & "([forenames]= """ & Me.txtFirstName & """) AND "
End If

If Len(Nz(Me.txtPostCode, "")) > 0 Then
strfilter = strfilter & "([postcode]= """ & Me.txtPostCode & """) AND "
End If

If Len(Nz(Me.txtPhoneNumber, "")) > 0 Then
strfilter = strfilter & "([telephone]= """ & Me.txtPhoneNumber & """) AND "
End If

If Len(Nz(Me.txtCustomerID, "")) > 0 Then
strfilter = strfilter & "([cust_id]= """ & Me.txtCustomerID & """) AND "
End If

If Len(strfilter) > 0 Then
strfilter = Left(strfilter, Len(strfilter) - 5)

End If
End Sub

Thanks
 
Looks good, you just need to add it to the form's .filter property now and ensure the filter is enabled.

That's done something like this:

Code:
Me.Filter = strFilter
Me.FilterOn = True

You may need Me.Requery or Me.Refresh too, but I'd try without it first.
 
Hi I have added the codes to the vba

If Len(strfilter) > 0 Then
strfilter = Left(strfilter, Len(strfilter) - 5)
Me.Filter = strfilter
Me.FilterOn = True
Me.Refresh
End If

But it is not showing the filtered results its just showing me the general results.
Thanks
 
If Len(strfilter) > 0 Then
strfilter = Left(strfilter, Len(strfilter) - 5)
End If

Me.Filter = strfilter
Me.FilterOn = True
Me.ReQuery
 
Yes Thats working now.
Thank you genius.
I am happy now
 

Users who are viewing this thread

Back
Top Bottom