Generate a report from a subform?

[magster06]

Hello all,

I am trying to generate a report from my subform, but I am not having much success in doing so. I am getting either all the records or nothing in my report.

My subform is populated from an unbound listbox; here is the code that is used to populate the subform:

'Populates the subform from the second listbox
    Dim strQueryFieldSelected As String
    Dim strQueryValueSelected As String
    Dim db As DAO.Database
    
    On Error GoTo ErrorHappened
    
    If lstQValues.ListIndex <> -1 And lstQFields.ListIndex <> -1 Then
        strQueryFieldSelected = lstQFields.ItemData(lstQFields.ListIndex)
        strQueryValueSelected = lstQValues.ItemData(lstQValues.ListIndex)
        Set db = CurrentDb
       
        'Me.txtCounter = DCount("strqueryfieldselected", "PSU")
    
        If CurrentDb.TableDefs("LightDuty").Fields(strQueryFieldSelected).Type = dbDate Then
             strSQL = "SELECT [" & strQueryFieldSelected & "], * FROM LightDuty WHERE [" & strQueryFieldSelected & "] = #" & strQueryValueSelected & "# ORDER BY #" & strQueryValueSelected & "# ASC"
        Else
             strSQL = "SELECT [" & strQueryFieldSelected & "], * FROM LightDuty WHERE [" & strQueryFieldSelected & "] = """ & strQueryValueSelected & """ ORDER BY """ & strQueryValueSelected & """ ASC"
        End If
        
        Debug.Print strSQL
        Set subQResults.Form.Recordset = db.OpenRecordset(strSQL, dbOpenSnapshot)
    Else
        GoTo ExitNow
    End If
    
ExitNow:
    On Error Resume Next
    Set db = Nothing
    Exit Sub
ErrorHappened:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    Resume ExitNow
    Resume

And this is what I am trying to open my report with:

 DoCmd.OpenReport "LightDuty", acViewPreview, , acDialog

With "LightDuty" being my report.

Any help would be appreciated.

 

[Isskint]

Your report Light Duty would need the same recordsource as the form. Use the SQL string you build to supply the recordsource to the form to supply the recordsource to the report (it would probably be better to make this a query which would source the report)

 

[magster06]

it would probably be better to make this a query which would source the report

I see what you mean, but I am not sure I know how to do this.

Would I place my code to populate the subform into a module and try to call it in the source for the query?

or a function and call it this way:

DoCmd.OpenReport "LightDuty", acViewPreview, , "LightDuty = funcSource

I apologize for my lack of Access skills

 

[Isskint]

I apologize for my lack of Access skills

I would say you are selling yourself a little short. The code you have displayed thusfar has (and will again) left many people whimpering in a dark corner trying to achieve


The way i approach Dynamic Queries, is to create a standard select query and provide some example criteria. Save the query and then create the report using the query. Simple to this point. Now copy the SQL for the query and use that as your 'platform' to build the SQL you need each time. You have covered the SQL part in your code already. All you will need to do is use the QueryDefs object/collection to change the SQL of your stored query, then open the report.
Add this to the declarations at the start of your code

[I][COLOR=black][FONT=Verdana][I]Dim qdf As DAO.QueryDef[/I][/FONT][/COLOR][/I]
[I][FONT=Verdana][COLOR=black][I][FONT=Verdana][I]Set qdf = db.QueryDefs("NameOfYourQueryHere”)[/I][/FONT][/I][/COLOR][/FONT][/I]

and this after you have created your SQL string

[I][COLOR=black][FONT=Verdana][I]qdf.SQL = strSQL[/I][/FONT][/COLOR][/I]
[I][COLOR=black][FONT=Verdana][I]DoCmd.OpenReport "NameOfYourReportHere"[/I][/FONT][/COLOR][/I]

 

[magster06]

Isskint,

Part of the code came from my old vb6 for loading a datagrid; the other part came from a friend I made on this forum (DrallocD). Trust me when I say I am a beginner at Access.

It will never cease to amaze me of the generosity that is given everyday on this forum. People who take time out of their busy lives to help strangers with common interest. AMAZING!

I applaud you and everyone else on this forum (and I am not just saying this to get help, lol, I really mean it)!

Anyways, I'll give it a go!

 

[Isskint]

It will never cease to amaze me of the generosity that is given everyday on this forum. People who take time out of their busy lives to help strangers with common interest. AMAZING!

I applaud you and everyone else on this forum (and I am not just saying this to get help, lol, I really mean it)!

Anyways, I'll give it a go!

It is for this very reason that I take the time to try and help. The satisfaction i gain from helping someone is not measurable. It fills me with a warm, fuzzy feeling (a bit like when you pee yourself). Not to measure i feel i have an obligation to pay back the help i have received in the past from some of these talented people. Payback, it may payforward

 

[magster06]

Isskint,

This is what I finally ended up with:

Dim db As DAO.Database
Set db = CurrentDb
 
Set qdf = db.QueryDefs("QLightDuty")
qdf.SQL = strSQL
 
DoCmd.OpenReport "Report_LightDuty", acViewPreview,,,acDialog

Seems to work; do you see anything that should be changed?


Now, in order to print the report, should I place a cmd button on the report itself?

 

[Isskint]

Code looks OK.

Never put command buttons on reports. If you want to open it in preview mode, the user just needs to use the normal print options (Toolbar OR File meu>>Print). You could add a button to the form that generates the report to print. That way you could have the current command button as "Preview report" and the new command button as "Print report". the Print code would be DoCmd.OpenReport "Report_LightDuty", acViewNormal

 

[magster06]

Thanks for your help Isskint; it is very much appreciated.