Generate a report from a subform-need help (1 Viewer)

[magster06]

Hello all,

I have asked a similar question before and with the help of another member I was able to generate a report using querydefs, but it does not seem to work with my current query.

The only difference between my other query and the new one is that the old one used a single source (table) for the query and the current one uses 3 tables.

I have two listboxes. The first listbox is populated from the query fields. The second listbox is populated with the following code:

Private Sub lstQFields_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strQuerySelection As String
    On Error GoTo ErrorHappened
 
    If lstQFields.ListIndex <> -1 Then
        If lstQFields = "Documents" Then
            lstQValues.RowSource = ""
            MsgBox "Searching attachments is not possible", vbInformation + vbOKOnly, "PSU Query"
            GoTo ExitNow
        Else
            strQuerySelection = "[" & lstQFields & "]"
        End If
    Else
        GoTo ExitNow
    End If
    Set db = CurrentDb
    strSQL = "SELECT DISTINCT " & strQuerySelection & " FROM QCaseReportFill WHERE nz(" & strQuerySelection & ","""") <> """" ORDER BY " & strQuerySelection & " ASC"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    If rs.EOF Then
        MsgBox "No matches found; Please check your criteria", vbInformation + vbOKOnly, "Report Query"
        GoTo ExitNow
    End If
    lstQValues.RowSource = ""
    Do While Not rs.EOF
        lstQValues.AddItem rs.Fields(0)
        rs.MoveNext
    Loop
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

And this is the code to populate the subform:

Private Sub lstQValues_AfterUpdate()
    Dim strQueryValueSelected As String, strQueryFieldSelected As String
    Dim db As DAO.Database
 
    On Error GoTo ErrorHappened
    Dim rs As DAO.Recordset
 
    If lstQValues.ListIndex <> -1 And lstQFields.ListIndex <> -1 Then
        strQueryFieldSelected = lstQFields.ItemData(lstQFields.ListIndex)
        strQueryValueSelected = lstQValues.ItemData(lstQValues.ListIndex)
        Set db = CurrentDb
 
        strSQL = "SELECT [" & strQueryFieldSelected & "],* FROM QCaseReportFill WHERE [" & strQueryFieldSelected & "] = '" & strQueryValueSelected & "'  ORDER BY '" & strQueryValueSelected & "' ASC"
        Debug.Print strSQL
        Set sfCaseReportFill.Form.Recordset = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
    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

This all works great, but for the life of me I cannot get a report to generate from the subform content.

Any help would be appreciated.

I have tried using querydefs:

Dim qdf as DAO.QueryDef
Dim db as DAO.Database
 
Set qdf = db.QueryDefs("QCaseReportFill")
    qdf.SQL = strSQL
    DoCmd.OpenReport "CaseReportFill", acViewPreview, , , acDialog

but it keeps throwing a "Circular Error"

I tried using a Select Case:

Select Case Me.lstQFields.ListIndex
        Case 1
            strCriteria = "[CaseNumber] " = Me.lstQValues
            Me.Requery
        Case 2
            strCriteria = "[FocusFDID]= '" & Me.lstQValues & "'"
            Me.Requery
        Case 3
            strCriteria = "[FocusLastName]= '" & Me.lstQValues & "'"
            Me.Requery
        Case 4
            strCriteria = "[Allegation] = '" & Me.lstQValues & "'"
            Me.Requery
        Case 5
            strCriteria = "[Action] = '" & Me.lstQValues & "'"
            Me.Requery
        Case 6
            strCriteria = "[FinalDisposition] = '" & Me.lstQValues & "'"
            Me.Requery
    End Select

but I am not sure if it is set up correctly.

I tried using the sql statement that fills the subform, but this does not return any records.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String
 
    If lstQValues.ListIndex <> -1 And lstQFields.ListIndex <> -1 Then
       strQueryFieldSelected = lstQFields.ItemData(lstQFields.ListIndex)
     strQueryValueSelected = lstQValues.ItemData(lstQValues.ListIndex)
 
   strSQL = "SELECT [" & strQueryFieldSelected & "],* FROM QCaseReportFill WHERE [" & strQueryFieldSelected & "] = '" & strQueryValueSelected & "'  ORDER BY '" & strQueryValueSelected & "' ASC"
   Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
   Debug.Print strSQL
     DoCmd.OpenReport "CaseReportFill", acViewPreview, , strSQL, acDialog

The debug.print shows that the statement is drawing what it is suppose to, but it will not run the report.

 

[JHB]

The where condition is wrong:
From the help-file:
WhereCondition
Optional
Variant
A string expression that's a valid SQL WHERE clause without the word WHERE.

 

[magster06]

JHB,

Thanks for the reply!

I have tried this, but it is not working yet:

Private Sub cmdOpenReport_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strQueryFieldSelected As String, strQueryValueSelected As String
   
    If lstQValues.ListIndex <> -1 And lstQFields.ListIndex <> -1 Then
        strQueryFieldSelected = lstQFields.ItemData(lstQFields.ListIndex)
        strQueryValueSelected = lstQValues.ItemData(lstQValues.ListIndex)
    
    Set db = CurrentDb
    strSQL = "SELECT [" & strQueryFieldSelected & "],* FROM QCaseReportFill WHERE [" & strQueryFieldSelected & "] = '" & strQueryValueSelected & "'  ORDER BY '" & strQueryValueSelected & "' ASC"
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    Debug.Print strSQL
    End If
    DoCmd.OpenReport "CaseReportFill", acViewPreview, , "[" & strQueryFieldSelected & "] = " & """ & Me.lstQValues & """

The report still shows all records.

 

[JHB]

Yeah - now is it and other place the fault lays, you have 3 ", correct is, if it is a string value "'".

DoCmd.OpenReport "CaseReportFill", acViewPreview, , "[" & strQueryFieldSelected & "] = " & """ & Me.lstQValues & """

So right is:

DoCmd.OpenReport "CaseReportFill", acViewPreview, , "[" & strQueryFieldSelected & "] = " & "'" & Me.lstQValues & "'"

 

[magster06]

I tried your suggestion, but the report now shows #name? for all of the fields.

Arrgh, this is so frustrating. It seems like it would be so easy that if the subform is getting the correct records that you can create a report from the subform.

 

[JHB]

What is the recordsource for the report?
Else post you database with some sample data.

 

[magster06]

JHB,

The recordsource is the same query for the that supplies the subform:

Here is the code that fills the subform

 strSQL = "SELECT [" & strQueryFieldSelected & "],* FROM QCaseReportFill WHERE [" & strQueryFieldSelected & "] = '" & strQueryValueSelected & "'  ORDER BY '" & strQueryValueSelected & "' ASC"

 

[magster06]

Ok, it is finally working!

Thanks JHB for the help!

This is what is looks like now:

Private Sub cmdGenerateReport_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strCaseFieldSelected As String, strCaseValueSelected As String
    Set db = CurrentDb
    If Me.lstCaseQFields.ItemsSelected.Count > 0 Then
        strCaseValueSelected = lstCaseQValues.ItemData(lstCaseQValues.ListIndex)
        strCaseFieldSelected = lstCaseQFields.ItemData(lstCaseQFields.ListIndex)
    Else
        MsgBox "You must make a selection in the Query Fields Box before you can generate a report!", vbOKOnly + vbExclamation, "No Selection Made"
        Me.lstCaseQFields.SetFocus
        Exit Sub
    End If
 
    DoCmd.OpenReport "Case Detail Report", acViewPreview, , "[" & strCaseFieldSelected & "] =" & "'" & strCaseValueSelected & "'", acDialog
End Sub