DoCmd.OpenReport Error (1 Viewer)

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:16
Joined
Aug 30, 2003
Messages
36,118
From that line, or later in the code?
 

dvarvel

Registered User.
Local time
Today, 07:16
Joined
Nov 28, 2017
Messages
22
It's a popup box. When I hit cancel, it brings up the code and it highlights: DoCmd.OpenReport ReportName, acViewPreview, , strWhere

Since my state abbreviations are text, should there be single quotes anywhere in the line you sent me?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:16
Joined
Aug 30, 2003
Messages
36,118
That means you're not building strWhere correctly. Do you still have the corrected code that surrounds the value with single quotes?
 

dvarvel

Registered User.
Local time
Today, 07:16
Joined
Nov 28, 2017
Messages
22
I updated my strWhere to read: strWhere = LoopedField & " = " & "' LoopedFieldValue &'"

The reports are being created, but they have no data in them. It is creating a blank report for each state.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:16
Joined
Aug 30, 2003
Messages
36,118
Close but no cigar. Try

strWhere = LoopedField & " = '" & LoopedFieldValue & "'"
 

dvarvel

Registered User.
Local time
Today, 07:16
Joined
Nov 28, 2017
Messages
22
PERFECT!! You are amazing!! Is there any way to make it go faster? lol The current report only has 975 rows... the next report will have over 50k. hahahahahah
 

dvarvel

Registered User.
Local time
Today, 07:16
Joined
Nov 28, 2017
Messages
22
Also, is there a way for it to let me know when the process is complete?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 07:16
Joined
Aug 30, 2003
Messages
36,118
You can put a message box at the end to alert the user when the process is complete.
 

dvarvel

Registered User.
Local time
Today, 07:16
Joined
Nov 28, 2017
Messages
22
I assigned the code to a click box. How do I add a message box to the code?
Nevermind... I figured it out. lol
Thanks again!!
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 14:16
Joined
Sep 21, 2011
Messages
14,042
PERFECT!! You are amazing!! Is there any way to make it go faster? lol The current report only has 975 rows... the next report will have over 50k. hahahahahah

Have you got an index on the State field?
 

dvarvel

Registered User.
Local time
Today, 07:16
Joined
Nov 28, 2017
Messages
22
Yes, they are indexed. :)
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 07:16
Joined
Sep 12, 2017
Messages
2,111
If you are worried about how long it takes, I'd put a control on the screen that gets updated with "Working on State " & [State] or something similar. That way your users know what its doing and don't complain that its just sitting there...

Your biggest time eater is probably creating the PDFs. I've seen that happen with reports before.
 

dvarvel

Registered User.
Local time
Today, 07:16
Joined
Nov 28, 2017
Messages
22
"I'd put a control on the screen that gets updated with "Working on State " & [State] or something similar. That way your users know what its doing and don't complain that its just sitting there..."

I don't know how to do this. Can you please help?
 

Mark_

Longboard on the internet
Local time
Today, 07:16
Joined
Sep 12, 2017
Messages
2,111
1) On your form, I would add a label called "Lbl_WorkingOn"
2) In your existing code, just after


LoopedFieldValue = rs.Fields(LoopedField)

add

Me.Lbl_WorkingOn.Caption = "Outputting report for " & LoopedFieldValue

3) After you are done with your loop I would add

Me.Lbl_WorkingOn.Caption = ""

This would display which report you export only while exporting. It would also give you an idea of how long each report takes to make.

If you really wanted to get tricky you can also put the time each starts up. That would let you, the programmer, know if something seems strange.
 

Users who are viewing this thread

Top Bottom