Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access VBA - Exiting SUB from another form

Posted on 2008-06-13
3
Medium Priority
?
1,166 Views
Last Modified: 2008-07-07
Hi,

I have a form which loaded from a module. When I press the cancle button on my form is there anyway to exit the sub in which the code is currently running?

Thanks
0
Comment
Question by:andyb7901
3 Comments
 
LVL 16

Expert Comment

by:Robin
ID: 21776852
When you press the cancel button on your form, and execute

DoCmd.Close

the form will close, and you will 'return' to the sub.

Is that what you meant?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 21776914
If you have a looping procedure which takes a long time and you want to be able to stop it, you will need to have it test a variable each time that it goes through the loop and to have DoEvents in the loop to allow the variable to be updated.
Your Cancel button would change the value of the variable. The (VB6) snippet illustrates


Option Explicit
Dim bStop As Boolean
 
Private Sub Command1_Click()
Do
    Text1.Text = (Val(Text1.Text) + 1) Mod 30000
    DoEvents
    If bStop Then
        bStop = False
        Exit Sub
    End If
Loop
End Sub
 
Private Sub Command2_Click()
    bStop = True
End Sub

Open in new window

0
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 2000 total points
ID: 21896292
Normally if you open a form from a procedure in a module the code in that procedure will continue to execute until it is done.  If, however, the form is opened as a dialog form, or is modal then the calling procedure's code halts until the form is...

1) Closed
   or
2) Hidden

Given two ways to resume execution of the code in your calling procedure you have a way to exit the procedure under one of the two scenarios.  

Below are two procedures to illustrate how this would work.  The first one would be in some global module.  The second procedure would be behind the OnClick Event of a command button named cmdCloseHide.

How it works...

The Procedure SampleSub opens the Form named "frmTest" as a dialog form.  Thus all code within that procedure halts until "frmTest" is closed or hidden.

If the user clicks the cmdCloseHide button the OnClick event hides the form.  From the users perspective they will think it was closed.  At this point control goes back to SampleSub where you'll find the first line of code checks to see if "frmTest" is loaded.  If it is then it closes the form and exits the procedure.

If the form was closed, however, then code simply continues beyond the End If statement you see here.

In fact you could reverse the logic if you prefer, meaning you could exit the sub routine if the form was not loaded else continue the execution of code within the procedure if the form was merely hidden.  Just remember that if you hide the form that the very next thing you'll usually do after you've confirmed that that is the case is to close the form unless you have some reason for leaving it loaded and hidden.  

In short, you have the option to exit the calling procedure as soon as the form is hidden or closed (your choice) but remember that if the form is not opened as a dialog form that the code does not halt and thus it will most likely finish executing everything there long before you hide or close the form in the first place.

Rick


If the user closes the form then the code from SampleSub resumes

Sub SampleSub()
    DoCmd.OpenForm "frmTest", , , , , acDialog
    If CurrentProject.AllForms("frmTest").IsLoaded Then
        DoCmd.Close acForm, "frmTest"
        Exit Sub
    End If
    Debug.Print "Code continues after form has closed"
End Sub
 
 
Private Sub cmdCloseHide_Click()
    'This command doesn't actually close the form, it hides it.
    'If the form was called from another procedure as a dialog form (modal) then
    'Code from the calling procedure would halt until this form was closed or hidden.
    'By hiding it instead of closing it we give the calling procedure the option to decide
    'how to proceed.
    Me.Visible = False
End Sub

Open in new window

0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

783 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