Close Form After User Input

[AliceW]

Hey, this was my question, too! But I have a further problem:
I want to open a form from code in a stand-alone module, run code that writes some values to the form, THEN have code execution wait while the user enters values on the form.

This is an audit trail program that runs before form update, detects changes and writes details to an audit trail table. For each change detected, I want to stop and make the user enter information about why the change was made. Then I want the audit trail program to resume. The values from the form will be written to the audit trail.

I tried opening the “ReasonforChange” form in dialog mode (thanks, Mile-O-Phile!), and it worked great, except then the program code stopped right away and didn’t send values to the form. (d'oh!)

Here is the gist of the code:

For Each C In tempForm.Controls
If Nz(C.OldValue) <> Nz(C.Value) Or (IsNull(C.OldValue) And Not IsNull(C.Value)) Or (IsNull(C.Value) And Not IsNull(C.OldValue)) Then

DoCmd.OpenForm "ReasonForChange", acNormal [NOT ,,,,acDialog]
Forms!ReasonForChange!Patient_ID = tempID
Forms!ReasonForChange!PageNum = tempPageNum
...etc....
Forms!ReasonForChange!Field = C.Name
Forms!ReasonForChange!OldValue = C.OldValue
Forms!ReasonForChange!NewValue = C.Value

[Here I want to go to “ReasonForChange” form, where operator enters a reason code number chosen from a combo box and may be required to enter further explanation depending on what code number is entered. (I want to go to this form so I can control and assist the choice of reason data, especially through use of the combo box with code #s and explanations.) “ReasonForChange” form has the VBA code to add a record to Audit Trail table. When operator closes “ReasonForChange,” and only then, I want to resume looping through the controls. Each time a change is detected, ReasonForChange form should open.]

End If
Next C

--------

There must be a way to do this?? Or can I accomplish what I'm trying to do without using the form? I want the reason codes and their interpretations to be taken from a table that is used as row source for the combo box.

 

[a.sinatra]

Does the form close before the information is passed to the new form?
________
Marijuana medical

 

[Mile-O]

I tried opening the “ReasonforChange” form in dialog mode and it worked great, except then the program code stopped right away and didn’t send values to the form.

When you open a form in Dialog mode, the new form takes precedence in the execution of code, and the remainder of code in the calling function/subroutine is only executed once the Dialog form is closed.

 

[Mile-O]

Why don't you put your code in the Dialog form's Unload event -that way it won't run until the form is unloading it's recordset and closing?

 

[AliceW]

Anthony—I’m not sure I understand the question, but here’s the flow: There is a data entry form open. On that form’s Before Update event, audit trail code in a free-standing module runs –that’s the code with the “For each C in controls” loop. The data entry form should remain open while the audit trail code runs, and while the “input reason for change” form opens and closes, perhaps repeatedly.

Mile-O-Phile: Yeah, I got that about why my code stopped running as soon as the dialog form opened, and felt silly. I guess I don’t understand your suggestion about running my code on the unload event of the dialog form. You mean move the code in the free-standing module into the dialog form? Well, the way I have it set up, the code in the module is a loop that opens the dialog form as often as it is needed—once for each change—and then after finishing the loop stamps a modified date in a field on the record that was modified. I don’t see how to change my concept to put all the code in the dialog form. Or maybe I don’t understand the suggestion.

Maybe, instead of trying to fill the values on the dialog form by running code from the module, I could write the values to global variables, then open the form and copy in the values. Does that sound safe? I’m trying to write generic code that can be used in all our databases, and some of the databases are shared by 2 or even 3 users without being split (we’ve found this works on our very small network), so that users share the same front end... would different users’ values of the same global variable remain distinct? (Seems to me we have done this in another context and the values did remain distinct.)

 

[AliceW]

My solution

I am passing the values from the code module to the dialog form as open args. Works!

(Quick recap: I needed to pause execution of code in a free-standing module while opening and closing a form that has values written to it by the code in the module. The crucial piece was opening the form in dialog mode so that code in the module would pause. THANK YOU Mile-O-Phile! --Alice)