Using Listboxes On a Form

[Kevin]

I am trying to accomplish the following by using a form with multiple
listboxes for selecting various criteria to query. I am at a road block.

My form contains the following listboxes for selecting information from a
table: Company name, year, quarter.

I need to do the following:
1. Setup the listboxes so they return unique values from the query. If all
records need to be returned, then I leave it blank.
2. Once the 3 categories are selected in the form, I need create a run
button that will update the query with the 3 category criteria and then
produce a report with the results.

Thanks in advance for any help!

 

[Tony Vrolyk]

Sounds like a fun problem to solve. Here is an outline of what I would do

1. Set the row source of each list box to a UNION query so you can include
"(All)" as an option. This should be easier than just not selecting from a
list box to include all. What if a user clicks and then wants all? They
can't unclick a listbox. Here is an example
http://www.mvps.org/access/forms/frm0043.htm

2. Create code on the AfterUpdate events of the list boxes to leave the Run
button disabled until all three have been selected

3. On Click of the Run button create a sql string in code and assign it to a
public string variable. Then open the Report. You can build a sample query
in the Qurey Builder then copy the SQL vie to code and modify to referenc
the listboxes. You would end up with something like this

strSql = "SELECT Orders.* FROM Orders WHERE Orders.Company Like '" &
me.lstCompany & "' AND Orders.Year Like '" & me.lstYear & "' AND
Orders.Quarter Like '" & Me.lstQuarter & "'"
(notice the single and double quotes next to each other)

4. On the report set the On Open event to set the reports Record Source
Private Sub Report_OnOpen()
Me.Report.RecordSource = strSql

Just my 2 cents
Tony V