Get 2 values from popup

[ClaraBarton]

@MajP posted this function for retrieving values from popup forms:

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

How would I go about retrieving 2 values from a popup?

dtstart = getValueFromPopUp("popDate", "begDate")
dtend = getValueFromPopUp("popDate", "endDate")

The way I'm doing it now, it opens for popup twice and just seems messy.

 

[ClaraBarton]

um... nevermind... I see there are other people with the same issue that's been answered. Thank you!

 

[NauticalGent]

You would need to add another control name (popUpControl1, popUpControl2...), to the arguments.

 

[Gasman]

You would need to add another control name (popUpControl1, popUpControl2...), to the arguments.

I would probably use an array?

 

[NauticalGent]

I would probably use an array?

I had thought about the paramarray option and also a delimited OpenArgs too but wanted to keep it simple for now - more for myself!

 

[Gasman]

For both, I think you would end up duplicating the code? What if it were 3, 4 or 5 controls?
With an array, you could use a simple loop.

Anytime I find myself duplicating code, I stop and rethink.

 

[NauticalGent]

With.delimeted OpenArgs, you would use the Split() function to populate an array.

With ParamArray, you could pass an indefinite number of control names as an array to return their values - i have never used one, I just know the concept.

 

[Pat Hartman]

A function returns only a single value. You can code the function to return two mushed values with a delimiter between them such as val1;val2 but then you have to parse the return string and keep in mind that the return will always be a string using this technique so you lose the ability to use the function as functions are normally used.

PS. One function that returns two different values depending on a parameter is not logical. Your one function should always have been two different functions that returned one value or the other.

I think the logic problem is that you are calling the popup as you would a function but the function is actually populating the fields rather than your variable = function statements. So, if you always want the two fields, just let the function populate both fields.

 

[MajP]

@ClaraBarton,
That code was an attempt to write a generic function that can be used most places to return a single value. This works in conjunction with a pop up that has an OK and Cancel button. The OK hides the popup and the Cancel closes it. The popup needs to be called by the function using ACDIALOG.
Since you called the popup with ACDIALOG the code in the calling function halts until the pop up is hidden or closed.
If hidden then you can pull the values from the hidden pop up
If canceled the popup is closed and the code in the function just exits.

Although you could probably write generic code it is probably easier to write a specific code for your problem.

Public Sub PopulateDatesFromPopUp
    dim formName as string
    FormName = "PopUpFormName"
    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)
    me.beginDate = frm.begDate
    me.endDate   = frm.endDate
    docmd.close Acform, formName
  End If
End Sub

For returning multiple values from a single function use a USER TYPE
here is an example.

Public Type DogInfo
  Breed As String
  Age As Integer
  Color As String
  Sex As String
  Spade_Neutered As Boolean
End Type

Public Function GetDogInfo() As DogInfo
  'some code here to pick or assingn a dog
  GetDogInfo.Breed = "Boxer"
  GetDogInfo.Color = "Brown"
  GetDogInfo.Age = 4
  GetDogInfo.Sex = "Female"
  GetDogInfo.Spade_Neutered = True
End Function

Public Sub TestDogInfo()
  Dim msg As String
  Dim inf As DogInfo
  inf = GetDogInfo
  msg = "My new dog is a " & inf.Color & " " & inf.Breed & vbCrLf
  msg = msg & "It is a " & inf.Age & " year old " & inf.Sex
  MsgBox msg
End Sub

 

[MajP]

@ClaraBarton
There are several ways to pull values from a pop up. The trick is you want to have the calling form pull values and not have the called form push values. This makes the code more loosely coupled.

In the trick I am doing this is how it works.
1. the calling code you open the pop up using ACDIALOG which means the code stops executing at that point from where it was called until the pop up is hidden or closed. If you do not use ACDIALOG then the code would continue to run. But with ACDIALOG the code waits.
2. The Pop up has an OK button to hide the form and keep it open. Cancel just closes it
3. When the form closes or is hidden then code execution resumes from where it was called.
4. The calling form/ calling code can then check to see if the pop up is open (hidden). If it is open you can read whatever you want from the pop up. You can pull 1 value or a hundred.
5. Once you read the value/s then need to remember to close the hidden form.

 

[Pat Hartman]

Trying to make reusable code is certainly a worthwhile endeavor. However, knowing what functionality actually qualifies as reusable isn't always that easy to figure out. For this function, returning ONE field seems to make sense to make the function reusable. But you want two fields this time. If you always want two fields, then code the function that way. But if you really want the function to be reusable and work with any popup, one field at a time, then you need to decouple the calling of the popup that gathers the info from the filling of the field. That means that the code to fill the fields becomes a loop inside the popup rather than being called twice from the main form.

If I want a popup to fill fields on the calling form, I don't use a function. I open the popup and pass in a reference to the form. The popup knows what fields it is collecting and when you press the OK button to close the popup, the popup transfers the data you entered to the calling form, one field at a time. Then in the calling form on the line AFTER you open the popup as modal, you refresh the form so you can see the transferred values.

 

[ClaraBarton]

@MajP With your training, piece of cake:

DoCmd.OpenForm "popDate", _
                WindowMode:=acDialog
                If CurrentProject.AllForms!popDate.IsLoaded Then
                    dtstart = Nz(Forms!popDate![begDate])
                    dtend = Nz(Forms!popDate![endDate])
                    DoCmd.Close acForm, "popDate"
                End If
            GetDateRange = "[" & DateFieldName & "] Between #" & dtstart & "# And #" & dtend & "#"

Thank you very much!

 

[Gasman]

How can you close popDate from there?

 

[MajP]

How can you close popDate from there?

See thread 10. Process is explained as best I can. Read steps 4 and 5.

 

[ClaraBarton]

Hmm... Ok button makes invisible...
Go back and close form.

 

[MajP]

Ok button makes invisible

The big trick is that when form A calls PopUp using ACDIALOG code execution stops in the Form A and does not restart until PopUp closes or PopUp made Invisibile.
If your OK buttons makes the PopUp invisible then code execution resumes in Form A and it can pull anything it wants off the invisible but open form. Then when done FormA where code execution has resumed can close PopUp.