Form Search Options

  • Thread starter Thread starter Vincent Capodanno
  • Start date Start date
V

Vincent Capodanno

I a trying to create a Form with option buttons
designating 4 search options. Each button will search a
different field, Authorization Number, SSN, CIS Number,
and Last Name. I want all of the options to link to one
text field that will allow me to search any of the 4
fields depending on which option button is selected. For
example, if I am looking for all authorization records for
999999 I would select the Authorization Number option
button and enter 99999 in the text field then click
Search. The subform will then list all 999999
authorization records. The database contains only 1 table
with thousands of records. How can this be done.
 
Vincent Capodanno said:
I a trying to create a Form with option buttons
designating 4 search options. Each button will search a
different field, Authorization Number, SSN, CIS Number,
and Last Name. I want all of the options to link to one
text field that will allow me to search any of the 4
fields depending on which option button is selected. For
example, if I am looking for all authorization records for
999999 I would select the Authorization Number option
button and enter 99999 in the text field then click
Search. The subform will then list all 999999
authorization records. The database contains only 1 table
with thousands of records. How can this be done.
Click to expand...

Hmm. If you're displaying the results in a subform, I'd think you could
do this by changing the LinkChildFields property of the subform control
based on which option was chosen, leaving the LinkMasterFields property
always set to the same text box on the parent form. The code might be
something like this:

'*** WARNING: AIR CODE ***
Private Sub cmdSearch_Click()

Dim strChildField As String

Select Case Me.frmSearchOption
Case 1 ' Authorization Number
strChildField = "AuthNo"
Case 2 ' SSN
strChildField = "SSN"
Case 3 ' CIS Number
strChildField = "CISNo"
Case 4 ' Last Name
strChildField = "LastName"
Case Else
MsgBox "No valid search option chosen!"
Exit Sub
End Select

Me.sfResults.LinkChildFields = "[" & strChildField & "]"

End Sub
'----- end code -----

In the above, substitute the name of your subform control (the control
on the main form that displays the subform) for "sfResults".

Now, this is untested and it may be that you have to deal with an error
raised when you set the LinkChildFields property, or that you have to
explicitly requery the subform. But I'd try it like this first.
 
Back
Top