DoCmd.OpenReport Error

[pbaldy]

From that line, or later in the code?

 

[dvarvel]

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]

That means you're not building strWhere correctly. Do you still have the corrected code that surrounds the value with single quotes?

 

[dvarvel]

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]

Close but no cigar. Try

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

 

[dvarvel]

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]

Also, is there a way for it to let me know when the process is complete?

 

[pbaldy]

You can put a message box at the end to alert the user when the process is complete.

 

[dvarvel]

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!!

 

[pbaldy]

Happy to help!

 

[Gasman]

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]

Yes, they are indexed.

 

[Mark_]

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]

"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_]

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.