OpenForm in VBA

I am trying to open a form in VBA, have the user double click on a row, get a value from that row and use it in an underlying form.  Problem is that even if I set the form properties to Modal = Yes and Popup = Yes, the program doesn't wait for the form to close, the code continues with the open form in the background.  I need for the form to be opened and the code to "wait" until one way or another that form is closed.  Any ideas?
DoCmd.OpenForm ("users")
            varfilter = "[USER] =" + "'" + VSelUser + "'"
 
VSelUser is set during the dblclick event in the "users" table.

Open in new window

MelGeeAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
<even if I set the form properties to Modal = Yes and Popup = Yes>
This is effectively making a Dialog Box.

However if you truly want to suspend everything outside of that form until it closes, you need to specify the acDialog option in the Open Form command (regardless of "Modal = Yes and Popup = Yes", which doesn't quite do the trick).

This WILL pause the execution of your code in the current procedure while the popup form is open:

       DoCmd.OpenForm ("users"),,,,,acDialog   '<---- use the acDialog option

The code execution will resume on the next line once the popup form closes.

0
 
Jeffrey CoachmanMIS LiasonCommented:
Double clicking on the row to open a related record is not the standard way of opening a related form.
(This is counter intuitive because a user must be "Told" that this will happen)
Hence the issues you are having.
Use a button instead.

Dim Sub YourButton_Click()
...
varfilter = "[USER] =" + "'" + VSelUser + "'"
docmd.OpenForm "YourForm",,,varfilter
end sub
0
 
milduraitCommented:
You could break your code up into two parts, and then fire the second part from the close event of the 2nd form.
0
 
MelGeeAuthor Commented:
You hit the nail on the head.  Thanks!
0
 
mbizupCommented:
Glad to help out :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.