Text Box Search for Inside a Database

  • Thread starter Thread starter Charles Robinson III
  • Start date Start date
C

Charles Robinson III

I am attempting to create a text box where the user of the
database would enter an alphanumeric string to search
within a table, such as a last name or part of a last
name, and would supply a list of results below in a
subform, where clicked would open that record for
editing.

I have been searching for a few days for such scripts, and
tring to get help, but have been unsuccessful. If anyone
can help, please let me know.

Thank You,
Charles Robinson III
(e-mail address removed)
 
Charles said:
I am attempting to create a text box where the user of the
database would enter an alphanumeric string to search
within a table, such as a last name or part of a last
name, and would supply a list of results below in a
subform, where clicked would open that record for
editing.

I have been searching for a few days for such scripts, and
tring to get help, but have been unsuccessful. If anyone
can help, please let me know.
Click to expand...

The most reliable way to do that is to construct a Where
clause and use it to set the subform's RecordSource
property. You didn't say what the sub form's record source
was, but if it's just a table or named query, then the code
would be something like:

Me.subformcontrol.Form.RecordSource = _
"SELECT * FROM thetable " _
& "WHERE lastname LIKE """ & Me.thetextbox & "*"""

If that's not practical in your case, try using the
subform's filter property:

Me.subformcontrol.Form.Filter = " lastname LIKE """ _
& Me.thetextbox & "*"""
Me.subformcontrol.Form.FilterOn = True

but this has unexpected side effects in some situations.

How you open another form by double clicking can be messy
because each control in the continuous subform would have to
deal with the issue. A cleaner way is to provide a command
button on the main form that opens the other form to the
matching record. Again, it involves constructing a Where
clause kind of string:

Dim strWhere As String
strWhere = "thekeyfield = " & _
& Me.subformcontrol.Form.thekeyfield
DoCmd.OpenForm otherform, WhereCondition:= strWhere
 
Back
Top