Microsoft Access cannot find the object

[vvwstcat]

I am a casual user, have only limited knowledge in the tools. I created a MS Access db module called GenStockReports. What it does is to run 2 queries and output the results to excel workbooks.
I did not write the code which was actually generated thru a few clicks. See the code below:


Public Sub GenStockReports() ' I replaced Sub by Function, the same error message.
Dim Outfile As String


On Error GoTo GenStockReports_Err


Outfile = Application.CurrentProject.Path & "" & "DailyStockReport US.xls"
DoCmd.OutputTo acOutputQuery, "QryStockReport", "Excel97-Excel2003Workbook(*.xls)", Outfile, False, "", , acExportQualityPrint



Outfile = Application.CurrentProject.Path & "" & "DailyOptionReport US.xls"
DoCmd.OutputTo acOutputQuery, "QryOptionReport", "Excel97-Excel2003Workbook(*.xls)", Outfile, False, "", , acExportQualityPrint




GenStockReports_Exit:
Exit Sub


GenStockReports_Err:
MsgBox Error$
Resume GenStockReports_Exit


End Sub

----------------------------------------

I can run it under the design view.
I can also run it under the tools | macros | run button (GenStockReports appears under the Macro name inbox)

However, if I create a macro in the main Access menu that runs the module, I then get an error messages “Microsoft Access cannot find the object “GenStockReports”
Can somebody help me to run the module under the create macro main page?



the macro list.



Many thanks.

[Welshgasman]

You would have to show us a picture of the macro list?

OK, from your description and my 2007,that runs CODE which in Excel terms is called a macro, but is just VBA code.?
A macro in Access is something completely different?

If your code is a Function, you can use the RunCode macro command.

[mike60smart]

Hi

See this https://stackoverflow.com/questions/...-access-module

[Welshgasman]

I have never used that option and can see that it emulates Excel, but still only writes VBA code into a module.? Very confusing.

[vvwstcat]

Thanks. I have done a bit of readings. It seems that Access modules have to be associated with forms or reports before they can be referenced. Mine one is a general module, not assoicated with any object. it could be the reason Access cannot find it. Anyway, it is very disappointed to see such limitation. I meant to do automation. if theere is no alternative, I then continue to handle it mannually.

[Welshgasman]

Thanks. I have done a bit of readings. It seems that Access modules have to be associated with forms or reports before they can be referenced. Mine one is a general module, not assoicated with any object. it could be the reason Access cannot find it. Anyway, it is very disappointed to see such limitation. I meant to do automation. if theere is no alternative, I then continue to handle it mannually.

No, it is perfectly normal to have vba code in modules, very common in fact.
Now you have posted what you have, that confirms what I suspected and said?

If you make that sub a function, then you use the actual Macro RunCode command to run that NOT RunMacro.?

You could just have a simple form with one button to run that as it is as well?

[davegri]

Thanks. I have done a bit of readings. It seems that Access modules have to be associated with forms or reports before they can be referenced. Mine one is a general module, not assoicated with any object. it could be the reason Access cannot find it. Anyway, it is very disappointed to see such limitation. I meant to do automation. if theere is no alternative, I then continue to handle it mannually.

A function or sub within a code module cannot have the same name as the code module.



The above macro Runcode argument must be Function Name =GenStockReports()
The = is required

Then make sure the code procedure in that renamed module actually is a Function:

Public Function GenStockReports() 
    Dim Outfile As String
    On Error GoTo GenStockReports_Err
    Outfile = Application.CurrentProject.Path & "" & "DailyStockReport US.xls"
    DoCmd.OutputTo acOutputQuery, "QryStockReport", "Excel97-Excel2003Workbook(*.xls)", Outfile, False, "", , acExportQualityPrint
    Outfile = Application.CurrentProject.Path & "" & "DailyOptionReport US.xls"
    DoCmd.OutputTo acOutputQuery, "QryOptionReport", "Excel97-Excel2003Workbook(*.xls)", Outfile, False, "", , acExportQualityPrint
GenStockReports_Exit:
    Exit Function
GenStockReports_Err:
    MsgBox Error$
    Resume GenStockReports_Exit
End Function

[Gicu]

You have the path wrong (missing slash):

Outfile = Application.CurrentProject.Path & "\" & "DailyStockReport US.xls"

Cheers,

[vvwstcat]

@davegri, thanks a lot for your advice. It works after taking your words. Thanks a lot for pointing out the subtle difference, your advice saves me hours of headless trial and error. Appreciate your help very much.

[vvwstcat]

You have the path wrong (missing slash):

Outfile = Application.CurrentProject.Path & "\" & "DailyStockReport US.xls"

Cheers,

thanks Gicu for pointing out. it is a slash in my code, but dont know why it got changed when pasted there.

[davegri]

@davegri, thanks a lot for your advice. It works after taking your words. Thanks a lot for pointing out the subtle difference, your advice saves me hours of headless trial and error. Appreciate your help very much.

You're quite welcome. Glad to help and see that the project is progressing.

[ssanfu]

I am a casual user, have only limited knowledge in the tools. I created a MS Access db module called GenStockReports. What it does is to run 2 queries and output the results to excel workbooks.
I did not write the code which was actually generated thru a few clicks. See the code below:


Public Sub GenStockReports() ' I replaced Sub by Function, the same error message.
Dim Outfile As String

You should NOT have a procedure the same name as the Module name. Remember, the module name is a Container that holds the VBA code. And you do not "Run" modules, you Run the procedures inside the module.

I use a 3 letter prefix for my object names:
tbl for tables (tblEmployees)
frm for forms (frmEmployees)
qry for queries (qryEmployeeList)
rpt for reports (rptActiveEmployeeList)
and mod for standard modules (modStringRoutines)


You should get in the habit of using Debug.Print statements in code to check that values are what you expect.

Dim Outfile As String

On Error GoTo GenStockReports_Err

Outfile = Application.CurrentProject.Path & "" & "DailyStockReport US.xls"
Debug.Print Outfile

DoCmd.OutputTo acOutputQuery, "QryStockReport", "Excel97-Excel2003Workbook(*.xls)", Outfile, False, "", , acExportQualityPrint

Set a breakpoint on the debug statement to see what the value is ....




Instead of

Outfile = Application.CurrentProject.Path & "\" & "DailyStockReport US.xls"

I would use

Outfile = Application.CurrentProject.Path & "\DailyStockReport US.xls"

No need to concatenate the slash (in this case)