How to pause code when calling custom message box form?

[buratti]

I have created a custom message box form that suits my needs for this particular situation. It is called (opened) in the middle of a bunch of other VBA procedures. What I am having a problem with is how do I "pause" the rest of the code from running after the message box call?

For example, with a regular msgbox() function, once called, the code will wait for the user input or click of a button, then continue. My problem is that I am using the basic docmd.openform procedure, and it opens the message box form just fine, but then continues on with the rest of the code after the form is opened.

How would I "pause" the code after the call of opening my custom message box form, then continue it after the user clicks one of my closing buttons from that form?

Furthermore, although its not entirely needed in this situation, for future reference, how would I have that message box form return a value to the calling procedure?

 

[nanscombe]

When opening your form using Docmd.OpenForm you would set the WindowMode to acDialog, this will stop any further execution of the code until the form being opened is closed again.

docmd.OpenForm "yourForm",,,,,acDialog

 

[buratti]

Thanks didn't know that.

Now any suggestions on how to have that form return a value to the calling procedure?

for now I just plan on using something in the message box form like (forgive syntax, haven't actually written it yet):
"Forms!"calling form"!ControlName.Value = "value returning to calling form"

but is there another way to have my messagebox form return a value as if I'm calling a function that returns a value?

 

[bob fitz]

Perhaps you could declare a global variable in a general module and assign a value to the variable when the form is closed which could then be referenced by the code that opened the dialog form.

 

[Mile-O]

Or have a hidden control on the calling form that, when the called form is closed, an event assigns a value to the hidden control.

 

[nanscombe]

You could pass any values back using TempVar(s) as in the attached example.

I have added some appropriate code to the button form frmStart's command button called cmdClickMe.

Private Sub cmdClickMe_Click()
  [COLOR="Red"][B]DoCmd.OpenForm "msgCustom", , , , , acDialog[/B][/COLOR] ' Waits here until msgCustom closes
   
  Select Case TempVars("myMessageBox")
  Case vbYes
      
      MsgBox "You clicked the 'Yes' button.", vbInformation + vbOKOnly
  
  Case vbNo
      MsgBox "You clicked the 'No' button.", vbInformation + vbOKOnly
  
  Case vbCancel
      MsgBox "You clicked the 'Cancel' button.", vbInformation + vbOKOnly
  
  Case Else
      
      MsgBox "The value returned was '" & TempVars("myMessageBox") & "'", vbInformation + vbOKOnly
  
  End Select

End Sub

In the custom MessageBox form, msgCustom, there are three buttons with code; "Yes", "No" and "Cancel".

Private Sub cmdButton01_Click()
  TempVars("myMessageBox") = vbYes
  DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Private Sub cmdButton02_Click()
  TempVars("myMessageBox") = vbNo
  DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Private Sub cmdButton03_Click()
  TempVars("myMessageBox") = vbCancel
  DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

 

[buratti]

Thanks Bob Fitz and Mile-O... Those were 2 ideas I thought up after posting this.

nanscombe - I never worked with tempvars before. Will try it out and see how it works for this situation.

 

[jdraw]

You could also research InputBox.
Here's a link
http://www.functionx.com/vbaccess/topics/inputbox.htm

 

[nanscombe]

...
nanscombe - I never worked with tempvars before. Will try it out and see how it works for this situation.

I used to use a global variable but now that TempVars have been introduced I would be inclined to use one of them instead.

 

[Galaxiom]

Most experienced developers prefer to keep Global variables for application wide information. I rarely use them.

If the only object that would care about the returned value is the calling form then add a Public function to the calling form. This becomes a Method of the form and can accept the value passed to it.

This structure also allows for different calling forms to each get an independent value from the same popup form. In this circumstance you can tell the calling form how it was opened and where to run the method with the returned value by passing an OpenArg.

Adding the method to the form effectively encapsulates the code making it independent of extraneous external variables and hence more portable to other applications. It is a more robust programming practice.

 

[ChrisO]

You probably already have more methods than you need but there is another one.

Code in the calling Form suspends if the called Form is opened in acDialog mode.
The code stays suspended while the called Form is open and visible.

The called Form can set itself to invisible and the code in the calling Form will resume. The code in the calling Form can then access public variables in the called Form and then close the called Form.

So the variable(s) used are not truly global/public in the normal sense and therefore offer some protection from some of the normal things people complain about with global/public variables.

Chris.

 

[Galaxiom]

You probably already have more methods than you need but there is another one.

Never. Always worth knowing another, especially this one.

Code in the calling Form suspends if the called Form is opened in acDialog mode.
The code stays suspended while the called Form is open and visible.

I didn't know that visibility affected the execution of a popup form.

So it would be possible to load the popup form with Visible = False, let a custom property with the calling form object then make it visible.

This has an advantage over using OpenArgs which can only pass a string.

The string is fine to identify the calling form if there is the only one instance of the caller but passing an object allows multiple instances of the same form to be uniquely identified in the best way possible.

I love this place. I sometimes answer questions like this as much to see what other techniques are used by developers as offering my own knowledge. This time I struck gold for my efforts.

Thanks

 

[ChrisO]

>>This has an advantage over using OpenArgs which can only pass a string.<<
That’s another thing we can blame the Microsoft documentation for.

OpenArgs is a Variant which means it can be a String or a Long which means it can be a numerical value such as a pointer which means it can be an Object (pointer to Object) which means we can pass Objects through OpenArgs.

Naturally enough, there are some people who have tried to tear down the idea but that’s just because they didn’t think of it for themselves. One of those people does not come to this site anymore. (At least not under their original name.)

On my SkyDrive site, in my signature, there are a number of Access2003 demos showing how to pass Objects through OpenArgs. Look for any demo with OpenArgs in its title.

There is also a write up of it here:-
http://www.access-programmers.co.uk/forums/showthread.php?t=225415


Chris.

 

[Galaxiom]

There is also a write up of it here:-
http://www.access-programmers.co.uk/forums/showthread.php?t=225415

Gold with diamond studs.

 

[nanscombe]

An example based on the method of hiding, then closing, the form rather than just closing it.

The example allows for the passing of two values, an Integer (the status) and a Variant (a value).

I have wrapped the functionality into a subroutine in a module which can be reused elsewhere.

Public Sub showMyMessageBox(ByRef mmbStatus As Integer, ByRef mmbValue As Variant)

' ========================================================================
'
' This routine expects two parameters:
'
'  mmbStatus - An Integer which returns the status of the button you pressed
'  mmbValue - A variant which returns the value of the option chosen
'
' ========================================================================

' In case the form is already open, but hidden, try to close it
  DoCmd.Close acForm, "msgCustom", acSaveNo
  
' Open the form as a dialog
' Waits here until msgCustom closes or is made invisible
  DoCmd.OpenForm "msgCustom", , , , , acDialog
  
' Now the form is invisible code continues
' and retrieves the Return Status and Return Value from msgCustom
  mmbStatus = Forms("msgCustom").msgCustomStatus
  mmbValue = Forms("msgCustom").msgCustomValue
  
' Close form msgCustom
  DoCmd.Close acForm, "msgCustom", acSaveNo

End Sub

The subroutine can be called thus by passing two variable to it which are populated by showMyMessageBox to be used later.

Dim intStatus As Integer, varValue As Variant, strTemp As String
  
' Call showMyMessageBox passing two parameters:
'   intStatus - An Integer which will hold the Return Status of the button you pressed
'   varValue - A variant which will hold the Return Value of the option chosen
  showMyMessageBox intStatus, varValue

I wish I'd know about hiding the form years ago, it's much tidier.

 

[gemma-the-husky]

>>This has an advantage over using OpenArgs which can only pass a string.<<
That’s another thing we can blame the Microsoft documentation for.

OpenArgs is a Variant which means it can be a String or a Long which means it can be a numerical value such as a pointer which means it can be an Object (pointer to Object) which means we can pass Objects through OpenArgs.

Naturally enough, there are some people who have tried to tear down the idea but that’s just because they didn’t think of it for themselves. One of those people does not come to this site anymore. (At least not under their original name.)

On my SkyDrive site, in my signature, there are a number of Access2003 demos showing how to pass Objects through OpenArgs. Look for any demo with OpenArgs in its title.

There is also a write up of it here:-
http://www.access-programmers.co.uk/forums/showthread.php?t=225415


Chris.

what a good idea, Chris. Most useful having a simple way to pass multiple (open) arguments


With respect to using acdialog, I like to be able to resize windows - which you cannot do with a dialog frame, and I prefer to use other methods to manage open forms.

 

[spikepl]

Before all you guys go bananas over a pretty obscure feature, have you forgotten the KISS principle? I may or may not remember the modus operandi of Modal & Visible in 6 months, and then if I see that code can then spend hours trying to figure out how it works. I have made "smart" things before, and nearly always got to curse it subsequently.

 

[Galaxiom]

Before all you guys go bananas over a pretty obscure feature, have you forgotten the KISS principle? I may or may not remember the modus operandi of Modal & Visible in 6 months, and then if I see that code can then spend hours trying to figure out how it works. I have made "smart" things before, and nearly always got to curse it subsequently.

Obscurity is a matter of who is reading the code.

I have had experienced developers on here criticise my solutions for using what they consider to be obscure.

These have included:

Using the Abs() function instead of testing positive and negative case independently.

 boolX = Abs(intY) > 2

Directly letting a boolean property to the result of a comparison instead spelling it out with an If construction.

Me.somecontrol.Visible = (x > y)

No doubt some would spit the dummy if I used Mid on the left side:

Mid(strZ, intX, intY) = "abc"

Or bitwise operators

intX = intA And intB

Fact is, any of these techniques are completely legitimate. Moreover they are faster to process and more concise to write than any alternatives.

If we considered the lack of familiarity as a critera to eschew a particular syntax then we would all be stuck at "beginner" coding levels.

 

[nanscombe]

Obscurity is a matter of who is reading the code.

I have had experienced developers on here criticise my solutions for using what they consider to be obscure.

These have included:

Using the Abs() function instead of testing positive and negative case independently.

 boolX = Abs(intY) > 2

Done that.

Directly letting a boolean property to the result of a comparison instead spelling it out with an If construction.

Me.somecontrol.Visible = (x > y)

Done that, or when converting a binary string to an integer.

intValue = intValue + ((2 ^ intIndex) * abs(Mid(strBinary, 7 - intIndex, 1) = "1")

No doubt some would spit the dummy if I used Mid on the left side:

Mid(strZ, intX, intY) = "abc"

Done that too, recently whilst doing a spot of encryption.

Mid(strMessage, intIndex, 1) = Mid(strMessage, intIndex, 1) Xor Mid(strKey, intIndex, 1)

Or bitwise operators

intX = intA And intB

Yep. Combined two in fact.

 

[Galaxiom]

Google is good. Here is a case with both Abs and setting the property directly.

http://www.access-programmers.co.uk/forums/showthread.php?p=1294508

See post 4 onwards.