Re: Selecting certain items for a report

From: Sandra Daigle (Invalid_at_KeepYourSpam.org)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 10:44:14 -0500

Hi Chuck,

(Whoops, hit send somehow)

Sorry about that - I didn't really give you enough information. The article
on MVPS.org implies that you need to use the resulting SQL as the
recordsource for your report.

Here's how I do it - a little different than described in the articles but
it works well especially when the recordsource for the report is a complex
query. Create a new query that only pulls data from the product form - you
only need to include the primary key column from the table. Save it as
"tmpSelectProducts". For now it doesn't have any criteria - your code will
actually modify the criteria later. Join this new table to your recordsource
query for the report - this will allow the query to serve as a filter for
the report.

Note that you could delete and recreate the query each time, but 'I
generally leave the query and just modify the SQL. It helps me debug and
doesn't really cause any issues.

Now your code for opening the report will look something like this:

Private Sub cmdReport_Click()
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim varItem As Variant
Dim StrWhere As String
'You could delete and recreate the query each time,
'I generally leave the query and just modify the SQL
'On Error Resume Next
'db.QueryDefs.Delete ("tmpSelectProducts")
'On Error GoTo Proc_err
Set db = CurrentDb()
Set qdf = db.QueryDefs("tmpSelectProducts")
For Each varItem In Me.lstSelectProducts.ItemsSelected
    StrWhere = StrWhere & "ProductID=" &
Me.lstSelectProducts.ItemData(varItem) & " OR "
Next varItem
'Add "Where" and Trim off Trailing " Or "
If Len(StrWhere) > 0 Then
    StrWhere = "Where " & Left(StrWhere, Len(StrWhere) - 4)
End If
qdf.SQL = "Select Productid from tblProducts " & StrWhere
DoCmd.OpenReport "rptSelectedProducts", acViewPreview

End Sub

Note that I'm using ProductID - which would be a numeric primary key. If you
use a text value for selecting records you need to wrap the value in quotes
when you build the Where clause. This is easily done by putting an extra
pair of double quote characters (") inside the strings on either side of the
&. Each embedded pair of quote characters will be replaced with a single
character which will remain in the final string. The outside pair are string
delimiters and aren't part of the actual literal string.

    StrWhere = StrWhere & "ProductName=""" &
Me.lstSelectProducts.ItemData(varItem) & """ OR " 

-- 
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.
Chuck W wrote:
> Sandra,
>
> Thanks for your help.  I went through the directions and
> created a form on the northwind database.  It stops where
> you create a form with the multiselect list box and a
> command button.  When you select some items from the list
> box and click the command button, It shows up at the
> bottom as saying "1 of 4 (which is how many records I
> selected) and then "Filtered".  What I really want to do
> is to create a report of information contained in the
> four records I just selected.  How would I add this next
> step?
>
> Thanks,
>
> Chuck
>
>> -----Original Message-----
>> Hi Chuck,
>>
>> A Multiselect listbox is ideal for this type of thing. Take a look
>> at the following articles -
>>
>> http://www.mvps.org/access/forms/frm0007.htm
>>
>> How to Use a Multi-Select List Box to Filter a Form
>> http://support.microsoft.com/default.aspx?scid=kb; [LN];135546
>>
>>
>> --
>> Sandra Daigle
>> [Microsoft Access MVP]
>> For the benefit of others please post all replies to this newsgroup.
>>
>> ChuckW wrote:
>>> Hi,
>>>
>>> I have a table called products that has four fields:
>>> ProductName, Ingredients, Dosage, and Notes.  There are
>>> about 250 products.  I want to create an application that
>>> allows a person to select anywhere from 3 to about 10
>>> products out of these 250 and have these selected
>>> products appear on a report.  I can create either radio
>>> boxes or drop downs but am not sure which to use.  Can
>>> anyone help me get started with doing this?
>>>
>>> Thanks,
>>>
>>> Chuck
>>
>> .
Loading