VBA to reuse data entry popup form

[Zydeceltico]

Hi All,

I am a VBA novice.

I have two forms used for inspections: frmWeldInspection and frmMillInspection.

Both inspection forms need to record two length measurements - "required" and "actual." I have a popup form (frmMeasurement) to accomplish this.

Currently, on frmWeldInspection, to record "Length Required" I have a control button (cmdOpenLR) that calls frmMeasurement. I enter data into frmMeasurement, click a "Save" button (cmdSaveDims) and a value is returned to a text box next to cmdOpenLR on frmWeldInspection.

I have the exact same condition on frmWeldInspection for "Length Actual." That control button is called cmdOpenLA.

This currently works great for data entry into frmWeldInspection.

However, I also have frmMillInspection where I also need to record "Length Required" and "Length Actual."

Instead of reinventing the wheel, I would like to just call that same frmMeasurement and use it to populate to the appropriate textboxes on frmMillInspection.

The code that I currently use and which works great for frmWeldInspection is this:

Private Sub cmdSaveDims_Click()

Select Case Me.OpenArgs
Case "LR"
[Forms]![frmWeldAssembleInspection]![Length Required] = Nz(12 * [cboFeet], 0) + Nz([cboInches], 0) + Nz([cboFractions], 0)

Case "LA"
[Forms]![frmWeldAssembleInspection]![Length Actual] = Nz(12 * [cboFeet], 0) + Nz([cboInches], 0) + Nz([cboFractions], 0)

End Select

DoCmd.Close
End Sub

I thought the following would work but it doesn't. I shoudl say "it kind of does" but it is clunky. I can get Length Required on frmMillInspection and the value immediately shows in the correct text box on frm MillInspection. When I try to enter a value for Length Actual the frmMeasurement is called; I enter data; click Save; and nothing happens. I've checked all of the names of controls and forms. They are all correct.

Here is the code:

Private Sub cmdSaveDims_Click()

Select Case Me.OpenArgs
Case "LR"
[Forms]![frmWeldAssembleInspection]![Length Required] = Nz(12 * [cboFeet], 0) + Nz([cboInches], 0) + Nz([cboFractions], 0)

Case "LA"
[Forms]![frmWeldAssembleInspection]![Length Actual] = Nz(12 * [cboFeet], 0) + Nz([cboInches], 0) + Nz([cboFractions], 0)

Case "LRMI"
[Forms]![frmMillInspection]![LengthRequired] = Nz(12 * [cboFeet], 0) + Nz([cboInches], 0) + Nz([cboFractions], 0)

Case "LAMI"
[Forms]![frmMillInspection]![LengthActual] = Nz(12 * [cboFeet], 0) + Nz([cboInches], 0) + Nz([cboFractions], 0)

End Select

DoCmd.Close
End Sub

My question: Is there a more elegant way of achieving a call to frmMeasurement from other unrelated forms and have the calculated data on frmMeasurement be returned to the appropriate field on the form I called it from?

The following code was suggested to me before but I am too much of a noob to understand how to use it: Namely, I do not see/comprehend how to tell this code to "choose" between frmWeldInspection and frmMillInspection - in other words, how does this code distinguish what the open, active, calling form is?

Public Function getValueFromPopUp(formName As String, PopUpControlName As String) As Variant
'FormName: Name of the popup form
'PopupControlName: Name of the control on the pop up/dialog that you want the value
Dim frm As Access.Form
DoCmd.OpenForm formName, , , , acFormEdit, acDialog
'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)
getValueFromPopUp = frm.Controls(PopUpControlName).Value
DoCmd.Close acForm, formName
End If
End Function

Thanks in advance,

Tim

 

[Minty]

You've referred to [Length Required] on one form and [LengthRequired] (no space) on the other. Is that correct?

Also check you are passing the OpenArgs correctly, use a Debug.Print OpenArgs to ensure the correct string is being passed.

You could also simplify the code a bit by using something like

Dim ValToStore as Single  [COLOR="SeaGreen"]' Not sure if this is the right data type?[/COLOR]

