Adding parameter to query used to open reports

[fibayne]

Hi

I am using the code below to produce individual valuation reports for each policy, rptVALBATCH.
To limit the number of reports produced in one go, there is a parameter query hard coded on the policy number field in the report query of Policy Number, Between "CL10000074" And "CL10000354" whihc works fine but I must open the query each time to run a new batch of reports.

I tried changing the parameters to Between [Policy Number From:] And [PolicyNumber To:] on the query and now get the error message
Run-time error 3061 Too few parameters.Expected 2

I have searched and think this shoul dbe done with the button code below by adding in -
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Can anyone help me with this ?

Thanks in advance for any help

Fi
-------------------------------------------------------------------------------------
Private Sub cmdVALBATCH_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFilename As String
Dim MyPath As String
Dim temp As String

MyPath = "Z:\USERNAME\ Weekly Reports\December 2017 Valuations\IH\Jurisdiction"

Set db = CurrentDb()

Set rs1 = db.OpenRecordset("SELECT distinct [Policy No]FROM [qryVALBATCH]", dbOpenSnapshot)

Do While Not rs1.EOF

temp = rs1("PolicyNo")
MyFilename = rs1("PolicyNo") & ".PDF"

DoCmd.OpenReport "rptVALBATCH", acViewReport, , "[PolicyNo]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename
DoCmd.Close acReport, "rptVALBATCH"
DoEvents

rs1.MoveNext
Loop

rs1.Close
Set rs1 = Nothing
Set db = Nothing


End Sub

 

[Accessanitor]

Why don't you turn this into a sub where you pass along the name of the records? Then in your main code you simply run the same sub for every record.

 

[fibayne]

Hi many thanks for replying, my thoughts were to run batches of the individual reports using a parameter, the user would enter Policy number from and Policy number to, which would pick up the data for those policies and create the individual report, the code does this currently however i need to go int to report query and hard code the policy number from and to for each batch I want to create?

 

[Minty]

If you selected the policy numbers on a form why not simply refer to those in the query directly?

Between Forms!YourFormName!YourControlFromName  AND  Forms!YourFormName!YourControlToName

Is the query a cross tab?

 

[Gasman]

Why can't you filter the query with your select statement?
Something along the lines of

strSQL = "SELECT distinct [Policy No]FROM [qryVALBATCH]"
strSQL = strSQL & " WHERE [Policy No] BETWEEN '" & Me.PolicyFrom & "' AND '" & Me.PolicyTo & "'"

Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)

HTH

 

[fibayne]

Hi
Thanks for your replies, I think your suggestion Gasman is what would work, can you help with what is wrong on the code below ?

I have this criteria on the [Policy No] field on the query qryCSVOMIGIBBATCH for the report rptSOVValOMIBATCH
Between [Policy From:] And [Policy To:]

-------------------------------------------------------------------------------

Private Sub cmdBatchGIBB1_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFilename As String
Dim MyPath As String
Dim temp As String


MyPath = "C:\Users\iComms\Documents\New folder\Fiona Stuff"

Set db = CurrentDb()

strSQL = "SELECT distinct [Policy No]FROM [qryCSVOMIGIBBATCH]"
strSQL = strSQL & " WHERE [Policy No] BETWEEN '" & Me.[Policy No] & "' AND '" & Me.[Policy No] & "'"


Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)


Do While Not rs1.EOF

temp = rs1("Policy No")
MyFilename = rs1("Policy No") & ".PDF"

DoCmd.OpenReport "rptSOVValOMIBATCH", acViewReport, , "[Policy No]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename
DoCmd.Close acReport, "rptSOVValOMIBATCH"
DoEvents

rs1.MoveNext
Loop


rs1.Close
Set rs1 = Nothing
Set db = Nothing


End Sub

 

[isladogs]

Hi Fiona

You need a backslash at the end of MyPath value
Also add a space before FROM in strSQL line

Private Sub cmdBatchGIBB1_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFilename As String
Dim MyPath As String
Dim temp As String


MyPath = "C:\Users\iComms\Documents\New folder\Fiona Stuff[B][COLOR="Red"]\[/COLOR][/B]"

Set db = CurrentDb()

strSQL = "SELECT DISTINCT [Policy No] FROM [qryCSVOMIGIBBATCH]"
strSQL = strSQL & " WHERE [Policy No] BETWEEN '" & Me.[Policy No] & "' AND '" & Me.[Policy No] & "'"

Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)

Do While Not rs1.EOF

temp = rs1("Policy No")
'Debug.Print temp
MyFilename = rs1("Policy No") & ".PDF"

