Search Box by Option Group

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've lloked for information in the opsts, but I can not find a specific
answer to my problem...
I am trying to create a search form, that searches based on 1 of three
choices: Fleet #, Serial/VIN, or Plate #. I want the user to be able to
choose one of these 3 choices, and have them displayed in a list box. BUT,
if the user chooses Plate or Serial/VIN, a text box also appears to be able
to type in what they are looking for, and the list box displays these results
no matter where the value of the user input is in the Plate or Serial/VIN
value. For instance, if the user chooses Serial/VIN, then types in 7337 it
would bring up the Serial/VIN values that have '7337' anywhere in those
values. I have the Fleet# serach down, just not the other two. Thanks for
your help!!!!!
 
Karen,

One way to do this would be to create the RowSource for your listbox in a
manner similar to this:

Private sub btnSearch_Click
Dim NewRowSource as string
Dim WhereClause as string

If IsNull(me.FleetNumber) then
doevents
else
WhereClause = "FleetNumber = '" & me.FleetNumber & "' "
End If

If IsNull(me.SerialNumber) then
doevents
else
WhereClause = "SerialNumber = '" & me.SerialNumber & "' "
End If

If IsNull(me.PlateNumber) then
doevents
else
WhereClause = "PlateNumber = '" & me.PlateNumber & "' "
End If

NewRowSource = "SELECT * FROM theTable WHERE " & whereclause
Me.SearchResult.RowSource = NewRowSource
Me.Requery
End Sub

If you wanted to allow them to do combination searches, you could put "AND"
and "OR" in WhereClause.

Lee Robinson
 
How do I use this code to work with the option group boxes? I'm still pretty
new to VBA. I need the search form to be as basic as possible for the users...
 
Back
Top