Macro runs only once from switchboard

  • Thread starter Thread starter Ernie Lippert
  • Start date Start date
E

Ernie Lippert

On the Switchboard, I have a button <CloseProject?>
This button runs <mac_qry_Admin_Project_Close> that has 2 actions
OpenQuery: a make-table query <mtqryAdminProjectClose> (Datasheet, Edit)
OpenForm: <frmA> (Form, normal)

Entries are made on <frmA> after which a button on the form is used to run
<macClose> that has 3 actions:
OpenQuery: an update query <udQryB> (Datasheet, Edit)
OpenQuery: an append query <appQryC> (Datasheet, Edit)
OpenForm: <Switchboard> (Form, normal)

This procedure works satisfactorily on the first pass. If <CloseProject?> is
activated a second time, the expected message
"The existing table......
appears. Yes is selected and the following message appears:
Action Failed
mac_qry_Admin_Project_Close
True
OpenQuery
mtqryAdminProjectClose, Datasheet, Edit
On Halt the following message appearsâ€
"There was an error executing the command."

This behavior is consistent from the Switchboard. If, however, I close the
Switchboard and run <mtqryAdminProjectClose>, it works properly.

If I go back and run from the Switchboard, I get the error.

How do I reset the proper variables so that macro will run repeatedly from
the Switchboard?
 
Have you tried this in code rather than macros?

if you do, try the following DoCmd methods

DoCmd.OpenForm
DoCmd.Close
DoCmd.OpenQuery

There's nothing that you can do in a macro that cant be done via code
(excepting the AutoExec macro, in which case a hidden startup form is
prudent), and the control and debugging of vba code is far far far far
superior than macros.

I don't mean to tell you how to design your project, but I think most
experienced programmers would agree to avoid macros, especially more complex
ones such as you have.

--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
 
I didn't go the VBA route this time but your advice is well taken.

On the problem form was a seldom used button <Exit Without Changes> which
calls Mac_Close_Project cosisting of two actions:
Close: <frm_Close_TblMain_Administrative>
OpenForm: Switchboard

It was found that <frm_Close_TblMain_Administrative> did not exist. This was
replaced by the correct <frm_Close_Projects>.

After this change, the entire procedure worked correctly. I guess I'm
surprised that a bad object name had such dire consequences even though the
button and its macro were not used.

Thanks for your advice.
 
Back
Top