Search a table by unsing a form

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

Guest

I have a form based on a table with 20 fields. My clients want the ability
to search all fields to find the record they are looking for. Is there a way
for me to create a form (with all 20 fields) where they can chose which
fields to search by and those records come up?
Ex. I know that state, company, and first name of a record I need. I go
into the 'Search Form' and type that information in and that record is
retrieved.
It is kind of like an and/or type of query but they need to be able to
choose which fields to search by and leave the others blank.
Thanks in advance.
 
consjoe,

This sounds like the built in functionality called 'filter-by-form' You
can filter any form in this way by clicking the filter by form toolbar
button.

HTH,
Josh
 
The basic strategy is to place unbound controls on a form, build an SQL
search string, placing it a visible or invisible form control, then execute
it the query with a command button. WriteFilterString below is called in the
AfterUpdate event of each control:

Private Sub WriteFilterString()
Dim intindex As Integer
Dim ctl As Control

On Error Resume Next

'Reinitialize control
Me!txtFilterString = ""

' Loop through all form controls; if there's data, add to filter string
For Each ctl In Me.Controls

If ctl.Tag = "Criteria" Then
' I tag all criteria controls so that I don't have to test specifically
for labels, etc.

If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
‘ If not null, add to filter string

If ctl.ControlType = acComboBox Then
‘ For combo boxes, use the criteria that the field is equal to
the choice
‘ Get the fieldname by stripping the first three letters off the
control name

Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "]=" & ctl.Value & " AND "

Else
‘ For textboxes, use it as a wildcard with Like
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.name, Len(ctl.name) -
3)) _
& "] Like " & "'" & "*" & ctl.Value & "*" &
"'" & " AND "
End If

End If

End If

Next ctl

' Strip end of filter
Me!txtFilterString = Left(Me!txtFilterString, Len(Me!txtFilterString) - 5)

End Sub

Then, a command button procedure executes the query.


Dim stDocName As String
Dim stWhere As String

stDocName = "frmItemsDisplay"
stWhere = Me![txtFilterString]

DoCmd.OpenForm stDocName, , , stWhere

Another command button clears the entries.

Private Sub cmdClearSelection_Click()
' Reset all controls
For Each ctl In Me.Controls
If (ctl.Tag="Criteria") Then
ctl.Value = Null
End If
Next ctl
End Sub


Hope that helps.
Sprinks
 
Back
Top