Link to home
Create AccountLog in
Avatar of lkirke
lkirkeFlag for Australia

asked on

Combo Box Filter

Hello Experts,

I have a DB attached (Example DB) with a combo box filter (cbo_Filter_NMI) located on a form (frm_Premise).

Whilst leveraging off a previous example (Find-record-w-combo) I saw published on EE, I am having some trouble integrating this into my DB.

Essentially, the filter references a query (qry_Search), which is then used to populate the fields within the form, frm_Premise and its subform (frm_Participants). Once selected from the query, the record will be able to be edited, viewed etc etc.

I can get the filter referencing the query easily enough, but am having some trouble applying the On-Click Event code from the example to my DB.

For instance, on the line within the code, Forms!frm_Premise!frm_Participants.Form.RecordSource, is there a better way to extract the required records from the query considering there are so many fields within both the forms  frm_Premise and frm_Participants (NB: I do understand that more fields will need to be included within the query for this to work)?

Any suggestions and assistance would be warmly received. :)


Private Sub cboFilter_Click()
    Dim filter_a As String
    Dim filter_b As String
    Dim filter_c As String
    filter_a = "[Row ID] = '" & cboFilter.Column(3) & "'"
    filter_b = "[Row ID] = '" & cboFilter.Column(4) & "'"
    filter_c = "[Row ID] = '" & cboFilter.Column(5) & "'"
    Forms!tbl_A_FORM.Filter = filter_a
    DoCmd.ApplyFilter , filter_a
    Forms!tbl_A_FORM!tbl_B_FORM.Form.RecordSource = "SELECT tbl_B.[Row ID], tbl_B.Parent_ID, tbl_B.Desc FROM tbl_B WHERE tbl_B.[ROW ID] = '" & cboFilter.Column(4) & "'"
    Forms!tbl_A_FORM!tbl_B_FORM!tbl_C_FORM.Form.RecordSource = "SELECT tbl_C.[Row ID], tbl_C.Parent_ID, tbl_C.Desc FROM tbl_C WHERE tbl_C.[Row ID] = '" & cboFilter.Column(5) & "'"

End Sub


Private Sub cbo_Filter_NMI_Click()
    Dim Filter_A As String
    'Dim Filter_B As String
    'Dim Filter_C As String
    'Dim Filter_D As String
    Filter_A = "[Row ID] = '" & cbo_Filter_NMI.Column(0) & "'"
    'Filter_B = "[Row ID] = '" & cbo_Filter_NMI.Column(4) & "'"
    'Filter_C = "[Row ID] = '" & cbo_Filter_NMI.Column(5) & "'"
    'Filter_D = "[Row ID] = '" & cbo_Filter_NMI.Column(7) & "'"

    Forms!frm_Premise.Filter = Filter_A
    DoCmd.ApplyFilter , Filter_A
    'Forms!frm_Premise!frm_Participants.Form.RecordSource = "SELECT tbl_B.[Row ID], tbl_B.Parent_ID, tbl_B.Desc FROM tbl_B WHERE tbl_B.[ROW ID] = '" & cboFilter.Column(0) & "'"

End Sub

Open in new window

Avatar of Armen Stein - Microsoft Access MVP since 2006
Armen Stein - Microsoft Access MVP since 2006
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of lkirke


Thank you Armen. Given me some food for thought and pointed me in another direction.