Access VBA - Prompt a form and return value to caller

[ironfelix717]

Hi,

A common feature my apps require is getting a user selected value from a universal popup form and using that value in a sub.
I have been lousely designing such forms where they are passed an input (open args) and based on that arg, the form passes to the next function in the stack. Heres a written/sudo example:

In the "sales" form:

Private sub ParentFunction()
docmd.openform "Selection_Form",,,,,"Sales"
end sub

Selection_Form is opened.
User makes selection.
Select Case determines next function in stack to be called.
In this case, it is a routine in the "Sales" form called "ChildFunction"
"ChildFunction" is called from Selection_Form and passed a value.

Back to the sales form:

Public Sub ChildFunction(value)
DoSomething with value
end sub

This is obviously a very lousy way of doing things and creates a troubleshooting havoc.
What I would like to do is treat these universal popup forms as a variable, such that you would see with built in "InputBox" or "MsgBox".

Example:

Private Sub ParentFunction()
dim val        as integer

val = SelectionForm()  'selection form is opened and code is halted until closed

'do something with val'
end sub

Using a class object is fine, but a simple function would be the cleanest solution.

Is this possible? I assumed it wasn't so I never dug into it. Hoping we can find a solution!
cheers

 

[MajP]

I make universal pop up forms this way.
On the Pop up I have two buttons OK and Cancel. The OK sets the form to visible = false. The cancel closes the form.
I call these forms in acdialog. Then code execution stops that the point where the pop up is open. Then when the popup is Closed or Hidden execution resumes in the calling form.
If closed do nothing.
If hidden you can read the value off of a control. I wrap all of this in a single function.
This allow me to call a pop up form from anywhere and do whatever I want with it.
This is the function call

Public Function getValueFromPopUp(formName As String, PopUpControlName As String, Optional MyOpenArgs As String = "None") As Variant
  'FormName: Name of the popup form
  'PopupControlName: Name of the control on the pop up/dialog that you want the value
  'MyOpenArgs: If you want to pass something to your pop up.
  Dim frm As Access.Form
  Dim ctrl As Access.Control
  'Code execution stops after next line of code
  DoCmd.OpenForm formName, , , , acFormEdit, acDialog, MyOpenArgs
  'Wait until form is closed or hidden
  'The popup needs an OK button that hides the popup(me.visible = false), and a Cancel button that just closes it
  If CurrentProject.AllForms(formName).IsLoaded Then
    Set frm = Forms(formName)
    Set ctrl = frm.Controls(PopUpControlName)
    If ctrl.ControlType = acLabel Then
      getValueFromPopUp = frm.Controls(PopUpControlName).Caption
    Else
      getValueFromPopUp = frm.Controls(PopUpControlName).Value
    End If
    DoCmd.Close acForm, frm.Name
  End If
End Function

I have seen overly complicated classes, that just do not add anything to it.

 

[MajP]

The other way to do it is for the popup to raise an event that you can trap in the main form. In this way any form can call the pop up and react to an event on the pop up.

 

[ironfelix717]

The other way to do it is for the popup to raise an event that you can trap in the main form. In this way any form can call the pop up and react to an event on the pop up.

MajP,

Very clever. Awesome solution. This will clean up so much code in my app.

I can't see how the raise event method would be any cleaner, or rather, as versatile. If you think the raise event method would be even simpler yet, I would be curious on hearing more about that.

Thanks for the solution!

 

[MajP]

I have been using this approach for a long time. It is really so simple and reusable.

Here is the same thing done with events. It opens some other options. You could react to many events in the popup and you can call the pop up in a non dialog mode. You do not have to stop execution in the calling form. So there are cases where reacting to another forms events can really open up options. This example is not that great, I will see if I can do something more illustrative.

To modify the above I just added an event to the calculator form and raise that event in the OK button.

Public Event OKclicked(ReturnValue As Variant)

Private Sub cmdOK_Click()
    Dim rtn As Variant
    HandleOperatorClick "="
    DoEvents
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'---------------------------------------------------------------------------- Raise Event ---------------------------------------------------------------------------------
    If Me.lblReadOut.Caption <> "" Then rtn = CDbl(Me.lblReadOut.Caption)
    RaiseEvent OKclicked(rtn)
    DoCmd.Close acForm, Me.Name
End Sub

Then in the calling form I trap the event

Public WithEvents Calc As Form_frmCalculatorWithEvents


Private Sub Calc_OKclicked(ReturnValue As Variant)
  If Not IsNull(ReturnValue) Then Me.ActiveControl = ReturnValue
End Sub

 

[MajP]

For some reason I cannot edit the above reply. If not clear. I was trying to say the first example using the Hide / Close trick is the easiest and most versatile IMO. Trapping another form events can be a little trickier, but a valuable tool and creates other possibilities.

This is still a kind of silly example but it shows trapping 2 custom events and 1 native control event. So you can trap any native event of the pop up form or its controls. Does not have to be a custom event.

 

[Uncle Gizmo]

A common feature my apps require is getting a user selected value from a universal popup form and using that value in a sub.

I have a slightly different solution to MajP's which uses a Class Module.

I call the Class "clsCallCalled" . It's available on my website here:-

CallCalled Class Module - Nifty Access

CallCalled Class Module I will be adding information about my "CallCalled" Class Module here, as it becomes available. The most recent example of it is on Access World Forums here:-VBA to reuse data entry popup form Class Module - Overview Class Module - Overview Video 1 (01:09) You can see how...

www.niftyaccess.com

Along with Video instructions.

With the "call called" class module, all you need to do is open the form with normal code and the class module does everything for you.

The particular feature I am proud of is that when you close the form and pass the information back to the calling form, I also have a technique for simulating after update event of the control that called it.

This means that if you have a popup form which gives someone's date of birth, when you have chosen the date of birth and passed it back into the calling form, then you can run code to calculate and show the person's age!

My class also works in exactly the same way when called from a control on a subform. You also have the ability to check that the form carrying the subform has been closed and cancel the operation if necessary.

 

[NauticalGent]

Then when the popup is Closed or Hidden execution resumes in the calling form.

I was unaware of that attribute. Of course I knew execution resumes after closing but not when it was hidden. Good tip, thanks!

 

[MajP]

Demo to return values from possibly multiple controls. Easier to roll your own procedure then make a generic one

Private Sub txtCity_DblClick(Cancel As Integer)
  getValues
End Sub
Private Sub txtCustomer_DblClick(Cancel As Integer)
  getValues
End Sub
Private Sub txtCountry_DblClick(Cancel As Integer)
  getValues
End Sub


Public Sub getValues()
  Dim Cust As String
  Dim Country As String
  Dim City As String
  Dim formName As String
  Dim frm As Access.Form
    
  formName = "frmMultipleControls"
  DoCmd.OpenForm formName, , , , acFormEdit, acDialog
 
  If CurrentProject.AllForms(formName).IsLoaded Then
    Set frm = Forms(formName)
    Me.txtCustomer = Nz(frm.Controls("cmboCustomer"), "")
    If Me.txtCustomer = "" Then Me.txtCustomer = Nz(frm.Controls("cmboCountry"), "")
    If Me.txtCustomer = "" Then Me.txtCustomer = Nz(frm.Controls("cmboCity"), "")
    DoCmd.Close acForm, frm.Name
  End If
End Sub