Show progress of large macro

H

Hank Rouse

I found this, Free chapter from the Access Cookbook: How to Create a Generic
Reusable
Status Meter
http://msdn.microsoft.com/library/d.../en-us/dnacbk02/html/ODC_CookbookChapter9.asp

However I am having a problem with it. My module looks like this:

What am I missing, or what it is I have that I shouldn't have. Rather new
to Macros, and did the upgrade Macro funtion.

Option Compare Database

'------------------------------------------------------------
' Pre_Scrub
'
'------------------------------------------------------------
Function Pre_Scrub()
On Error GoTo Pre_Scrub_Err

' Turn OFF Warnings
DoCmd.SetWarnings False
' Open Status Form
DoCmd.OpenForm "frmStatusMeter", acNormal, "Call
acbInitMeter(""Pre-Scrub Progress"", True) ", "", , acNormal
' A
DoCmd.OpenQuery "Scrub_A", acViewNormal, acEdit, fOK =
acbUpdateMeter(25)
' B
DoCmd.OpenQuery "Scrub_B", acViewNormal, acEdit, fOK =
acbUpdateMeter(50)
' C
DoCmd.OpenQuery "Scrub_C", acViewNormal, acEdit, fOK =
acbUpdateMeter(75)
' D
DoCmd.OpenQuery "Scrub_D", acViewNormal, acEdit, fOK =
acbUpdateMeter(100)
' Turn Warnings back ON
DoCmd.SetWarnings True
' Close Status Meter
DoCmd.Close acForm, acbcMeterForm
' Pre-Scrub has completed
Beep
MsgBox "Pre-Scrub has completed", vbOKOnly, "Pre_Scrub Status:"


Pre_Scrub_Exit:
Exit Function

Pre_Scrub_Err:
MsgBox Error$
Resume Pre_Scrub_Exit

End Function
 
D

Douglas J. Steele

The lines of code like

DoCmd.OpenQuery "Scrub_A", acViewNormal, acEdit, fOK = acbUpdateMeter(25)

need to be two separate lines:

fOK = acbUpdateMeter(25)
DoCmd.OpenQuery "Scrub_A", acViewNormal, acEdit


By the way, what you've posted isn't a macro: it's VBA code. Macros in
Access are very different.
 
H

Hank Rouse

Ok, I have now fixed this. So now I have a New Macro that runs the
following Code. Result: This action requires an Object Name argument

I thought the point was to get away from Macros. How else would I execute
the Module?

Option Compare Database

'------------------------------------------------------------
' Pre_Scrub
'
'------------------------------------------------------------
Function Pre_Scrub()
On Error GoTo Pre_Scrub_Err

' Turn OFF Warnings
DoCmd.SetWarnings False
' Open Status Form
DoCmd.OpenForm "frmStatusMeter", acNormal, "Call
acbInitMeter(""Pre-Scrub Progress"", True) ", "", , acNormal
' A
fOK = acbUpdateMeter(25)
DoCmd.OpenQuery "Scrub_A", acViewNormal, acEdit
' B
fOK = acbUpdateMeter(50)
DoCmd.OpenQuery "Scrub_B", acViewNormal, acEdit
' C
fOK = acbUpdateMeter(75)
DoCmd.OpenQuery "Scrub_C", acViewNormal, acEdit
' D
fOK = acbUpdateMeter(100)
DoCmd.OpenQuery "Scrub_D", acViewNormal, acEdit
' Turn Warnings back ON
DoCmd.SetWarnings True
' Close Status Meter
DoCmd.Close acForm, acbcMeterForm
' Pre-Scrub has completed
Beep
MsgBox "Pre-Scrub has completed", vbOKOnly, "Pre_Scrub Status:"


Pre_Scrub_Exit:
Exit Function

Pre_Scrub_Err:
MsgBox Error$
Resume Pre_Scrub_Exit

End Function
 
D

Douglas J. Steele

You can create a procedure associated with an event on your form (such as
the Click event for a command button), and call the function there.
 
G

George Nicholson

DoCmd.OpenForm "frmStatusMeter", acNormal, "Call
acbInitMeter(""Pre-Scrub Progress"", True) ", "", , acNormal
Click to expand...

1) the third argument for OpenForm is Filter "...the name of a valid query
in the current database...". "Call acbInitMeter..." is not a query name and
is why you are getting an "Object Name" error.

2) the 2nd acNormal should be acWindowNormal or omitted entirely, since
that's the default.

3) Reading the article you site, I don't think you need to use OpenForm
anyway. Assuming you've followed the instructions, you already have a
function that will open the form. You just need to call it. Replace the
"DoCmd.OpenForm" line with:
Call acbInitMeter(""Pre-Scrub Progress"", True)

4) Similarly, replace the "DoCmd.Close acForm, acbcMeterForm" line with:
Call acbCloseMeter

HTH,
 
H

Hank Rouse

Thanks Guys, everything is working GREAT after Douglas pointed me in the
right direction, and I made the necessary changes.

Have since used the same steps, and created a second procedure that runs a
series of queries successfully.

Thanks for all the assistance.

Hank
 
G

Guest

All has been working quitre well now. However I'd like to add something to
it.

What is the syntax I would add to the end of this stored procedure below to
update the TBL and COL "Actions.Button1LastRun" with the Time and Date Stamp
of the procedure completing?

' Turn Warnings back ON
DoCmd.SetWarnings True
' Close Status Meter
Call acbCloseMeter

AddressConversionScrub_Exit:
Exit Function

AddressConversionScrub_Err:
MsgBox Error$
Resume AddressConversionScrub_Exit

End Function

Thanks,
Hank
 
S

Steve Schapel

Hank,

If I understand your meaning here, I think this will do it...

CurrentDb.Execute "UPDATE Actions SET Button1LastRun = Now()",
dbFailOnError

By the way, as an aside, this is not a "Stored procedure".
 
Top