Query Def Objects for parameterized Access query

[corai]

Hi there,

I am trying to create a module with VBA to output approx 500 access reports into a folder. The access report is linked to a query called “qryRptS” with each record in the query responsible for one report. The problem is that qryRptS is a parameterised query based on 2 date fields, (Forms!frmCompany![StartDate] and Forms!frmCompany![EndDate]), which are entered on a form.

The code I have produced returned the error “Too few parameters, expected 2”, which led me to believe that VBA doesn’t like parameterised queries and needs to have exact parameter values supplied in the code. I think this can be done using something called QueryDef objects but as I don’t have much experience with VBA I wonder whether anyone could provide some guidance.

Here is what I have so far. I think I need to incorporate the lines in bold-italic below but I don’t really understand this part. Currently this code is generating the error: “Data type mismatch in criteria expression”. Please let me know if you have any thoughts…

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryRptS")
qdf![Forms!frmCompany!StartDate] = #1/1/2011#
qdf![Forms!frmCompany!EndDate] = #1/1/2012#

strSQL = "Select * FROM qryRptS "

Set rst = qdf.OpenRecordset(dbOpenDynaset)

'For Each prm In qdf.Parameters
' prm.Value = Eval(prm.Name)
'Next prm

filename = Forms!frmCompany!TradingName & Forms!frmCompany!CompanyID
filename = Replace(filename, "/", "-")

rst.MoveFirst

Do While rst.EOF = False

DoCmd.OpenReport "rptQryS", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "rptQryS", "PDFFormat(*.pdf)", "\\HBSRV01\common\PROJECTS\Q1 Test\" & filename & ".pdf", False, "", , acExportQualityPrint
DoCmd.Close acReport, "rptQryS"

rst.MoveNext

Loop

ReportGeneration_Exit:
Exit Function

ReportGeneration_Err:
MsgBox Error$
Resume ReportGeneration_Exit

End Function

 

[VilaRestal]

[Forms!frmCompany!StartDate] and [Forms!frmCompany!EndDate] are slightly odd names for parameters. I've never tried treating such expressions as parameters but I think it should be ok.

However, you shouldn't need to set the parameters if frmCompany is open and those fields have values.

Have you tried:

Dim filename As String
filename = Forms!frmCompany!TradingName & Forms!frmCompany!CompanyID
filename = Replace(filename, "/", "-")
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryRptS")
With rst
    .MoveFirst
    Do While Not .EOF
        DoCmd.OpenReport "rptQryS", acViewReport, "", "ID = " & .Fields("ID"), acNormal
        DoCmd.OutputTo acOutputReport, "rptQryS", "PDFFormat(*.pdf)", "\\HBSRV01\common\PROJECTS\Q1 Test\" & filename & " ID " & .Fields("ID") & ".pdf", False, "", , acExportQualityPrint
        DoCmd.Close acReport, "rptQryS"
        .MoveNext
    Loop
End With

?

Note that each report opened would need to be with a where clause for the current record in the recordset. Not knowing the names of your fields I have put in "ID = " & .Fields("ID"). Change that to what it should be (and include quotes in the string if it's a text field).

Also each file will need a different name for each loop (in your code there was only one filename for all records). I added & " ID " & .Fields("ID") &. Again, change the to something appropriate.

 

[VilaRestal]

If you still get the parameters message then the way to do it would be:

Dim filename As String
filename = Forms!frmCompany!TradingName & Forms!frmCompany!CompanyID
filename = Replace(filename, "/", "-")
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qryRptS")
qdf.Parameters("[Forms!frmCompany!StartDate]") = Forms!frmCompany!StartDate
qdf.Parameters("[Forms!frmCompany!EndDate]") = Forms!frmCompany!EndDate
Dim rst As DAO.Recordset
Set rst = qdf.OpenRecordset()
With rst
    .MoveFirst
    Do While Not .EOF
        DoCmd.OpenReport "rptQryS", acViewReport, "", "ID = " & .Fields("ID"), acNormal
        DoCmd.OutputTo acOutputReport, "rptQryS", "PDFFormat(*.pdf)", "\\HBSRV01\common\PROJECTS\Q1 Test\" & filename & " ID " & .Fields("ID") & ".pdf", False, "", , acExportQualityPrint
        DoCmd.Close acReport, "rptQryS"
        .MoveNext
    Loop
End With

 

[corai]

Hi VilaRestal,

Thanks for getting back to me. In the latest code you posted I don't get the parameter error ("Too few parameters. Expected 2") but I get an error which says "data type mismatch in criteria expression"...

Do you have any ideas about what could be casuing this?

 

[VilaRestal]

Which line produces that error?

 

[corai]

The line producing the error is:

Set rst=qdf.OpenRecordset()

 

[corai]

I set the parameter type in the query "qryRptS" as Date/time for both instances and it seems to be working now