Macro runs only once from switchboard

[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?

 

[dymondjack]

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

 

[Ernie Lippert]

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.