DoCmd.OpenReport "rptSOVValOMIBATCH", acViewReport, , "[Policy No]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename
DoCmd.Close acReport, "rptSOVValOMIBATCH"
DoEvents

rs1.MoveNext
Loop

rs1.Close
Set rs1 = Nothing
Set db = Nothing

End Sub

If it still doesn't work, try adding breakpoints in your code to see where it fails
You can also add lines like Debug.Print temp
This will print the value to the VBE Immediate window so you can check its what you expect

 

[fibayne]

Hi ridders

Thank you for replying, it is failing here when I run the report

strSQL = strSQL & " WHERE [Policy No] BETWEEN '" & Me.[Policy No] & "' AND '" & Me.[Policy No] & "'"

with error message -

can't find the field '|1' referred to in your expression

I've searched the error but its still not clear to me what causes this error...any idea what it could be ?

thanks Fi

 

[isladogs]

There are several things that need looking at here:

1. Is the field 'Policy No' a number field as the name suggests.
If so you need to get rid of the text delimiters

strSQL = strSQL & " WHERE [Policy No] BETWEEN " & Me.[Policy No] & " AND " & Me.[Policy No] & ";"

2. However, the WHERE clause doesn't make sense as written
If you are using the same value from a textbox 'Policy No' on your form then it should be:

strSQL = strSQL & " WHERE [Policy No] = " & Me.[Policy No] & ";"

or if it's a text datatype

strSQL = strSQL & " WHERE [Policy No] = '" & Me.[Policy No] & "';"

Also in that case, you don't need to loop through any recordset code as you only have one record

3. However, in post 1, you mentioned [Policy Number From:] And [PolicyNumber To:]
NOTE: You should not be using special characters like : in field or control names

So lets assume you do have 2 controls called PolicyFrom & PolicyTo (better without spaces also) then

strSQL = strSQL & " WHERE [Policy No] BETWEEN " & Me.PolicyFrom & " AND " & Me.PolicyTo & ";"

4. Also please check you do have a folder "C:\Users\iComms\Documents\New folder\Fiona Stuff"

I think that's everything ...for now
If after doing the appropriate changes above, it still doesn't work then please....
Add the lines Debug.Print strSQL and Debug.Print MyFilename and post the results you get

Then upload the relevant parts of your database removing anything confidential. If so, tell us what to look at in the db

 

[fibayne]

Hi
The code needs to run a batch of individual PDF's reports per policy number, the original code I posted worked fine apart from, to change the set of policies I wanted to run I had to go into the query and and on the Policy No field, add to the criteria the range of policies to produce the PDF reports for eg
Between "100" And "200"

What I hoped to do was have this code, which is on the command button ask for the range then produce the PDF reports based on that range.
1.Policy No is a field on the underlying table
2.I'm not using a field on the form to enter the policy no, perhaps that would be easier?
3. This was on the criteria of the reports query of the Policy No field
4. Yes this is the correct path and the PDF's do store there when using the original code that I add the range on the query to mentioned above.

thanks Fi

 

[isladogs]

2.I'm not using a field on the form to enter the policy no, perhaps that would be easier?

I would use two textboxes on the form for users to enter the start & end values.
Better still use 2 combo boxes with row sources equal to the available 'Policy No' values

Between "100" And "200"

Still not clear whether it's a number field or a text field due to the quote marks.
As long as your clear, that's ok!

 

[fibayne]

thank you
will try that now, the Policy No field is a text field, there are many policies with letters and numbers.
thanks again will post my results !!!

 

[Gasman]

Sorry, only just had the email, but I can see that Ridders is helping you further, so no real need for any more input from me.
You will get more experienced help from Ridders as well.

 

[Accessanitor]

Another simple way to debug is to let it provide a msgbox to show the result of the query string. Then look at that query string and see what's missing.

strSQL = strSQL & " WHERE [Policy No] BETWEEN " & Me.PolicyFrom & " AND " & Me.PolicyTo & ";"
msgbox strSQL

Then look for the discrepancies between what the query should be and what the msgbox is displaying.

I have also found that with some queries I had to add something like

"(" & Chr(39) & Me.PolicyTo & Chr(39) & ");"

For instance (not related to your code exactly, just to show what I mean)

Sub UpDateRuntime()
    Set dbs = CurrentDb
    Dim timetaken
    timetaken = DateDiff("s", StartTime, Now())
    dbs.Execute " INSERT INTO [Runtime] " & _
    "( [Report run time]) VALUES " & _
    "(" & Chr(39) & timetaken & Chr(39) & ");"
    UpdateLog "Updated Runtime, time taken was " & timetaken & " seconds"
    dbs.Close
End Sub

It might be that the query gets confused by the "." after me, so the ' ( chr(39) ) might help it see that as a text field.