Search a table by unsing a form

C

consjoe

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.
 
J

Joshua A. Booker

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
 
S

Sprinks

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
 
Top