Set Filter on Query after Combo Box Selection

I need to set a filter on a query so I can run a report from a joined query.  I want to do this from a form, since I already have selected a record with the [acct nbr] I want.  I just can't figure out how to do this but I thought it would be simple.  I need to run a report  that is filtered from the joined query so I can run a text stream to put into Outlook.  I have the Outlook section done.  I just need the filtered text stream.

If I can't filter the query from the combo box VBA, then filtering from the Forms![acct nbr] is fine.  Or, filtering the report by [acct nbr] is fine as well.

The Combo box VBA is below.

Private Sub Combo34_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![Combo34], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Open in new window

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.

You can set the criteria row of the desired field in your query to use a value from your form.


Name field in query.

criteria row of name field in query

= forms!formname.controlname


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
If you will always filter from the ComboBox as it appears you do from the code above you could create the query to filter against the control itslelf.  While this limits your options it's extremely simple if this is the extent of what you need to do.  The query would look something like this, (Replace Fields and table names to fit your situation....

SELECT tblCustomer.CustomerID
FROM tblCustomer
WHERE tblCustomer.CustomerID=[Forms]![Form1]![Combo34];
To include the use of your Nz() fuction the query would require only a slight alteration as follows...

SELECT tblCustomer.CustomerID
FROM tblCustomer
WHERE tblCustomer.CustomerID=Nz([Forms]![Form1]![Combo34],0);

If however the field is a string you'll need to remember to encapsulate the field with Quotes or an appostrophe to tell JET (Access) that the field is a string.  You'll also need to check for and replace any appostrophes within the string and double them up so Jet (Access) doesn't confuse an appsotrophe in the string to be a character indicating the strart or end of the string vs. a litteral appostrophe within the WHERE clause.  For example...

SELECT tblCustomer.CustomerID
FROM tblCustomer
WHERE tblCustomer.CustomerID=' & Replace(Nz([Forms]![Form1]![Combo34],0),"'","''");

do you know how to get the selected value form teh combo box  in code ?
waiting for your reply
jmar1946Author Commented:
Very simply done.  Thanks.  I can just run the report straight from the filtered query as I wanted.  Thanks.
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.