DoCmd.OpenReport Error (2 Viewers)

dvarvel

Registered User.
Local time
Today, 04:59
Joined
Nov 28, 2017
Messages
22
I am trying to export a single report to multiple pdfs. I think I got the Loop part working, but I can't figure out why I'm getting the attached error. I am ignorant when it comes to VBA, I only know what I have taught myself and learned in these forums. Any help would be appreciated.
 

Attachments

  • VBA Error.docx
    60 KB · Views: 102

Gasman

Enthusiastic Amateur
Local time
Today, 11:59
Joined
Sep 21, 2011
Messages
14,038
Debug.print strWhere and you should see your error.

Your SQL string is incorrect.

Edit: You have already?
It should either have = or IN not both
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:59
Joined
Aug 30, 2003
Messages
36,118
You need single quotes around the state value, since it's text.
 

dvarvel

Registered User.
Local time
Today, 04:59
Joined
Nov 28, 2017
Messages
22
Thanks, Gasman, but I don't understand what you mean by "It should either have = or IN not both".
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:59
Joined
Aug 30, 2003
Messages
36,118
I thought there was an incomplete In() clause at first too. IN is the abbreviation for a US state, so with the field name I'm guessing that's the problem.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:59
Joined
Sep 21, 2011
Messages
14,038
Thanks, Gasman, but I don't understand what you mean by "It should either have = or IN not both".

I didn't realise it was the state abbreviation for Indiana?
You can have SQL along the lines of WHERE State IN ("IN","LA","CA") and it would return for those three states.
 

dvarvel

Registered User.
Local time
Today, 04:59
Joined
Nov 28, 2017
Messages
22
I want the code to loop through the entire query and create a separate report for every state. I have all 50 states in the report, not just Indiana. I don't know why it is getting stuck with IN.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:59
Joined
Aug 30, 2003
Messages
36,118
Based on what you're doing, I think you want to end up with:

State = 'IN'
 

dvarvel

Registered User.
Local time
Today, 04:59
Joined
Nov 28, 2017
Messages
22
pbaldy - I changed: [FONT=&quot]strWhere = LoopedField & " = " & LoopedFieldValue[/FONT]
[FONT=&quot]TO:[/FONT]
[FONT=&quot][FONT=&quot]strWhere = LoopedField & LoopedFieldValue[/FONT][/FONT]
[FONT=&quot][FONT=&quot]
[/FONT][/FONT]
[FONT=&quot][FONT=&quot]Now it is asking me to Enter Parameter Value for StateIN

That wasn't it. lol - Still trying to figure it out. I think it has to do with putting single quotes around the LoopedFieldValue.
[/FONT][/FONT]
[FONT=&quot][FONT=&quot]
[/FONT][/FONT]
 
Last edited:

dvarvel

Registered User.
Local time
Today, 04:59
Joined
Nov 28, 2017
Messages
22
Ok. I got it to output a pdf, but it only saved the state of IN. It should loop through the entire query and save a report for each state.
 

Minty

AWF VIP
Local time
Today, 11:59
Joined
Jul 26, 2013
Messages
10,354
Post up the whole actual code you are using , press the # in the editor to put it into code tags to make it readable.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:59
Joined
Aug 30, 2003
Messages
36,118
What is the code now? Are all the states in the "Domain" table/query?
 

dvarvel

Registered User.
Local time
Today, 04:59
Joined
Nov 28, 2017
Messages
22
I finally got it to work. Now, the problem is that since the query has each state listed multiple times, the loop is looking at each line and creating a report for based on the state on that line. My query can have up to 50k lines O.O!!
example:
Line 1 is state IL so it creates a report with all of the IL information in the query.
Line 2 is state MO so it creates a report with all of the MO information in the query.
Line 3 is state IL so it creates a report with all of the IL information in the query and over writes the current IL report.

Here is my code:
Const Folder = "C:\Test"
Const Domain = "Retail Sales - For Report - TEST"
'Domain can be table name, query name, or sql statement that provides the values to loop
Const LoopedField = "State"
Const ReportName = "Monthly Tax Report - TEST"

Dim rs As DAO.Recordset
Dim LoopedFieldValue As String
Dim FileName As String
Dim FullPath As String
Dim strWhere As String
Set rs = CurrentDb.OpenRecordset(Domain)

Do While Not rs.EOF
LoopedFieldValue = rs.Fields(LoopedField)
FileName = LoopedFieldValue & ".PDF"
'The field may be a text field. It then must be surrounded in singlequotes. If so uncomment below
'LoopedFieldValue = "'" & LoopedFieldValue & "'"
FullPath = Folder & FileName
strWhere = LoopedField & "=" & "'" & LoopedFieldValue & "'"

Debug.Print FullPath
Debug.Print strWhere
DoCmd.OpenReport ReportName, acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath
DoCmd.Close acReport, ReportName
rs.MoveNext
Loop
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:59
Joined
Aug 30, 2003
Messages
36,118
Try this:

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT State FROM " & Domain)
 

dvarvel

Registered User.
Local time
Today, 04:59
Joined
Nov 28, 2017
Messages
22
That didn't work. It bugged out immediately. I don't want to have to enter every state. Once I get this to work, I will implement it for multiple reports, and I can't take the time to enter every state for every report. Is there a way to set up a table with the state abbreviations and have the loop run through the table of abbreviations to select the state and then build the report using the query detail, based on the state from the table?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:59
Joined
Aug 30, 2003
Messages
36,118
Nobody is asking you to enter them manually. That should give you a single record for each state. What error did you get?
 

dvarvel

Registered User.
Local time
Today, 04:59
Joined
Nov 28, 2017
Messages
22
Sorry, pbaldy. I really appreciate your helping me with this. :)

The error message is" Run-time error '3131': Syntax error in FROM clause.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:59
Joined
Aug 30, 2003
Messages
36,118
Sorry, missed your name with inadvisable spaces and symbols. Try

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT State FROM [" & Domain & "]")
 

dvarvel

Registered User.
Local time
Today, 04:59
Joined
Nov 28, 2017
Messages
22
Now, it is asking me to Enter Parameter Value AK
 
Last edited:

Users who are viewing this thread

Top Bottom