Search Listbox with Multiple Critera

[leighj]

Hi All,

On one of my forms, I have a list box (SearchResults).

This pulls data from "tblContacts".

What I want to do, is to add two "filters" to narrow down the listbox results. I've got other forms running with code which work perfectly for only one condition... but now I'm trying to add three into the mix it's proving difficult - and a good hour of googling hasn't turned anything up.

So my list box is: SearchResults

Search Critera 1 = Combo box (cboLocation) - filtering down by location (stored in the table as short text)

Search Critera 2 = Option Group (grpDay) - filtering down by the day

Search Critera 3 = Can be automatically applied... the only results that I want to display will be ticked in my "optFirstAid" field on the table.

I'd like my user to be able to go to the form, select "Building 1" on the combo box and select the "Thursday" option - and the specific contacts (which are ticked) attached to that building on that day would be returned in the list box.

Hope I've explained that OK?

Really appreciate any help

Leigh

 

[bob fitz]

Can you post a copy of your db

 

[leighj]

Please find attached a stripped down version of my DB

Thanks Bob

 

[bob fitz]

I have looked at your db but I can not see how your tables are related.
Because you have fields called ThursFALoc, FriFALoc and SatFALoc, I also suspect that your tables are not normalized. IMHO these issues should be sorted out before going any further.

 

[leighj]

I have looked at your db but I can not see how your tables are related.
Because you have fields called ThursFALoc, FriFALoc and SatFALoc, I also suspect that your tables are not normalized. IMHO these issues should be sorted out before going any further.

Thanks for your feedback Bob. I'm very new to this, so I'll do some research on normalising a database.

Cheers,
Leigh

 

[isladogs]

Hi

I haven't looked at your db as I was typing this before you uploaded it.
=====================================

One approach would be something like the following
You can see this in use on the attached screenshot.

For each combo box have after update code similar to this

Private Sub cboCodeFilter_AfterUpdate()

On Error GoTo Err_Handler
    'Filter by pastoral code
    If Nz(Me.cboCodeFilter, "") <> "" Then
        strCodeFilter = " And PRecords.Code = '" & Me.cboCodeFilter & "'"
        GetRecordSource
    End If
    
    If Me.LstIncidents.ListCount > 0 Then LstIncidents_Click 'CR v4683 - added to ensure first record in list is displayed

Exit_Handler:
    Exit Sub

Err_Handler:	
    strProc = "cboCodeFilter_AfterUpdate"
    MsgBox "Error " & err.Number & " in procedure " & strProc & vbNewLine & err.Description
    Resume Exit_Handler

End Sub

Each of these refers to a GetRecordSource routine:

Sub GetRecordSource()

On Error GoTo Err_Handler
    
    strSelect = "SELECT DISTINCTROW PRecords.PastoralRecordID, Format([DateOfIncident],'dd/mm/yyyy') AS [Date]," & _
        " PupilData.PupilID, [Surname] & ', ' & [Forename] AS Pupil," & _
        " [Yeargroup] & [TutorGroup] AS TGp, PRecords.Code, PRecords.ReferredBy AS [From]," & _
        " PRecords.ForAttentionOf AS [To], IIf([PRecords].[Acknowledged]=True,'Yes','No') AS Ack, IIf([PRecords].[Done]=True,'Yes','No') AS Done," & _
        " PupilData.YearGroup, Subjects.FacultyID" & _
        " FROM (PupilData INNER JOIN PRecords ON PupilData.PupilID = PRecords.PupilID)" & _
        " LEFT JOIN Subjects ON PRecords.Subject = Subjects.SubjectID"

   strOrderBy = " ORDER BY PRecords.DateOfIncident DESC, PastoralRecordID DESC"	

   strWhere = " WHERE (PRecords.PastoralRecordID > 0) AND (PRecords.Code Is Not Null) AND (PRecords.DateOfIncident<=Now())" _
        & strCodeFilter & strYearFilter & strTGpFilter & strHouseFilter & strDoneFilter _
        & strMyFilter & strFacultyFilter & strReferrerFilter

    strRecordSource = strSelect & strWhere & strOrderBy & ";"

    Me.LstIncidents.RowSource = strRecordSource

    'requery the list and set to first item
    Me.LstIncidents.Requery
    If Me.LstIncidents.ListCount > 0 Then
        Me.LstIncidents = Me.LstIncidents.ItemData(1)
        Me.txtRecordCount = Me.LstIncidents.ListCount - 1 'CR v5142
    Else
        Me.txtRecordCount = 0 'CR v5142
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    strProc = "GetRecordSource"	
    MsgBox "Error " & err.Number & " in procedure " & strProc & vbNewLine & err.Description
    Resume Exit_Handler
    
End Sub

As you can see in the screenshot, this also gives a record count for the filtered list

Obviously you'll need to define the strings used

Hope this helps

Colin