VBA to reuse data entry popup form (1 Viewer)

[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.

 

[Uncle Gizmo]

I think this is a somewhat simpler solution.

Please note the following features.

The calculation form is called when you click on the command buttons and also the text boxes.

The label caption of the control I call the "Principal Control" is passed through to the Pop-Up calculation form and displayed as the form's caption.

This Class module also allows you to simulate the afterupdate event of the control you are updating, functionality which is not available from the built-in MS Access datepicker and not available from any other pop up calendar form I know of.

The class module which I call "clsCallCalled" can be used in any situation where you have a pop-up form for doing calculations, selecting dates, selecting part of a string for an SQL statement as in the between section of the SQL statement.

Updated Sample file added to the next post...

 

[Uncle Gizmo]

I've re-done the example.

I have added the "After Update Simulation".

The after update simulation is not required to answer Zydeceltico's question, but the functionality is available with this class module, so I thought it was worth demoing...

Why would you need to simulate the after update event?
Imagine a form for entering someone's birth date. You have a separate text box to show their age. When the dates selection form closes, it fills in the birth date field, then runs the code to calculate the person's age.

I don't know of any of the popup date selection forms that allow you to automatically calculate the age without pressing another button to do it.

Not only does this class module make this functionality available for Date Selection Forms, but it will work in any situation where you have a pop-up form. This class module can be added to your database in minutes. Demo video showing how to add it to follow!

 

[Uncle Gizmo]

Here's a run through of the Call Called Class Module in action, in a YouTube video:-

Nifty Access - Call Called - Class Module

 

[Uncle Gizmo]

In this Google Presentation is an overview of how it works:-

 

[MajP]

Nothing simpler.

I am sorry, but why do you keep saying that? It is not simpler. It works but it is not simpler. Why not simply say "Here is an alternate solution"?
My solution requires the user to paste a function and modify one line of code to put in their form and control names.
getValuefromPopUp("frmMeasurement", "txtValue")
Then have a OK button on the form with one line of code
me.visible = false

That is 6 words that need to be updated.

 

[Uncle Gizmo]

This gives you another explanation of how it works..

Nifty Access - Call Called - Class Module

 

[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

 

[Uncle Gizmo]

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

Let me try and explain it a different way round for you.

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.

With my solution the complexity is hidden within the "Call Called Class Module". This makes it very simple and easy to use. The code is practically plug and play.

My only problem is in conveying that message, I am obviously failing!

I know I'm failing because my "Call Called Class Module" has been around since 2008 (if not before) and I don't think anyone has taken it up much.

So my problem is, getting people to understand how it works, and finding it, in other words SEO and the like. This is what I'm working on now.

Your critique has been very helpful in boosting the SEO so thank you very much!

I have built, and am building a range of products, (some which go back to 2008) all are simple drop in components that anyone can use. This "Call Called Class Module" is just another item in my repertoire, and there are more to follow.

If you are still interested in how it works, I used the "Call Called Class Module" as the basis for a set of YouTube I did on object orientated programming last year which you can find in a Google playlist here:- Object Oriented - Nifty Access: https://www.youtube.com/playlist?list=PLhf4YcS5AjdpoI9A0zFTz4hYVs6ox_kIR

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

I think one of the main reasons I didn't get much traction with it, besides not really promoting it anywhere is the fact that I promote it a "class module" for loading calendar forms.

Seeing this thread I realised that it has potential for loading any popup form.

I realised that I shouldn't be promoting it as a "class module" but I should be promoting it as a way of opening popup forms, a simple and easy to set up way, and providing a lot of extra features without any VBA.

In fact did you notice that when the popup form opens, the forms caption is set to the text contained in the label of the control it is called from?

It also works on subforms and indeed the simulated after update event also works on subforms without the user having to write complicated VBA statements which interrogate subforms through the subform/subreport control and the like, which I have noticed often caused much confusion in people new to VBA, mainly because of the awkward way that Microsoft decided to name them.

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

So on your form put a hidden text box on your from.

I stopped using "hidden controls" a few years ago. I refer to them as the the "yellow boxes" "yellow perils" because traditionally they have their back colour set to "yellow". When you view the form in design view, they stick out like sore thumbs! Of course there's nothing wrong with using them, but they just seemed a bit untidy to me. A better way is to set a custom property within your form code module.

The following are examples of custom properties, these are taken from, and can be found in the CallCalled Class Module:-

'*******************************************************************************************************
'************************ Declare the Private Variable(s) used in this Class ***************************
'*******************************************************************************************************
Private mobjPrincipleCtrl As Object
Private mstrCallingCtrlName As String
Private mfrmCallingForm As Form
Private mstrPrincipleCtrlCaption As String
Private mFlgDevMode As Boolean

'*******************************************************************************************************
'********************************* Custom Properties used in this Class ********************************
'*******************************************************************************************************
Property Get prpCallingCtrlName() As String
    prpCallingCtrlName = mstrCallingCtrlName
End Property      'prpCallingCtrlName Get

Public Property Get prpCallingForm() As Form
    Set prpCallingForm = mfrmCallingForm
End Property      'prpCallingForm Get

Property Set prpPrincipleCtrl(oPassedCtrl As Object)
    Set mobjPrincipleCtrl = oPassedCtrl
End Property      'prpPrincipleCtrl Let

Property Get prpPrincipleCtrl() As Object
   Set prpPrincipleCtrl = mobjPrincipleCtrl
End Property      'prpPrincipleCtrl Get

Property Let prpPrincipleCtrlCaption(strPrincipleCtrlCaption As String)
    mstrPrincipleCtrlCaption = strPrincipleCtrlCaption
End Property      'prpPrincipleCtrlCaption Let

Property Get prpPrincipleCtrlCaption() As String
    prpPrincipleCtrlCaption = mstrPrincipleCtrlCaption
End Property      'prpPrincipleCtrlCaption Get

Property Let prpFlgDevMode(blnFlgDevMode As Boolean)
    mFlgDevMode = blnFlgDevMode
End Property      'prpFlgDevMode Let

Property Get prpFlgDevMode() As Boolean
    prpFlgDevMode = mFlgDevMode
End Property      'prpFlgDevMode Get

You just reference them in the same way you would reference any control on your Form...

These two are "read only" because there is only the "Get" version
prpCallingCtrlName (a String)
prpCallingForm (a Form Object)

This one is an "Object" and is read and write because it has both "Get" & "Set"
prpPrincipleCtrl (an Object)

This one is a "String" and is read and write because it has both "Get" & "Let"
prpPrincipleCtrlCaption

Noticed the difference in the "Get and Let" and the "Get and Set" statements "Get and Set" refers to an Object and "Get and Let" to a string variable..

And the last one is a "Boolean" and is read and write because it has both "Get" & "Let"
prpFlgDevMode

Don't get me wrong I'm not saying don't use invisible controls, I think you should do things the way you are comfortable with, it is much safer! but I think you will benefit from experimenting with new ways of programming.

 

[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!