Trying to DoCmd open a report in VB (1 Viewer)

[dazadd99]

I have a report that i need user input to decide whether report should be recompiled. The data source is the result of a make-table query and 9 other appends to collate the records in one table for analysis.
This needs to be done monthly, so I modified the help-sample script below to ask if the data should be re-compiled before the report is displayed.
The code is in a module and the function is called from the switch board, if refresh is needed, the macro runs the make / append queries, then the report accesses the newly made table.

The Problem is that the report runs as expected, but does not become the active window, when you click on the window on the task bar, the screen corrupts with merging of the two windows, icons disappear and it reminds me of the good old days of Windows 95 crashes. It invariably requires access to be closed.

Function collection_weight()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to Re-compile the Data?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "MsgBox Demonstration" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
DoCmd.RunMacro "Notional weight of Collected Items"
DoCmd.OpenReport "Electrical Collections Notional Weights of Collected Items", acViewPreview ' Perform some action.
Else
DoCmd.OpenReport "Electrical Collections Notional Weights of Collected Items", acViewPreview
' User chose No.
Exit Function ' Perform some action.
End If


End Function

I would appreciate some help on what is wrong????

Many thanks

daz

 

[Rich]

Macros have no inbuilt Error handling which is why the majority of programmers don't use them, use vba instead, you should also add an error handle event to your function

 

[dazadd99]

Thanks for the help, I have now done away with accessing the macro and I am running all queries within VBA. I can see that the queries are running as expected.
But the report is still being created and seeming to hang the application.
Any more ideas please?

daz

 

[dazadd99]

Progress - Switchboard wont run VBA code

I have found the cause of the hanging application.

DoCmd.Echo False, "Compiling Data......"
needs to be turned back to TRUE

DoCmd.Echo False, "Compiling Data......"

THis now allows the report to run.

But the strange thing is that I cannot get the switchboard manager to launch the function.
I have set it to "RUN CODE"
and put the function name in the box, I have tried Call <function>, Call <function ()> and both of those with out CALL command.

They launch successfully from a form button's On Click event by Calling <function name>

Any Ideas??

DAz