Using a recordset to populate a report?

[overunity]

I have a form with a listbox that gets categories from a table. based on which categories were selected (multiselect), a recordset is built from another table. But now that i have a recordset floating around in RAM, how the heck do i spit the data out?

I want to use that recordset to populate a Report I have made. how!?

heres what i have:

Listbox (multiselect)
button to click and fire off this code piece

[COLOR="YellowGreen"]' Set query string[/COLOR]
strSQL = "SELECT categories.category, [Public programs contacts].[Last Name], " & _
"[Public programs contacts].[First name], [Public programs contacts].Cat_ID " & _
"FROM categories INNER JOIN [Public programs contacts] ON categories.ID=[Public programs contacts].Cat_ID " & _
"WHERE cat_id IN "
[COLOR="YellowGreen"]' init working criteria[/COLOR]
strSQL2 = ""

[COLOR="yellowgreen"]'Loop to build ID list[/COLOR]
If Me!box_category_list.ItemsSelected.Count > 0 Then
For Each varItem In Me!box_category_list.ItemsSelected
strSQL2 = strSQL2 & Me!box_category_list.ItemData(varItem) & ","
Next varItem

strSQL2 = strSQL & "(" & Left(strSQL2, Len(strSQL2) - 1) & ");"

[COLOR="yellowgreen"]'Recordset[/COLOR]
Dim db As DAO.Database, rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL2)
    
    While Not rs.EOF

        Debug.Print rs.Fields(0)
          
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    
    [COLOR="yellowgreen"]'somehow call this report and use data from above recordset[/COLOR]
    DoCmd.OpenReport "My_Report"

My original problem was i tried building a query, but I couldnt get the criteria to work correctly.
I needed to put inside the CAT_ID Criteria field, a IN (Forms![Report - Category]![Text18])
In this case text18 = "7,3,8" (or some combination of IDs)
for some reason the query would pull nothing unless text18 had only 1 item in it....

leading me to the above VB attempts to do the same thing

HELP!

 

[pbaldy]

Why bother with a recordset when you have an SQL string that could be the source of your report?

 

[overunity]

hrmm

I know i can easily just dump a static SQL statement on a report, but this is dynamic and currently inside a variable.

How do I get that strSQL2 value to work on a prebuilt report? Sounds like it would be the easy solution, but i dont know of any methods to do it. -thx

 

[The_Doc_Man]

If the displayed fields for the report don't change but the where clause does, look into building the report with no filter, then build your WHERE clause only and put it into the report filter property. Be sure to turn on report filtering in the same code that sets the filter value.

 

[pbaldy]

I actually would do what the Doc Man suggested (build a wherecondition to use with DoCmd.OpenReport), but what I was offering was that in the open event of the report, you could set it's source to your variable (which would have to be public).

 

[Keith Nichols]

Hi Folks,

I have read a number of threads on this subject and am now a little confused.

My form has a combo box that uses a Select Case to apply 1 of 5 filters to the form and I have a formatted report that is essentially based on the same query as the form.

Here is the bit that has me stumped: How do I write the Where Clause to show the filtered set of records displayed by the form? Are the filtered form records the same as the form RecordSet?

My form is called fdlgProjectDetails
pk of the underlying table is ProjectID
Combo box is cmbSelectRecords
Report in rptPrjListReport

Would this be easier to do if I converted the filters to SQL in the VB?

As a supplementary question: I also need to send a text string for the report title based on the filter used on the form. I can pick the text from a select case but how to send it? Any pointers?

Many thanks in advance.

 

[pbaldy]

Following your earlier question using a listbox, here's an example from one of my db's using a listbox to build a wherecondition for a report (relevant parts only):

  strSql = "Type = '"
  'enumerate selected items and concatenate to strSQL
  For Each varItem In ctl.ItemsSelected
    strSql = strSql & ctl.ItemData(varItem) & "' OR Type = '"
  Next varItem

  'Trim the end of strSQL
  strSql = left$(strSql, Len(strSql) - 12)

  DoCmd.OpenReport "rptDriversByType", acViewPreview, , strSql