Running a report from a VB script

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have created a batch file following the instructions from a post by Albert
Kallal to run a routine that prints a report. Here is my script:

dim accessApp
set accessApp = createObject("Access.Application")
accessApp.OpenCurrentDataBase("C:\Documents and Settings\Owner\My
Documents\TimeandBilling.mdb")

accessApp.Run "macinvoice"
accessApp.Quit
set accessApp = nothing

The module in the mdb file is called macinvoice and this is the code:
Option Compare Database

'------------------------------------------------------------
' macinvoice
'
'------------------------------------------------------------
Function macinvoice()
On Error GoTo macinvoice_Err

DoCmd.OpenReport "Invoice List", acViewPreview, "", "", acNormal


macinvoice_Exit:
Exit Function

macinvoice_Err:
MsgBox Error$
Resume macinvoice_Exit

End Function


However when I click on the script icon Access opens at the right database
but I get an error message that says it can't find the procedure 'macinvoice'
even though the module is listed as macinvoice.

I must admit to being a little out of my depth here so can anyone help?
Thanks
Tony
 
You should NOT save a module with same name as a procedure. That will lead to
an error message when you attempt to use the procedure (function) since the
names of modules and VBA procedures use the same "name space".

Try changing the name of the module to modMacInvoice and see if that fixes
your problem.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Did you change the name in the script? You should not do that.
You change the name of the VBA code module.

SO in the AccessApp (TimeAndBilling) you would have a module named
ModMacInvoice and in that module you would have a FUNCTION named MacInvoice.
If that does not fix the problem, then I hope Albert Kallal happens onto this
thread.

Then the script would use MacInvoice.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks John. I changed the name as you suggested but still get a message that
says Access can't find the procedure "Modmacinvoice" with error code 800A09D5


Access opens when I run the script but it appeasr not to be able to find the
procedure that runs the report.

Thanks again
Tony

John said:
You should NOT save a module with same name as a procedure. That will lead to
an error message when you attempt to use the procedure (function) since the
names of modules and VBA procedures use the same "name space".

Try changing the name of the module to modMacInvoice and see if that fixes
your problem.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I have created a batch file following the instructions from a post by Albert
Kallal to run a routine that prints a report. Here is my script:
Click to expand...
[quoted text clipped - 36 lines]
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
I really don't have anything further. I might try to actually PRINT the
report instead of attempting to preview it. However in either case, you need
to correct the code.

'Note that your report name has a space in it. That means you are going to
have to bracket the name in order to use it or change the report name to get
rid of the spaces.

DoCmd.OpenReport "[Invoice List]",acViewNormal

or perhaps the following will work with the corrections to the arguments.

DoCmd.OpenReport "[Invoice List]",acViewPreview,,,acWindowNormal

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi John I now have a moduke called ModmacInvoice and a Function called
macinvoice. When I run the script Access opens briefly on the screen then
disappears. So I've got rid of the error message but nothing is happening now.

Any more ideas? No comment from Albert but I hope he picks this up as it's
his script I'm using.
Cheers
tony

John said:
Did you change the name in the script? You should not do that.
You change the name of the VBA code module.

SO in the AccessApp (TimeAndBilling) you would have a module named
ModMacInvoice and in that module you would have a FUNCTION named MacInvoice.
If that does not fix the problem, then I hope Albert Kallal happens onto this
thread.

Then the script would use MacInvoice.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks John. I changed the name as you suggested but still get a message that
says Access can't find the procedure "Modmacinvoice" with error code 800A09D5
Click to expand...
[quoted text clipped - 22 lines]
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
Sorry. I get so used to bracketing anything that does not follow my naming
convention that I got carried away. You are correct that the brackets should
NOT be there in this case.

I am out of ideas at this point. And I don't have time to attempt testing.

You might try reposting your problem. By now, the thread has gotten long
enough that many people that might help are no longer watching the thread.

I hope you solve this one. I am looking forward to seeing a solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John I've put brackets around the report name and this time I get an error
message that says can't find the report [Invoice List] I've also changed the
name to InvoiceList and when I run the script Access opens fleetingly then
closes.

Really frustrating!
Thanks for your suggestions though.
Tony

John said:
I really don't have anything further. I might try to actually PRINT the
report instead of attempting to preview it. However in either case, you need
to correct the code.

'Note that your report name has a space in it. That means you are going to
have to bracket the name in order to use it or change the report name to get
rid of the spaces.

DoCmd.OpenReport "[Invoice List]",acViewNormal

or perhaps the following will work with the corrections to the arguments.

DoCmd.OpenReport "[Invoice List]",acViewPreview,,,acWindowNormal

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi John I now have a moduke called ModmacInvoice and a Function called
macinvoice. When I run the script Access opens briefly on the screen then
Click to expand...
[quoted text clipped - 25 lines]
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
Yes reposting is ok in this case. You have made some progress.

If it makes you feel more comfortable, you can explain that you are reposting
the problem and why. Just make sure you post where you now are and the code
you are now using.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Thanks John for your help anyway. Is reposting OK? I know someforums don't
like it.
Tony

John said:
Sorry. I get so used to bracketing anything that does not follow my naming
convention that I got carried away. You are correct that the brackets should
NOT be there in this case.

I am out of ideas at this point. And I don't have time to attempt testing.

You might try reposting your problem. By now, the thread has gotten long
enough that many people that might help are no longer watching the thread.

I hope you solve this one. I am looking forward to seeing a solution.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
John I've put brackets around the report name and this time I get an error
message that says can't find the report [Invoice List] I've also changed the
Click to expand...
[quoted text clipped - 29 lines]
Thanks
Tony
Click to expand...
Click to expand...
Click to expand...
 
Back
Top