ValToStore = Nz(12 * [cboFeet], 0) + Nz([cboInches], 0) + Nz([cboFractions], 0)

Debug.Print Me.OpenArgs    [COLOR="seagreen"]' Use the immediate window to see the result in the VBA editor (Press ctrl + G to bring it up)
[/COLOR]
Case "LR"
[Forms]![frmWeldAssembleInspection]![Length Required] = ValToStore
 etc. etc.

 

[MajP]

Yes that is my code. I would never have a pop up form hard wired to set the value on another form. That is just bad design in my opinion. No flexibility. The calling form should be able to call a popup and "pull" the value.

So on your form put a hidden text box on your from. Make its controlsource
txtValue: = Nz(12 * [cboFeet], 0) + Nz([cboInches], 0) + Nz([cboFractions], 0)
or set it value in code on the "OK" button. As stated the OK button sets the form to visible = false

Then simply call the code from anywhere in your database. The code needs to go in a standard module

Public Function getValueFromPopUp(formName As String, PopUpControlName As String) As Variant
'FormName: Name of the popup form
'PopupControlName: Name of the control on the pop up/dialog that you want the value
Dim frm As Access.Form
DoCmd.OpenForm formName, , , , acFormEdit, acDialog
'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)
getValueFromPopUp = frm.Controls(PopUpControlName).Value
DoCmd.Close acForm, formName
End If
End Function

So on some other form you would do something like

dim someVariable as variant
someVariable = getValuefromPopUp("frmMeasurement", "txtValue")
if not isnull(someVariable) then
  do something
end if

 

[MajP]

So the way this works it pops open any form in Dialog. At that point the code calling it Stops. When you hide the form on the OK button code resumes. The function then checks if the form is loaded(hidden) or not loaded. If loaded it returns the value in the control you specified. If not loaded the function returns null.

 

[MajP]

let's see if you can "get it" this time shall we!

Trust me I can understand any VBA code. Not saying I do not get it, just calling it what it is. Just saying it is a solution without a problem. The thing you say is "Magic" and cannot be done can most certainly be done. VBA is event driven. I you want to trap another forms event, then just trap it. No reason for all the complexity.

I still say the first solution I provided is the simplest, but if you want to trap the after update then simply raise the event and pass back the value

FrmMeasurement

Public Event NewMeasurement(Measurement As Double)

Private Sub Form_Open(Cancel As Integer)
'Stop the Form Opening in the Nav Pane
'https://www.access-programmers.co.uk/forums/showthread.php?t=281839
'https://msdn.microsoft.com/en-us/library/office/ff196795.aspx
    Dim strCurrentName As String
    strCurrentName = Application.CurrentObjectName
    If strCurrentName = Me.Name Then
        Cancel = True
        Exit Sub
    End If
End Sub

Private Sub btnCancel_Click()
'Purpose:   Close without transferring back
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Private Sub btnOk_Click()
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Private Sub cboFrac_AfterUpdate()
    Me.txtAnswer = Nz(12 * Me.cboFt, 0) + Nz(Me.cboInch, 0) + Nz(Me.cboFrac, 0)
    If Not IsNull(Me.txtAnswer) Then RaiseEvent NewMeasurement(Me.txtAnswer)
End Sub

Private Sub cboFt_AfterUpdate()
    Me.txtAnswer = Nz(12 * Me.cboFt, 0) + Nz(Me.cboInch, 0) + Nz(Me.cboFrac, 0)
    If Not IsNull(Me.txtAnswer) Then RaiseEvent NewMeasurement(Me.txtAnswer)
End Sub

Private Sub cboInch_AfterUpdate()
    Me.txtAnswer = Nz(12 * Me.cboFt, 0) + Nz(Me.cboInch, 0) + Nz(Me.cboFrac, 0)
    If Not IsNull(Me.txtAnswer) Then RaiseEvent NewMeasurement(Me.txtAnswer)
End Sub

In the calling form simply trap the event

