How to pause macro code until non-modal form is closed

Question

I'm trying to achieve this in Access 2016 purely using macro code, without vba.

I have a form frmMaintenance that lists jobs. New jobs are added to the jobs table tblJob via the form frmAddJob.

frmAddJob is displayed, when a button is clicked on frmMaintenance, by an OpenForm macro. Once frmAddJob is open, I want to set a value of one of its controls, indicating job-type.

Then, after frmAddJob is closed, I want to requery the control on frmMaintenance that lists the jobs.

The macro code in my btnAddJob OnClick is, in outline, like this:

• OpenForm //open frmAddJob
• SetValue //of control on frmAddJob
• Requery //the control in frmMaintenance

The problem is that if frmAddJob is opened non-modally, 'Window Mode'=Normal, the Requery is executed straight away. On the other hand, if frmAddJob is opened with 'Window Mode'=Dialog, then the macro code pauses until the window is closed, and I never get to set the required value.

The 2 solutions that occur to me are:

Set frmAddJob somehow to modal, after SetValue. However, using OpenForm 'Window Mode'=Dialog on an already open form doesn't seem to work.

Have frmAddJob get the value it needs from frmMaintenance in its 'On Open' event. However, this makes frmAddJob dependent on frmMaintenance and thus non reusable.

Is this possible just using maros?

Answer 1

From suggestion of @June7, solution in outline is:

In frmMaintenance:

• SetTempVar tvJobType
• OpenForm frmAddJob modally
• RemoveTempVar tvJobType
• Requery

In frmAddJob:

• in OnLoad, SetValue of required item to [TempVars]![tvJobType]
• save the new record
• close

Works fine and the requirement for frmAddJob to have any knowledge of from where it was opened is gone.