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. :)

EXAMPLE --->

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


APPLICATION TO DB --->

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

Find-record-w-combo.mdb
Example-4.mdb
lkirkeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
I'm not sure why you're changing the Recordsources for the forms.  Don't you just want to change the filtering?  If so, you're already doing that in your code.

And if you do change the Recordsource, I recommend that you don't rebuild it from scratch in code.  Instead, you can just change the Where clause, using code we've written to automatically find and replace the Where clause in a SQL statement.  It's a free download called "J Street SQL Tools" on our free J Street Downloads page at http://ow.ly/P21j.

Take the text and paste it into a new module called basJStreetSQLTools.

To use it, try the function ReplaceWhereClause.  You send in a whole SQL statement and the new desired Where clause, and it locates and snips out the old one, inserts your new one, and gives you back the new statement.  If you send in a null or empty Where clause, the function just removes any existing one from the statement.

Rebuilding a whole SQL Statement in code is laborious and often leads to hard-to-diagnose errors.  Our replacement technique allows you to store the whole query structure in a property or saved query without having to rebuild it each time in code.  All you have to do is rebuild the Where clause.  It's one of the most useful functions we've ever written - it's used in every single Access application we build.  It works with both Access and SQL Server query syntax, so it works fine on passthrough queries too.

By the way, there's also a ReplaceOrderByClause function that does the same for sorting.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lkirkeAuthor Commented:
Thank you Armen. Given me some food for thought and pointed me in another direction.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.