Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-28-2017, 07:52 AM   #1
dvarvel
Newly Registered User
 
Join Date: Nov 2017
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
dvarvel is on a distinguished road
DoCmd.OpenReport Error

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.
Attached Files
File Type: docx VBA Error.docx (60.0 KB, 30 views)

dvarvel is offline   Reply With Quote
Old 11-28-2017, 07:54 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: DoCmd.OpenReport Error

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 11-28-2017 at 07:56 AM. Reason: Saw the debug.print
Gasman is offline   Reply With Quote
Old 11-28-2017, 07:54 AM   #3
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,370
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DoCmd.OpenReport Error

You need single quotes around the state value, since it's text.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-28-2017, 07:57 AM   #4
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: DoCmd.OpenReport Error

Quote:
Originally Posted by pbaldy View Post
You need single quotes around the state value, since it's text.
Oops, I *thought* that was part of the SQL statement?

Sorry dvarvel
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 11-28-2017, 07:59 AM   #5
dvarvel
Newly Registered User
 
Join Date: Nov 2017
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
dvarvel is on a distinguished road
Re: DoCmd.OpenReport Error

Thanks, Gasman, but I don't understand what you mean by "It should either have = or IN not both".
dvarvel is offline   Reply With Quote
Old 11-28-2017, 08:01 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,370
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DoCmd.OpenReport Error

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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-28-2017, 08:04 AM   #7
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,507
Thanks: 439
Thanked 838 Times in 809 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: DoCmd.OpenReport Error

Quote:
Originally Posted by dvarvel View Post
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.

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 11-28-2017, 08:06 AM   #8
dvarvel
Newly Registered User
 
Join Date: Nov 2017
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
dvarvel is on a distinguished road
Re: DoCmd.OpenReport Error

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.
dvarvel is offline   Reply With Quote
Old 11-28-2017, 08:06 AM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,370
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DoCmd.OpenReport Error

Based on what you're doing, I think you want to end up with:

State = 'IN'
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-28-2017, 08:25 AM   #10
dvarvel
Newly Registered User
 
Join Date: Nov 2017
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
dvarvel is on a distinguished road
Re: DoCmd.OpenReport Error

pbaldy - I changed: strWhere = LoopedField & " = " & LoopedFieldValue
TO:
strWhere = LoopedField & LoopedFieldValue


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.



Last edited by dvarvel; 11-28-2017 at 08:33 AM.
dvarvel is offline   Reply With Quote
Old 11-28-2017, 08:38 AM   #11
dvarvel
Newly Registered User
 
Join Date: Nov 2017
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
dvarvel is on a distinguished road
Re: DoCmd.OpenReport Error

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.
dvarvel is offline   Reply With Quote
Old 11-28-2017, 08:43 AM   #12
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,437
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: DoCmd.OpenReport Error

Post up the whole actual code you are using , press the # in the editor to put it into code tags to make it readable.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 11-28-2017, 08:44 AM   #13
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,370
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DoCmd.OpenReport Error

What is the code now? Are all the states in the "Domain" table/query?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-28-2017, 08:51 AM   #14
dvarvel
Newly Registered User
 
Join Date: Nov 2017
Posts: 22
Thanks: 9
Thanked 0 Times in 0 Posts
dvarvel is on a distinguished road
Re: DoCmd.OpenReport Error

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
dvarvel is offline   Reply With Quote
Old 11-28-2017, 08:53 AM   #15
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,370
Thanks: 13
Thanked 4,120 Times in 4,052 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: DoCmd.OpenReport Error

Try this:

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

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Tags
docmd.openreport , error , loop

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DoCmd.OpenReport OpenArgs error JGravesNBS Forms 3 11-17-2015 01:25 PM
DoCmd.OpenReport worked once, now error cjcobra Forms 3 01-26-2011 05:45 PM
Runtime error 2046 on doCmd.gotoRecord after doCmd.openReport Alexie Forms 3 06-03-2010 02:58 AM
Where Clause Error In The DoCmd.OpenReport cheer Forms 23 11-14-2009 08:06 AM
Access2002: Docmd.OpenReport Error mary.h Modules & VBA 6 10-08-2003 11:54 AM




All times are GMT -8. The time now is 03:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World