Using ListBox Selections in a Query

[LakotaMan]

Hi All,

Using Access 2003, I have a query (acting as the record source for a report) that picks up dates from a form --this part is working fine. I also have a multi-select list box on the form, allowing users to select more than one ProgramID for their report. I am stumped on how to write the criteria for picking up these multiple selections.

Any help you can give me on this will be greatly appreciated.

TWD

 

[PHV]

You have to use VBA to access the ItemsSelected collection.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Check the FAQs in this forum. Perhaps this might work for you faq701-6099.

Duane
Hook'D on Access
MS Access MVP

 

[FancyPrairie]

Check out this FAQ faq181-5497. It will scan through all relevant controls on your form and build the Where clause for you.

To start, open a new module, copy the code from the FAQ and paste it into the new module. The header section of the code explains how to use it. To make it work, simply set the Tag properties of your controls and then open your report like this: Docmd.OpenReport "YourReportName",acViewPreview,,BuildWhere(Me)

Example:
Query for Report: Select ProductID1, Date1,...
Query for List Box: Select ProductID, ProductName..

In this case the Tag property for the List Box would be: Where=ProductID1,string

Note the Tag property contains the name of the product ID used in the query for the Report (ProductID1) not the name provided for the row source of the list box (ProductID).

Assuming the dates you mentioned represent a data range (begin and end date), then the name of the begin date would be dteWhatever_BeginR and the end date would be named dteWhatever_EndR. The Tag property for the begin date would be Where=Date1,date

Note the Tag property contains the name of the date used in the query for the Report (Date1). Also note that the names of the 2 date range fields have the same prefix (dteWhatever) and end with _BeginR and _EndR.

Setting the Tag property is the key to making it work.

Finally, the query for your report does not need to reference the controls on your form at all. The query could look like this: Select * from yourtable.

That's all you have to do:
1. Copy/Paste the code into a new module
2. Set the Tag properties correctly
3. Open your report using the syntax I mentioned above

 

[LakotaMan]

FancyPrairie,

I have been trying to get your functions to work.
Here's what I've done:

Copied the functions into a new module.
Put this tag on the list box:

Where=qryCBOPrograms.lngProgramID,Number

Put this in the click event of a button:

DoCmd.OpenReport "rptUWProgAll--GAFScores", acViewPreview, , BuildWhere(Me)

I keep getting an error message saying the program doesn't recognize the table I put in the tag.

I tried just using the field name:

Where=lngProgramID,Number

but that didn't work either.

Any suggestions about what I might have done wrong?

Thanks,
TWD

 

[FancyPrairie]

Is qryCBOPrograms the query the record source of the report?

 

[LakotaMan]

FP:

No, it is the record source for the list box. . .

So I changed the name to the qry that's the record source for the report --same error message.

 

[FancyPrairie]

I just did what I told you to do to make sure my directions were correct. And it worked for me. I'm assuming, then, that lngProgramID is not defined in your report. It needs to be.

 

[FancyPrairie]

Forget about the BuildWhere stuff for a moment. Open the report's query in design view. Does lngProductID exist in that query? If so, set the Criteria for lngProductID to something that exists. Then run the query. You should only see the records that match your criteria. Now open the query in SQL view. What's the syntax for the Where clause? Is it "Where lngProductID=2"? If so, then the Tag property should be "Where=lngProductID,number". Basically, you want BuildWhere to return the Where clause with the same syntax as if you had added it via the query designer. Therefore, you need to provide the correct name in the Tag property.

 

[LakotaMan]

Yes, lngProgramID (my field) exists in the query, I did as you suggested, query runs fine.

I have used copy and paste to get the field name correct, my styntax is just as stated in the 3rd code example above, still getting the same error message.

 

[FancyPrairie]

Instead of issuing the command to open the report (i.e. Docmd.OpenReport...) via the onclick event, issue this instead: Msgbox BuildWhere(Me)

 

[LakotaMan]

Same message. I hit debug to get the code it stops at:

   If (strFunction = "FieldName") Then
                On Error Resume Next
                Eval (var(0))
                If (Err.Number) Then
                    Err.Clear
                    BuildWhere_GetTag = Trim(CStr(var(0)))

code halts at the Eval line
value of var(0) is "lngPrgramID

 

[FancyPrairie]

You can not be getting the "same" error using the Msgbox method (because you are not attempting to open a report). It doesn't care what your report's query looks like. It simply scans through the controls on your Report Criteria Form and looks for ones that contain "Where=" in the Tag property.

Let's make it simple.

Create a new form and place a list box on the new form. The Row Source property of the list box should point to a query that lists the ProductID and Product Name. The Bound Column property of the List Box points to the column assigned to ProductID.

The Tag property of the List Box should contain the following: Where=lngProductID,number

Now place a command button on your form and in the onclick event, add the following: Msgbox BuildWhere(Me)

Note, this form only contains 2 controls: List Box and Command button. Should only take you about 2 minutes to put this test together.

 

[dhookom]

Is this a typo

code halts at the Eval line
value of var(0) is "lngPrgramID"

?

Duane
Hook'D on Access
MS Access MVP