Report from filter -help needed

[magster06]

Hello all,

Below is the code I use to populate a subform. I would like to create a report from the information in that subform.

I have created a query "QPSU" and a report "PSU Report" for the subform. At the moment, I am getting all the records from the query in the report instead of the ones that populate the subform (which could be one or more).

Private Sub lstQueryResults_Click()
    Dim strQueryFieldSelected As String
    Dim strQueryValueSelected As String
    Dim db As DAO.Database
    
    On Error GoTo ErrorHappened
    Dim rs As DAO.Recordset
    
    If lstQueryResults.ListIndex <> -1 And lstQuerySelection.ListIndex <> -1 Then
        strQueryFieldSelected = lstQuerySelection.ItemData(lstQuerySelection.ListIndex)
        strQueryValueSelected = lstQueryResults.ItemData(lstQueryResults.ListIndex)
        Set db = CurrentDb
        
        PSUSubform.Form.text1.ControlSource = strQueryFieldSelected
        PSUSubform.Form.Label1.Caption = strQueryFieldSelected
        
'        strSQL = "SELECT [" & strQueryFieldSelected & "],* FROM PSU WHERE [" & strQueryFieldSelected & "] = """ & strQueryValueSelected & """"
'        Debug.Print strSQL
'        Set PSUSubform.Form.Recordset = db.OpenRecordset(strSQL, dbOpenSnapshot)
        
        If CurrentDb.TableDefs("PSU").Fields(strQueryFieldSelected).Type = dbDate Then
            PSUSubform.Form.Filter = "[" & strQueryFieldSelected & "] = #" & Format(strQueryValueSelected, "mm/dd/yyyy") & "#"
        Else
            PSUSubform.Form.Filter = "[" & strQueryFieldSelected & "] = '" & strQueryValueSelected & "'"
        End If
    Else
        GoTo exitnow
    End If
    
exitnow:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
ErrorHappened:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    Resume exitnow
    Resume
End Sub

I use the sql statement above with other reports, but with the above I am using filters instead and am not sure how to extract the info with them using querydefs.

Here is the code I use to generate a report with a sql statement:

Private Sub cmdRunPSUQueryReport_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    On Error GoTo errHandler
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("QPSU")
    qdf.SQL = strSQL
    
    DoCmd.OpenReport "PSU Report", acViewPreview, , , acWindowNormal
    Set db = Nothing
exitnow:
    On Error Resume Next
    Set db = Nothing
    Exit Sub
errHandler:
    MsgBox "You must select a field first to generate a report!", vbInformation + vbOKOnly, "No Field Selected"
    Resume exitnow
    Resume
End Sub

The reason I had to change to using a filter is because my sql is getting strings instead of dates and when I call a date, then I get a type mismatch error.

Any help would be greatly appreciated!

 

[magster06]

Ok, so instead of pulling the info from the subform, I decided it might be easier to pull the info from the listbox.

I placed this in the query (QPSU) under the field "Case Number" :

[Forms]![PSU Query].[lstQueryResults]

and of course if I select "Case Number" , then I get the desired results. If I choose anything other than "Case Number", then I get nothing (which is expected).

My question is: how can I get a report generated from the second listbox?

Would I have to place a "where" criteria in the docmd.openreport? if so, then how would this look?

I have tried this, but it does not work:

 DoCmd.OpenReport "PSU Report", acViewPreview, , , acWindowNormal, " [Forms]![PSU Query].[lstQueryResults]"

Any help would be appreciated!