Private WithEvents Popup As Form_frmMeasurement
Private DestinationControl As Access.Control
Private Sub Popup_NewMeasurement(Measurement As Double)
  'Trap event procedure
   DestinationControl.Value = Measurement
End Sub
Private Sub btnLngAct_Click()
Dim strFrmName As String
strFrmName = "frmMeasurement"
    Set DestinationControl = Me.txtLngAct
    DoCmd.OpenForm strFrmName
    Set Popup = Forms(strFrmName)
End Sub

Private Sub btnLngReq_Click()
Dim strFrmName As String
strFrmName = "frmMeasurement"
    Set DestinationControl = Me.txtLngReq
    DoCmd.OpenForm strFrmName
    Set Popup = Forms(strFrmName)
End Sub
Private Sub txtLngAct_Click()
Dim strFrmName As String
strFrmName = "frmMeasurement"
    Set DestinationControl = Me.txtLngAct
    DoCmd.OpenForm strFrmName
    Set Popup = Forms(strFrmName)
End Sub
Private Sub txtLngReq_Click()
Dim strFrmName As String
strFrmName = "frmMeasurement"
    Set DestinationControl = Me.txtLngReq
    DoCmd.OpenForm strFrmName
    Set Popup = Forms(strFrmName)
End Sub

 

[arnelgp]

The code isn't for you, you are obviously an expert VBA coder who is quite happy coding your own VBA code. However there are people that can't be arsed with VBA, and are looking for a simpler solution.

agree. there is no point arguing which solution is best. same as a menu on the table. the op will decide which one to consume.

 

[MajP]

The code isn't for you, you are obviously an expert VBA coder who is quite happy coding your own VBA code. However there are people that can't be arsed with VBA, and are looking for a simpler solution.

Thanks Arnelgp

But that is my whole point because the above quote makes NO SENSE. The process I showed requires the user to simply provide the name of the form and the name of the control. That is it. It is one line of code here, and on the popup it is one line of code on the OK button. It is 6 words of code.

getValuefromPopUp("frmMeasurement", "txtValue")

Again I never said it was the best solution, but it is pretty damn simple. It works in 99% of the cases a user would need it. Gizmo code really provides no additional features except a caption. It is fine code, but sell it for what it is. I do not know what it is about this forum, but some people have to have the last word on everything. Again I am not disagreeing that Gizmo's code has utility, but do not go saying it is simpler when it is the opposite. Just say "here is another approach to consider." It is like some people feel compelled to one up everyone. Sometimes just give it a rest.

 

[isladogs]

I do not know what it is about this forum, but some people have to have the last word on everything....
... It is like some people feel compelled to one up everyone. Sometimes just give it a rest.

Very true MajP.

But it seems to me that you are proving the point here by your own actions

NOTE: I haven't looked at either solution - just read the thread!

 

[ClaraBarton]

Yes that is my code. I would never have a pop up form hard wired to set the value on another form. That is just bad design in my opinion. No flexibility. The calling form should be able to call a popup and "pull" the value.

So on your form put a hidden text box on your from. Make its controlsource
txtValue: = Nz(12 * [cboFeet], 0) + Nz([cboInches], 0) + Nz([cboFractions], 0)
or set it value in code on the "OK" button. As stated the OK button sets the form to visible = false

Then simply call the code from anywhere in your database. The code needs to go in a standard module

Public Function getValueFromPopUp(formName As String, PopUpControlName As String) As Variant
'FormName: Name of the popup form
'PopupControlName: Name of the control on the pop up/dialog that you want the value
Dim frm As Access.Form
DoCmd.OpenForm formName, , , , acFormEdit, acDialog
'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)
getValueFromPopUp = frm.Controls(PopUpControlName).Value
DoCmd.Close acForm, formName
End If
End Function

So on some other form you would do something like

dim someVariable as variant
someVariable = getValuefromPopUp("frmMeasurement", "txtValue")
if not isnull(someVariable) then
  do something
end if

Just... wow! Works great!