I have a report that is query driven, when I run it, the macros stop working?

[Rebecca Ross]

I have built an Access DB, it uses a form to enter the data into the db. The form also has a macro for three dif types of documents/receipts to print depending on the data entered. All functions nicely, until we run a new report that I developed. The report is driven by a query, if I run this report, the macros stop functioning and I get the following error message:

Microsoft Visual Basic for Applications:
Compile Error:
Expected: Identifier

Any ideas?

Thanks so much...

[zmbd]

Basically, VBA is trying to tell you that one of the lines in the code has a variable not set as expected usually a function.

Are you writing in VBA or using the Macros... these are related but VERY different. MS made the mistake of refering to VBA as Macros in Excel and that creates some confusion.

Hopefully you're using VBA as you can open the VBA-Editor (<ALT-F11>) close all the open program windows... under the debug option in the ribbon select compile... this should then choke on the line in the code that is giving the error. From there post back the code, format it with the <CODE/> button, and indicate which line in the code caused the error.

Macros are a tad harder to troubleshoot and We'll need to know what version of MS Access you're using (infact, that information is always helpful)

[Rebecca Ross]

Thank you... I am using Access 2010

It's odd, the report is driven by this query below, but the Macros that stop functioning are NOT. If I do NOT run the report, I do not have an issue. It's almost like something/setting within the report takes control of the Macros after it runs... I hope that I explained the problem clearly enough... ;)

Option Compare Database
 
Private Sub Criminal_Complaint_Click(Is Not Null)
 
If Criminal_Complaint_Click = True
 
Or
 
If MDJ_Fingerprinting_Order_Click = True
 
Or
 
If Warrant_Service_Click = True
 
 
End Sub

----It stopped at the (Is Not Null), does that mean that I should remove that line of code?

[zmbd]

Sorry... this is going to be a Dentist visit:

First:
<alt-f11> to open the VBA editor.
menubar/tools/options
{editor} tab
- Uncheck "Auto Syntax Check" - your lines will still turn red however, you will nolonger receive the message prompt that you've goofed... I find it annoying to have to clear that box and then reselect the line to fix something I've mis-typed.
- Check "Require Variable Declaration" - this will now Force you to declare all of your variables before using them - Trust me, once you get the hang of it, you will apprecite the troubleshooting tool. It is sooooo easy to mistype a variable and then not be able to finde it when you're expecting one thing and your eyes fool you into thinking things are one way and not the other.

Now for the pain...
One by one... seriously just one at a time
Open all of your modules, class modules (if you don't know about these then you don't have them), form modules, reports...

Right below Option Compare Database insert the following: Option Explicit

Save everything.

Close all of the code windows again.
Go back to the Access interface (leave the VBA editor open)
and close all of the forms and reports that are open.

Go back to the VBA editor:
menubar/debug/compile
(the compile might be followed by "db1" or someother thing... however, the menu option should be first).
Lets see where the compiler chokes

:)

[zmbd]

Sorry,
Got called into the lab and someone pressed enter on the keypad (or that's my story) - maybe it was the screen saver. Been weird things happening on my work pc for the past few weeks.

Yes remove the Is Not Null from the Criminal_Complaint_Click... please do the reset of my post and then compile.

Also the remainder of your code is troublsome... on_click events are not usually boolean. Very weird. Subs do not return values so testing them for true/false is not done.

We'll need to see the rest of your code once you get thru clearing the errors that the compiler is going to bury you in.

Also

It's odd, the report is driven by this query below, but the Macros that stop functioning are

what you posted is not called a query... I'm not even sure if it passes for VBA-code but that is what it is trying to be....
A query is something like:

SELECT field1,
   field2,
   field3
FROM tbl_sometable
WHERE ((field1='somestring')
   AND (field2=#somedate#)
   AND (field3 = 0));

[Rebecca Ross]

Thanks so much! I followed your advice, Unchecked "Auto Syntax Check" and then made sure that I Checked "Require Variable Declaration" - I then removed that line of code "Is Not Null" and all works great now! Sometimes it's the simplest things that wreak such havoc! As I read the code, that command line didn't make sense...I should have saw that sooner!

[zmbd]

YEa.... I think!?
Well... you know where to come back to should it start acting up again... glad I could help - not exactly sure what I did though.
Best of Luck

[NeoPa]

That surprises me Rebecca. If your code is exactly as you have it displayed except for the "is not null" being removed then I still don't see how it can compile as it doesn't appear to be in the right format at all for VBA code.

I guess if everything is working now then you have less of an issue, but if you haven't set Option Explicit in every module and compiled the project then I can see you (or your project more likely) suffering badly from unexpected problems.

VBA can take code of many shapes and sizes but an "Or" alone on a line should always throw up a compilation error.