Help to create a loop for printing

[bosk00]

We have single page reports that are unique to each state, that are added to our clients annual report. I have a query that will return all of the information that I need for every client that need a report for a specified month. The month needed and a line of text are entered onto a form. A button on the form will run the query. Where I am having a problem is creating the code that will take each line returned by the query and print the report for the correct state. The name of the query is PLSL3, here is the code.

SELECT DISTINCT [QAMAIN].[STATE], [QAMAIN].[COMPANY], [QAMAIN].[T01DESM], [QAMAIN].[T01FEE], [QAMAIN].[T01COMCD], [QAMAIN].[T01PLSL], [QAMAIN].[T01CONDTM], [QAMAIN].[T01CLINOP], [QAMAIN].[T01CLILOP], [Forms]![TDNR Form]![TDNRDate] AS [Date]
FROM QAMAIN
WHERE ((([QAMAIN].[STATE])<>"CT" And ([QAMAIN].[STATE])<>"MA" And ([QAMAIN].[STATE])<>"NY" And ([QAMAIN].[STATE])<>"FL" And ([QAMAIN].[STATE])<>"GA" And ([QAMAIN].[STATE])<>"PA" And ([QAMAIN].[STATE])<>"TX") And (([QAMAIN].[T01CONDTM])=[Forms]![TDNR Form]![Month]))
ORDER BY [QAMAIN].[STATE], [QAMAIN].[COMPANY]
WITH OWNERACCESS OPTION;

I named the reports the same as the states that are returned by the query. This is my attempt at the code to do the loop.

Public Sub PrintTDNR()
Dim db As Database
Dim rs As DAO.Recordset
Dim varx As Variant
Set db = CurrentDb
[red]Set rs = db.OpenRecordset("PLSL3")[/red]
    Do Until rs.EOF = True
    varx = DLookup("[STATE]", "PLSL3")
    DoCmd.OpenReport varx, acNormal
    rs.MoveNext
    Loop
    Set varx = Nothing
    Set rs = Nothing
    Set db = Nothing

End Sub

When I run it I get an runtime error 3061, Too few parameters at the highlighted line. I am unable to see where the error is coming from. Any and all help is appreciated. Thank You

Alan
Senility at its finest

 

[mordja]

Your query has the parameter [Forms]![TDNR Form]![TDNRDate] AS [Date]. When opening a recordset programmatically you need to pass the parameter explicitly other wise you will get a message like "Too few parameters ....".

Instead you could do something like

Dim strDate as string

strDate = [Forms]![TDNR Form]![TDNRDate]
Set db = CurrentDB
Set qdf = db.QueryDefs("PLSL3")

qdf.Parameters("[Forms]![TDNR Form]![TDNRDate]")= strDate
Set rst = qdf.OpenRecordset()


If you are only going to use the query this way maybe you could give the parameter a simpler name.

Mordja

 

[bosk00]

I changed the program as you suggested, but still get the 3061 runtime error on the highlighted line.

Public Sub PrintTDNR()
Dim db As Database
Dim rs As DAO.Recordset
Dim varx As Variant
Dim strDate As String
strDate = [Forms]![TDNR Form]![TDNRDate]
Set db = CurrentDb
Set qdf = db.QueryDefs("PLSL3")
qdf.Parameters("[Forms]![TDNR Form]![TDNRDate]") = strDate
[red]Set rs = qdf.OpenRecordset()[/red]
    Do Until rs.EOF = True
    varx = DLookup("[STATE]", "PLSL3")
    DoCmd.OpenReport varx, acNormal
    rs.MoveNext
    Loop
    Set varx = Nothing
    Set rs = Nothing
    Set db = Nothing

End Sub

I want to thank you for your help, it is appreciated.

Alan
Senility at its finest

 

[PHV]

You have 2 parameters in the query:
[Forms]![TDNR Form]![TDNRDate]
[Forms]![TDNR Form]![Month]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mordja]

Aah, PHV is correct. You will need to explicitly declare both. Something like

Dim strDate, strMonth as string

strDate = [Forms]![TDNR Form]![TDNRDate]
strMonth = [Forms]![TDNR Form]![Month]
Set db = CurrentDB
Set qdf = db.QueryDefs("PLSL3")

qdf.Parameters("[Forms]![TDNR Form]![TDNRDate]")= strDate
qdf.Parameters("[Forms]![TDNR Form]![Month]")= strMonth
Set rst = qdf.OpenRecordset()


Mordja

 

[bosk00]

I want to thank you both for your help. A server crash will put this on the back burner for a couple of days. I added the second parameter after PHV's note and no error is reported. The report didn't work correctly, but I havent had a chance to start chasing that down yet. Again, thank you both for your help.

Alan
Senility at its finest