• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8980
  • Last Modified:

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

1 Solution
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

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.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now