How do you create a form that you can search data?

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

Guest

I am trying to create a form with multiple cells that you can search on any
of them or a combination of them and return the data you want.
 
Hi, Rhetta,

First, just a small lingo thing--cells are an Excel object, Access forms
have controls.

The general strategy is to use unbound controls (usually combo boxes to make
the selection easy) to build a filter string in a textbox on the form, and
then apply the filter in either opening a form, report, or query. You can
set the textbox' Visible property to No if you like.

In the AfterUpdate event of each control, the code below loops through all
the controls on the form, looking in this case for a textbox or a combo box.
If the control is not null, it processes the entered value and adds the
result to the filter string. After all controls have been handled, it strips
the last " AND " off.

One key to making this particular code work is naming the control after the
field in the table it represents, with a three-character prefix for the
control type.

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

On Error Resume Next

'Initialize control
Me!txtFilterString = ""

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

If (ctl.ControlType = acComboBox OR ctl.ControlType = acTextBox) Then
If (Nz(ctl.Value) <> 0 And Nz(ctl.Value) <> "") Then
Me!txtFilterString = Me!txtFilterString & _
"[" & LTrim(Right(ctl.Name, Len(ctl.Name) -
3)) _
& "]=" & ctl.Value & " AND "
End If
End If

Next ctl

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

End Sub

Attached to a command button is code to open a form, using the filter if it
is not null:

Private Sub cmdOpenMyForm_Click()

Dim strDocName As String
Dim strFilter As String

strDocName = "YourFormName"
strFilter = ""

' If no criteria is selected, open the form showing all records
If IsNull(Me!txtFilterString) Then
DoCmd.OpenForm strDocName, acNormal
Else
strFilter = Me!txtFilterString
DoCmd.OpenForm strDocName, acNormal, , strFilter
End If

End Sub

Hope that helps.

Sprinks
 
Well, this feature is built into ms-access.

Bring up that form you want to search, and then go

records->filter->filter by form

the form you are looking at will be transformed into a query form, and
"and", "or" can be used, in addition, many fields actually become combo
boxes.

I would give the above feature a try. It is OFTEN overlooked by many
ms-access users....
 
Back
Top