Search form using both combo boxes and checkboxes (1 Viewer)

[phil.palmer]

Good Afternoon everyone,

First, I would like to say I am new however trying to learn...please be gentle.

I am trying to make a search option in my form header. Right now I have two unbound combo boxes (CboAccountsfilter and cboCourseName) that I can use to filter my records. Currently, I can use the drop down for CboAccountsfilter and a list of accounts will appear. When I select one, the corresponding Course Names will appear in cboCourseName. This works fine...Code below. I would like to take the filtering a step farther and add checkboxes to filter the data. I my form, there currently exist several check boxes (yes/no)...(Priority, Rep Top Target, Manager Top Target, ect). I would like to have the option to use a check box to filter. I.E if I had a checkbox in my header called PriorityFilter, if checked it would only bring up those records that met the two combo boxes criteria and was a priority. I hope this make sense.

Below is the code I have so far...it doesnt have anything for the checkbox because I am at a lost of how to get started. Again, I am new but eager

Private Sub CboAccountsfilter_Change()
Me.Requery
Me.cboCourseName.Requery
Me.Check178.Requery
End Sub

Private Sub CboAccountsfilter_AfterUpdate()
Dim strFilter As String

Me.cboCourseName.Value = Null
Me.Requery

If Len(Me.cboCourseName & vbNullString) > 0 Then
strFilter = "[finalname]=" & Chr(34) & Me.cboCourseName & Chr(34) & " And "
End If

If Len(Me.CboAccountsfilter & vbNullString) > 0 Then
strFilter = strFilter & "[Account Name]=" & Chr(34) & Me.CboAccountsfilter & Chr(34) & " AND "
End If

If Right(strFilter, 5) = " AND " Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If

Me.Filter = strFilter
Me.FilterOn = True
End Sub
Private Sub cboCourseName_AfterUpdate()
Dim strFilter As String
cboCourseName.Requery
If Len(Me.cboCourseName & vbNullString) > 0 Then
strFilter = "[finalname]=" & Chr(34) & Me.cboCourseName & Chr(34) & " And "
End If

If Len(Me.CboAccountsfilter & vbNullString) > 0 Then
strFilter = strFilter & "[Account Name]=" & Chr(34) & Me.CboAccountsfilter & Chr(34) & " AND "
End If

If Right(strFilter, 5) = " AND " Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If

Me.Filter = strFilter
Me.FilterOn = True
Me.Requery
End Sub

 

[Chungalin]

How I do it: I’ve developed a a generic scheme to handle these kind of filters.

• I have several unbound controls as filters, clearly differentiated from data controls (different backcolor). Each filter has a Tag property set to the filter expression like this: “fieldname = @@” (numeric), “fieldname LIKE "@@*"” (match string beginning), etc.
• Two events for each filter control: Double Click (resets the control to Null or its default value) and After Update. Each event handler calls a function "SetRecordSource" at the end.
• SetRecordSource updates form RecordSource property according to all filters state. To do so, it enumerates all form’s controls looking for those with non null Tag. Then replaces @@ with actual filter control value. Then combines all filters with " AND " and finally it updates the RecordSource property.
• Form Tag contains base SQL query with @@ where the "where" clause should be placed: “SELECT * FROM tbl_Test @@ ORDER BY test_Id”

Public Sub SetRecordSource(ByRef frm As Form)
  Dim strWhere As String
  Dim bolFiltrePreviOn As Boolean
  Dim strFiltrePrevi As String
  Dim ctl As Control

  For Each ctl In frm.Controls
    If ctl.Tag <> "" Then
      If Not IsNull(ctl) Then
        strWhere = IIf(strWhere <> "", strWhere & " AND ", "") & Replace(ctl.Tag, "@@", ctl, 1, 1)
      End If
    End If
  Next

  bolFiltrePreviOn = frm.FilterOn
  strFiltrePrevi = frm.Filter

  frm.RecordSource = Replace(frm.Tag, "@@", IIf(strWhere <> "", "WHERE " & strWhere, ""), 1, 1)

  frm.Filter = strFiltrePrevi
  frm.FilterOn = bolFiltrePreviOn
End Sub

It can also be done by just updating the Filter property instead of RecordSource. In that case, just assign strWhere to Filter property and update FilterOn accordingly.

Any constructive improvements are welcome.