MsgBox Assistance Needed

[beavetoots]

I have an Access DB with many queries. What I would like to do is display a
MsgBox with the name of the query that is running so that the user is aware
of the progress. I have all of the queries in a Macro. I do not want to
have the user have to click on any OK buttons or anything until after the
final query has run.

 

[Arvin Meyer MVP]

I have an Access DB with many queries. What I would like to do is display
a
MsgBox with the name of the query that is running so that the user is
aware
of the progress. I have all of the queries in a Macro. I do not want to
have the user have to click on any OK buttons or anything until after the
final query has run.

You cannot do that with a message box because it is modal (that means it
stops code from running until it is closed). If you are using code, it is
easier, but you may be able to use a macro to open your own form, with the
name of the query that's running. With code, you could do this with a single
form, but with macros. you'll need a form with each query's name. You will
also need a close macro to automatically close the form after however many
seconds you want to leave it open. Put that macro in each form's Close
event, and set the Timer Interval for 1000 * as many seconds you want it to
remain open.

 

[Dirk Goldgar]

I have an Access DB with many queries. What I would like to do is display
a
MsgBox with the name of the query that is running so that the user is
aware
of the progress. I have all of the queries in a Macro. I do not want to
have the user have to click on any OK buttons or anything until after the
final query has run.

You won't be able to do this with the built-in MsgBox function, because that
suspends macro or code execution until the user has clicked one of the
buttons on the MsgBox dialog. Instead, you can display your own form, with
a text box or label control on the form that you will set at run time to
display your message. You would open this form set its message before
running the first query. Then, before running each subsequent query, you
would set the message to a new value telling the user what's going on.
Finally, after you're done with all your processing, you would close the
form.

One thing to take care about is making sure that the form gets closed even
if there's an error that causes your macro to stop early. You don't want to
leave the form open afterward, no matter how the macro is terminated.

 

[John Spencer MVP]

You can't use the msgbox since it requires user interaction.

What you can use is a custom form you design and set a control on the form to
display the name of the current query. I don't know that you can do that from
a macro. I do this sort of thing using some VBA code.

I have a vba function that I use to open, update, and close the form.

Public Function fHandleProgressMeter(strMessage As String)
'*******************************************
'Name: fHandleProgressMeter (Function)
'Purpose: Loads and updates frmProgressMessage to show progress in loops
' Calling function must close form when finished.
'Author: John Spencer UMBC-CHPDM
'Date: April 28, 2000, 12:04:04 PM
'Inputs: strMessage - message to be displayed to user
' -- strMessage - "Close" will close the form
'*******************************************

If CurrentProject.AllForms("frmProgressMessage").IsLoaded = False Then
DoCmd.OpenForm "frmProgressMessage", acNormal
End If

If strMessage = "Close" Then
DoCmd.Close acForm, "frmProgressMessage"
Else
'Set the message and force the change to appear
Forms!frmProgressmessage!lblMESSAGE.Caption = strMessage
DoCmd.RepaintObject acForm, "FrmProgressMessage"
DoEvents
End If


End Function
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County