Access Diva Search Form

The difficult I do immediately, the impossible takes a little bit longer.

Forms

Searching Records...

Search Form

With this Search you open up a separate Form, make your selections and then from your selections the filtered Form showing your selections opens.

Decide on which fields on your main form you want Users to be able to search on and then create your Form.

'From https://www.access-diva.com/f14d.html
'Gina Whipp 6.29.2010
  Dim strFilter As String
  Dim ctl As Control
  Dim strType As String
 
  For Each ctl In Me.Controls
    If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
      If (ctl.Tag <> "") And (ctl <> "") Then
        strType = Mid(ctl.Tag, 1, 1)
 
        Select Case strType
          Case "t"
            If InStr(1, ctl, "*") Then
              strFilter = strFilter & Mid(ctl.Tag, 2) & " LIKE '" & ctl & "' AND "
            Else
              strFilter = strFilter & Mid(ctl.Tag, 2) & " = '" & ctl & "' AND "
            End If
          Case "n"
            strFilter = strFilter & Mid(ctl.Tag, 2) & " = " & ctl & " AND "
          Case "d"
            strFilter = strFilter & Mid(ctl.Tag, 2) & " = #" & ctl & "# AND "
 
        End Select
 
      End If
    End If
 
  Next ctl
 
  If strFilter = "" Then
    MsgBox ("No criteria selected.")
    Exit Sub
  End If
 
  strFilter = Trim(Mid(strFilter, 1, Len(strFilter) - 4))
 
  DoCmd.OpenForm "frmDamageInvestigation", , , strFilter, , , strFilter
  DoCmd.Close acForm, "frmSearch"

Private Sub cboAddressLocation_Click()
On Error Resume Next
    DoCmd.OpenForm "frmDamageInvestigation", , , "[diDamageInvestigationID]=" & Me![cboAddressLocation]
End Sub
 
Private Sub cboDamageDate_Click()
On Error Resume Next
    DoCmd.OpenForm "frmDamageInvestigation", , , "[diDamageInvestigationID]=" & Me![cboDamageDate]
End Sub
 
Private Sub cboInvestigationNumber_Click()
On Error Resume Next
 DoCmd.OpenForm "frmDamageInvestigation", , , "[diDamageInvestigationID]=" & Me![cboInvestigationNumber]
End Sub
 
Private Sub cboTicketNumber_Click()
On Error Resume Next
    DoCmd.OpenForm "frmDamageInvestigation", , , "[diDamageInvestigationID]=" & Me![cboTicketNumber]
End Sub

...Command Button with Combo and Text Boxes

Be sure to change the Row Sources, Forms and Controls to match your own!

Here's an example of two different way to use a separate Search Form.

In this example you make you selection and Form closes to open frmDamageInvestigation. Once frmDamageInvestigation opens it checks for frmSearch and if loaded it closes it. The disadvantage of this type of search is you are only allowed to make one selection. You may want to narrow your records down further. For that we go to example B.

For this one you need to add the Field Name to with a preceding t to the Tag Line of the control. So, your Field Name is diDate, in the Properties window of diDate on the Tag line add tdiDate. Do this for every field you want to search on. (Note, use the Field Name not the Control Name. You are not searching controls!)

Once you have completed that, add a Submit command button to your Search Form and copy the code below in the On_Click event procedure of your Submit command button.

Tips (Main)

Home

Let your Client customize the form labels

Linking Subforms to an Unbound Control(s) on the Main (Parent) Form

Customize your Main Menu or Switchboard

Auto-Add new child records when adding a new record on the Main (Parent) Form

Simple Bar Chart using Shapes

Simulate a Drop Down box (not a Combo Box) as seen on Web Pages

Implement a Log-On form to control what the User sees

Make Controls resize with a Form

Switch Windows

Assign *Permissions* based on Users Levels

New User Form

Add *Favorites* to a Continuous Form

Custom Record Selectors

Drill Down (for Continuous Forms)

Highlight Required Controls (End-User Defined)

This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.