OpenForm in VBA

Posted on 2009-04-02
Medium Priority
Last Modified: 2013-11-28
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

Question by:MelGee
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24055608
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
LVL 11

Expert Comment

ID: 24056145
You could break your code up into two parts, and then fire the second part from the close event of the 2nd form.
LVL 61

Accepted Solution

mbizup earned 200 total points
ID: 24060053
<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.


Author Closing Comment

ID: 31566033
You hit the nail on the head.  Thanks!
LVL 61

Expert Comment

ID: 24062120
Glad to help out :-)

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

Join & Write a Comment

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question