Basic & Simple, but I'm stuck!

  • Thread starter Thread starter Sara
  • Start date Start date
S

Sara

I have an Access 2000 db with loads of queries and
reports. I would like the user to be able to run a module
that will run the right query, and put 2 reports to the
screen in Print Preview.

I wrote the VBA module and I can get it to work if I click
on one of the two (identical) run buttons on the toolbar
in VBA editor. The problem is that I want to put the
module in a Group, and have the user click "Run" or double-
click the module to run it. But the "Run" button is
grayed-out.

I've been through help and web searches and read VBA for
Dummies (I guess I'm whatever is lower than a dummy!), and
I can't figure out how to get this to work. The module
works, but has to be run from the code window. And then
when both reports are on the screen, control goes back to
the Module window. I want the user to Run (double click)
a module from the "Group" in MS Access, and never see the
code, just see the 2 reports in print preview.

I'm so close, and can't figure out this last step.
(Module:
Option Compare Database
Option Explicit

' M_Front_End_Report
'
Private Function M__Front_End_Report()
On Error GoTo M__Front_End_Report_Err

Dim strMsg As String, strInput As String
' Initialize string.
strMsg = "Now enter parameters for the Over-Short
Quarterly Report "

' Suppress all messages
DoCmd.SetWarnings False

' Run Query to put data in the table; requries
Parameters for Year and Quarter
DoCmd.OpenQuery "MTQ_StoreOSas Pct of CoOS and
COSales", acNormal, acReadOnly


' Run Front End Report
DoCmd.OpenReport "R_Front End Report by Quarter",
acPreview, "", ""

' Run Qtrly Over/Short Report
' Tell user reason for entering parameters again
MsgBox strMsg
DoCmd.OpenReport "R_OverShort for Qtr by Week",
acPreview, "", ""


' Reset messages
DoCmd.SetWarnings True


M__Front_End_Report_Exit:
Exit Function

THANKS!!!
 
You need a method for calling the function.

If you have a command button, select the OnClick event,
and add =M__Front_End_Report()

Or, if you are using the Switchboard Manager, use the
RunCode section, then add M__Front_End_Report


Chris
 
This is crazy - I just am not getting this.
I looked up Switchboard, and tried to create one, and I
don't have that option in Add Ins.

So, I created a form (which I have never done and really
didn't want to have to do), and it's simple: Title and
one Command Button. I can't get the Module to run doing
all sorts of things:
I typed =M__Front_End_Report() on the OnClick line
I tried to build code, with RUN, CALL - nothing works.

Can you help?? I know this is awful, and I also have been
looking for a VBA class for months. I just can't find one -
any tips as to how to search, or what to look for would
be helpful, too.

Struggling,
Sara
 
In ACCESS 2000, you can look for the Switchboard Manager under Tools:Database
Utilities in the menus. MS moved it and it is no longer an Add-in.

You can't call a PRIVATE function from another module. So first, try changing
the Function to a Public function and make sure it is in a general module and
not in the form's module.
 
You can't call a PRIVATE function from another module.
So first, try changing
the Function to a Public function and make sure it is in a general module and
not in the form's module.
Click to expand...


I'm sorry, I never even noticed that the module was
PRIVATE.


PRIVATE modules mean that you can only call it from the
same module that it is in.

PUBLIC modules can be called from anywhere in the database.

Chris
 
Got it!! Amazing....

Now, I have a module that runs 4 reports, and each report
requires the parameter of "Sale Date". The purpose of the
module was so the user could click a button and all the
required reports would run.

Is there any way to have the user enter the Report
(underlying query) parameter once and have it apply to all
reports in the module?

I tried Dim SalesDate as Date.
Then I couldn't figure out how to have the module ask them
for the date and apply it to all reports. Can this be
done fairly easily, for a rookie programmer like me?

Thanks
 
Thanks - I knew that from my reading and studying, and
didn't realize Private didn't mean my whole database.
Anyway, I fixed it - learning as I go and appreciate your
help. I am now in the Switchboard mode - it's great! I
posted another question - my next level, but I won't
repeat it here in this "thank you" note.

Again, many thanks.
Sara
 
Is there any way to have the user enter the Report
(underlying query) parameter once and have it apply to all
reports in the module?
Click to expand...

I'd suggest creating a little form frmCrit (or, if you have a form
open already, using it) with an unbound textbox for the criterion;
change your query criterion to

=Forms!frmCrit!textboxname
 
Back
Top