verifying user wants to run macro

[Guest]

I have an itchy mouse finger and sometimes end up running the macro instead
of going to design view. The macro sends email to dozens of managers. To
avoid this error and having to recall the messages, is there a way I can make
the macro ask the user "are you sure you want to run this macro?"

 

[Steve Schapel]

Cornbean,

Yes, you can use a MsgBox() function in the Condition of your macro to
prompt for a confirmation. Something like this...
MsgBox("Proceed?",547)=7

I assume you have your macro assigned on some event on a form or some
such, which is how it is supposed to be run, right? However, it appears
you are running the macro from the Database Window, accidentally, when
trying to access design view of the macro. To me, this implies that you
are frequently accessing the macro design. Is this right? If so, it is
a very unusual thing... normally, once a macro is set up, you would very
seldom need to go back to design view. What are you doing?

 

[Steve Schapel]

Cornbean,

Sorry, that should be...
MsgBox("Proceed?",292)=6

 

[Guest]

Hi Steve,

Thanks for the response. Yes, I am frequently accessing the design view of
the macro. We have a table of all our employees and their vacation balances
which we update after each payroll. Then we have a query set up for each
manager to see their employee's vacation balances. We use the macro to email
each query out to all the different managers as an excel attachment. So when
managers change, the macro needs to be changed. Do you know of a better way
to update this kind of project?

 

[Steve Schapel]

Cornbean,

Can you provide full details of the Query and the Macro, with examples?
I don't have a clear idea of what you are doing at this stage.

 

[Guest]

Steve,

I wish I could send you the access file but it's 30 megs big. Hopefully I
can paint you a picture. Imagine a table with 10 employees listing their
name, vacation balance and their managers names. There are 5 managers with 2
employees each. So we set up a query labeled for each manager to filter out
just their employees' balances. Each week we send out an updated balance to
each manager but instead of running each query individually and mailing it as
an excel attachment, we created a macro to do that for us. The design of this
macro has a send object action. we tell the macro to take the object "query"
named "manager", use an output format of "excel", to: (e-mail address removed)

So i hope you can imagine, that as managers come and go, I have to change
the name of the query to match the new manager. Then I have to change the
macro to recognize the new query and the new email address.

 

[Steve Schapel]

Cornbean,

I would not do it like this myself. You can do it with just one query
and just one macro, and cycle through the managers one by one to send
the emails. Because this involves processing records one at a time, it
is rather awkward to do with a macro. But it is possible, by having a
continuous view form open at the time the macro runs, referring to the
manager and his email address on the form in the query criteria and the
macro, putting a GoToRecord/Next action in the macro, and then using
another macro with a RunMacro action to run the SendObject for each
manager. Post back if you want more specific details of this approach.
However, using a VBA procedure would be more elegant. Here is a rough
outline of what this might look like...

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim BaseSQL As String
Dim TrimSQL As String
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("YourEmailQuery")
BaseSQL = qdf.SQL
TrimSQL = Left(BaseSQL, Len(BaseSQL)-3)
SET rst = dbs.OpenRecordset("SELECT Manager, Email FROM
YourManagersTable")
With rst
Do Until .EOF
strSQL = TrimSQL & " WHERE Manager = '" & !Manager & "'"
qdf.SQL = strSQL
DoCmd.OutputTo ...
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

 

[Guest]

Steve, I'm trying your MsgBox as shown below. If the user selects No or
Cancel, I'd like it to stop the macro. If they click Yes, it continues. The
next step in my macro is to close the database. It's closing the database
regardless of which button I select. What am I missing? Thanks! Mary

 

[Steve Schapel]

Mary,

I guess you have a Quit action in the macro. You will need to specify
the condition under which you want this to take place. Or else by the
use of a StopMacro action judiciously placed.

Sorry, it's hard to be specific without more details... if you still
need more help with this, please post back with the
conditions/actions/arguments of the macro as it presently stands, and
explanation of what needs to change.

 

[Guest]

Yes, the macro runs when a user closes the database. It's a reminder to make
sure a macro has been run. Right now I have MsgBox("Proceed?",547)=7 as the
condition for the first row, no action. The second row has the Quit action.
I know I need to change something, but not exactly sure what. Thanks for
your help!

 

[Steve Schapel]

Mary,

Do you mean you want the application to close if the user answers Yes to
the MsgBox? If so, the Quit action needs o be on the same line in the
macro as the MsgBox Condition.

 

[Guest]

Only certain employees will need to run the extra macro, others should be
able to continue and Quit the application. I tried putting it on the same
line, but it Quit whether I clicked Yes or No. If the answer to "Do you want
to proceed?" is No, then I want it to stop so they can go back and run a
different macro before closing the db.

Better yet, if the question could be, "Do you want to refresh the
database?", then on Yes, run my other macro and then Quit -- or No, just Quit.

Thanks, Mary

 

[Steve Schapel]

Mary,

I really still have no idea at all what "run my other macro" means.
This is most mysterious.

But if we go back to square 1 here, and adapting the MsgBox function to
your question, just try a simple macro with one line only. In the
Condition column, put this...
MsgBox("Do you want to exit?",36)=6
.... and then put the Quit action.
Ok, run the macro. If you click Yes on the message box, the database
should close. If you click No on the message box, the database should
stay open.

 

[Guest]

Thanks, Steve. That works. My other macro just runs two queries. They need
to be run by the last person logging out of the db every day. I'm not a pro
so I can't automate that -- but the prompt will work. Whoever is working the
late shift that day should run those queries. The prompt is just a reminder.

 

[Steve Schapel]

Mary,

The queries can be run via the macro, using OpenQuery actions... you
don't need to be a "pro" for that.

 

[Guest]

I do have a macro for this. The "pro" part was for getting it to run only
for the last person to log off and not the others. I used the information
you gave me to add a message box, and it's working great. Some users left for
the day and got the message and clicked Yes to close the database. It works
great!

 

[Steve Schapel]

Mary,

I am pretty sure that if you put the OpenQuery actions in the same
macro, with nothing in the Condition, then for those that say 'Yes' to
the message box, and hence exit the database, the queries won't run...
they can't really, can they, after you quit.

Another approach is to make a little unbound form that opens via an
OpenForm macro instead. On this form you have 2 buttons, one for the
users who just quit and go home, and the other for the last logger-offer
to click to activate the queries.