asked on

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

ASKER CERTIFIED SOLUTION

ldunscombe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rick_Rickards

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];

Rick_Rickards

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),"'","''");

Smart_Man

do you know how to get the selected value form teh combo box in code ?
waiting for your reply

jmar1946

ASKER

Very simply done. Thanks. I can just run the report straight from the filtered query as I wanted. Thanks.