Filter records in access subform based on 3 comboboxes in main form

I need to Filter records in access subform based on 3 comboboxes in main form.  I have tried making the said comboboxes criteria in the query underlying the subform but no luck.  Any suggestions?
LVL 1
ksilvosoAsked:
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.

PatHartmanCommented:
What you say you have done should work.  We'd have to see it to figure out what went wrong.  Post the SQL for the queries please.
0
Gustav BrockCIOCommented:
It might be easier to move the filtering from the query to the subform itself. Here no. 2 is text, 1 and 3 are numeric:

Public Sub SetSubformFilter()

    Dim Filter As String

    If Not IsNull(Me!Combo1.Value + Me!Combo2.Value + Me!Combo3.Value) Then
        Filter = "[SortField1] = " & Me!Combo1.Value & " And "[SortField2] = '" & Me!Combo2.Value & "' And "[SortField3] = " & Me!Combo3.Value & ""
        Me!NameOfSubformControl.Form.Filter = Filter
        Me!NameOfSubformControl.Form.FilterOn = True
    Else
        Me!NameOfSubformControl.Form.FilterOn = False
    End If
    
End Sub

Open in new window

Call this in the AfterUpdate event of each combobox.

/gustav
0

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
ksilvosoAuthor Commented:
Thanks Gustav,
  should I create this as a module and call the module?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ksilvosoAuthor Commented:
Thanks Pat,  Here's my sql:
SELECT jcnDogBrace.jcnDogBraceID, jcnDogBrace.BraceID, jcnDogBrace.TrialYearID, jcnDogBrace.TrialDayID, jcnDogBrace.BraceID, jcnDogBrace.DogID, jcnDogBrace.Points, jcnDogBrace.Notes
FROM jcnDogBrace
WHERE (((jcnDogBrace.BraceID)=[Forms]![frmCompetition2]![cboBraceID]) AND ((jcnDogBrace.TrialYearID)=[Forms]![frmCompetition2]![cboTrialYearID]) AND ((jcnDogBrace.TrialDayID)=[Forms]![frmCompetition2]![cboDayID]) AND ((jcnDogBrace.BraceID)=[Forms]![frmCompetition2]![cboBraceID]));
0
PatHartmanCommented:
The SQL looks fine.  Do you have a button to run after the criteria is selected that requeries the subform?  You can do the requery in the AftereUpdate event of each combo:

Me.Requery

But, I don't like to do that since there are three combos.
0
Gustav BrockCIOCommented:
should I create this as a module and call the module?

I would include it in the module behind the main form.

/gustav
0
Gustav BrockCIOCommented:
Methods provided.
0
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
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.