Sonny G
asked on
How do I stop the current form from executing steps past the VBA code that calls another form until the called form is closed?
On the calling form (frmMF_Project), there is a button to fire-up another form (frmCF_ITN_Downloads) The calling form calls the called form and continues to process past the call. The problem is that the called form creates missing data for the calling form (frmMF_Project ) to process. But that data is not yet available because the calling form (frmMF_Project )code continues past the call command to call the form, frmCF_ITN_Downloads.
How do I stop the code at the opening of the called form until it is closed. I tried making the called form modal. I tried dialog and I tried modal and dialog together to no avail.
How do I stop the code at the opening of the called form until it is closed. I tried making the called form modal. I tried dialog and I tried modal and dialog together to no avail.
Private Sub btnMatrix_Click()
Call OnTheFlyInitiation
'***********************************
' Call the on-the-fly control file *
'***********************************
Call ITN_Matrix_CF_Data
strITN.SetFocus
strITN.Requery
Call strITN_AfterUpdate
DoCmd.GoToControl "CursorLandingSpot"
End Sub
Martin Liss
Can you show the code where you made it modal?
open the form in Dialog mode. That will halt execution in the calling code until the form is closed or hidden.
Jim.
normally if you open a form like that
doCmd.OpenForm FormName:=<Name of form to open>, WindowMode:=acDialog
https://docs.microsoft.com/en-us/office/vba/access/concepts/forms-design/suspend-code-execution-until-a-pop-up-form-is-closed
code execution will wait for the form to be closed...
doCmd.OpenForm FormName:=<Name of form to open>, WindowMode:=acDialog
https://docs.microsoft.com/en-us/office/vba/access/concepts/forms-design/suspend-code-execution-until-a-pop-up-form-is-closed
code execution will wait for the form to be closed...
ASKER
When I added the acDialog to the open statement, it did what I had asked. Thanks, Jim and HainKurt. I originally assigned a Border Style property to Dialog figuring that it did the same thing. I have a few forms that popup as acDialog, so i understood your solutions. Thank you.
Another "senior moment". <sigh>
Your solutions work fine but in my application, I have over 60 forms so far and they belong to three categories. In my application, each category form has its own ribbon. Your solution eliminates the ribbon.
Is there a way to adding code after the "open form" command that has the application wait until the form closes? Maybe a "do until" or some while (the form is open) statement?
Another "senior moment". <sigh>
Your solutions work fine but in my application, I have over 60 forms so far and they belong to three categories. In my application, each category form has its own ribbon. Your solution eliminates the ribbon.
Is there a way to adding code after the "open form" command that has the application wait until the form closes? Maybe a "do until" or some while (the form is open) statement?
ASKER
Hi Martin,
The modal designation was in the form properties. It did not owrk.
Lenny.
The modal designation was in the form properties. It did not owrk.
Lenny.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I would do it differently.
I would break apart the last half of your code and put it in a public method of the form.
Then when your pop-up form closes, call that code .
No wait loop is needed then.
Jim
ASKER
Thanks, Jim!
I really appreciate your insight.
Happy New Year!
Lenny
I really appreciate your insight.
Happy New Year!
Lenny
I would put
sleep 100
inside the loop
to make it run max 10 times/sec
your way, CPU may spin in loops too much, unnecessarily :)
for example, it may run that code 1000 times per sec!
if you put "sleep 100" it will be executed 10 times/sec
and you just lose 100ms before continue at worst...
sleep 100
inside the loop
to make it run max 10 times/sec
your way, CPU may spin in loops too much, unnecessarily :)
for example, it may run that code 1000 times per sec!
if you put "sleep 100" it will be executed 10 times/sec
and you just lose 100ms before continue at worst...
ASKER
My VBA did not recognize sleep. I had it in originally. I'll check why tomorrow.
I really appreciated your help!
I really appreciated your help!
you have to add a reference on top,
like
like
#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
#Else
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
#End IfASKER
That's great! Thanks!
I'll add the appropriate reference.
You've gone the extra mile!
I'll add the appropriate reference.
You've gone the extra mile!
Sleep is not going to work well in this case.
Sleep puts the whole MSACCESS.EXE process to sleep....nothing will work while the sleep is executed.
DoEvents() will work, but not very well. DoEvents clears Access's Windows queue of keystrokes, then gives up the remainder of the time slice. It does leave Access active though, so the popup will be functional, but it it will probably hesitate a lot. That's why this is a bad way to go with stalling loop like this.
Jim.
Sleep puts the whole MSACCESS.EXE process to sleep....nothing will work while the sleep is executed.
DoEvents() will work, but not very well. DoEvents clears Access's Windows queue of keystrokes, then gives up the remainder of the time slice. It does leave Access active though, so the popup will be functional, but it it will probably hesitate a lot. That's why this is a bad way to go with stalling loop like this.
Jim.
ASKER
Thanks, Jim! We are blessed to have someone like you who is so knowledgeable in Access.
There's a method to my madness.
The form that calls the Control File form is a subform that resides in three other main forms. The Control File feeds data to a combobox on that subform. If a choice is missing, misspelled or whatever, the user can add/change/delete the data in the Control File and upon closing the user is returned to that calling subform where further work is done in the afterupdate of that combobox control.
The application's Control File screens have special ribbons. If all Control files were dialogs, they'd lose their custom ribbons. The way that my applications have been programmed since Access 1.1 is that only one form/report is open at a time. The main menu form always remains open, but in the background because that has the ribbon that opens the entire application.
I hope that you understand why the pause is not that burdensome to the application.
BTW, did you win? I nominated you.
There's a method to my madness.
The form that calls the Control File form is a subform that resides in three other main forms. The Control File feeds data to a combobox on that subform. If a choice is missing, misspelled or whatever, the user can add/change/delete the data in the Control File and upon closing the user is returned to that calling subform where further work is done in the afterupdate of that combobox control.
The application's Control File screens have special ribbons. If all Control files were dialogs, they'd lose their custom ribbons. The way that my applications have been programmed since Access 1.1 is that only one form/report is open at a time. The main menu form always remains open, but in the background because that has the ribbon that opens the entire application.
I hope that you understand why the pause is not that burdensome to the application.
BTW, did you win? I nominated you.
<< If a choice is missing, misspelled or whatever, the user can add/change/delete the data in the Control File and upon closing the user is returned to that calling subform where further work is done in the afterupdate of that combobox control.>>
You would be better off to use the NoInList Event of the combo control.
<<I hope that you understand why the pause is not that burdensome to the application. >>
I think I understand the situation and with that said, let me say that it is hard to "pause" code in one place and still have the rest of the app active. Sleep will put the whole thing to sleep. Nothing will happen during that time.
As I said, you can use DoEvents() and it works to a point, but you are still effecting the entire app when you use it.
<<The way that my applications have been programmed since Access 1.1 is that only one form/report is open at a time. >>
Setting the modal and popup properties should take care of it for you, but you'd need to call a method in the calling form when you exit the popup.
Jim.
You would be better off to use the NoInList Event of the combo control.
<<I hope that you understand why the pause is not that burdensome to the application. >>
I think I understand the situation and with that said, let me say that it is hard to "pause" code in one place and still have the rest of the app active. Sleep will put the whole thing to sleep. Nothing will happen during that time.
As I said, you can use DoEvents() and it works to a point, but you are still effecting the entire app when you use it.
<<The way that my applications have been programmed since Access 1.1 is that only one form/report is open at a time. >>
Setting the modal and popup properties should take care of it for you, but you'd need to call a method in the calling form when you exit the popup.
Jim.
ASKER
Thanks, Jim!
Did you win?
Did you win?
Not sure what you are referring to...I would assume an award on EE. Winners are always the last to know<g>.
Thank you for the nomination.
Glad I've been able to help you out, as other people did for me way back when.
That's what EE is all about; everyone helping each other get through life a little easier.
Jim.
Thank you for the nomination.
Glad I've been able to help you out, as other people did for me way back when.
That's what EE is all about; everyone helping each other get through life a little easier.
Jim.
what about you move the rest of the code to form.close event...
some code here
open window modal
modal.close : the rest of the code runs here...
some code here
open window modal
modal.close : the rest of the code runs here...
ASKER
Your suggestion with the do events works well. I eliminated the sleep functions because it locked up everything. I had to use task manager to kill the application.
I agree with your suggestion regarding putting the code at the close, but it is not practical for my design. All of the control file screens utilize dozens of common functions to tightly manage what they are doing and generate message dialogs if they stray. Those functions also handle form and controls properties of current, open, activate, ondirty, afterupdate, onclick, onNotInList, OnGotFocus, mouse down, close, load and unload, etc. with parameters passed on the call to the particular control file to create the uniqueness of each control file screen based upon the control file needed at the call. This is a style that I had developed many years ago under CICS and VSAM - long before micros hit the marketplace
Currently, there are 35 control files and probably will reach close to 75 at the end of the project. By using the control file screen design, I have a template that is called to start making it unique. Creating a new control file screen generally take up to 15 minutes if the data table behind the screen consists of up to a dozen fields. In most cases, the control file has two fields: a hidden autonumbered index key and the data name. In that case the screen takes about 5 minutes to customize.
When a user is in a control file, they are in and out in less than a minute.
I agree with your suggestion regarding putting the code at the close, but it is not practical for my design. All of the control file screens utilize dozens of common functions to tightly manage what they are doing and generate message dialogs if they stray. Those functions also handle form and controls properties of current, open, activate, ondirty, afterupdate, onclick, onNotInList, OnGotFocus, mouse down, close, load and unload, etc. with parameters passed on the call to the particular control file to create the uniqueness of each control file screen based upon the control file needed at the call. This is a style that I had developed many years ago under CICS and VSAM - long before micros hit the marketplace
Currently, there are 35 control files and probably will reach close to 75 at the end of the project. By using the control file screen design, I have a template that is called to start making it unique. Creating a new control file screen generally take up to 15 minutes if the data table behind the screen consists of up to a dozen fields. In most cases, the control file has two fields: a hidden autonumbered index key and the data name. In that case the screen takes about 5 minutes to customize.
When a user is in a control file, they are in and out in less than a minute.