We help IT Professionals succeed at work.

Access Event when TextBox is populated from another form

Murray Brown
Murray Brown asked
on
Hi

I have a text box called LicenceNumber that is populated programmatically from another form using the following code. What is the best even behind the text box LicenceNumber that will be triggered when text is changed from the other form?

 Forms(oNewLicenseeFor).Controls("LicenceNumber") = Nz(DMax("LicenceNumberPK", "t_Licence"), 0)
Comment
Watch Question

President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Murray,

 Normally no events are triggered when you modify a control through code.   What you need to do is modify the control's .Text property to force the events to be fired:

Forms(oNewLicenseeFor).Controls("LicenceNumber").Text = Nz(DMax("LicenceNumberPK", "t_Licence"), 0)

 at which point any events you have setup (ie. BeforeUpdate, AfterUpdate, etc) will fire.

Jim.
John TsioumprisSoftware & Systems Engineer

Commented:
I think Before Update should be the one you want
Alternatively, make the event PUBLIC, and then you can call it e.g.:
Forms(oNewLicenseeFor).Controls("LicenceNumber").Text = Nz(DMax("LicenceNumberPK", "t_Licence"), 0)
call Forms(oNewLicenseeFor).licensenumber_AfterUpdate

Open in new window

Distinguished Expert 2017

Commented:
For some reason, the other three experts let this slide but the real problem is storing the licenseNumber in two places.  It is time to review your schema and normalize it.  Not to mention the fact that if the other form isn't open, you will get an error.  Forms don't store data.  Tables store data and each piece of data belongs in one and only one table.  The only "data" that ever appears in multiple places is foreign keys since they are the glue that connect the tables to each other.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Maybe he's just displaying it....

Jim.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
I am just displaying it
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<I am just displaying it>>

Couple suggestions then to avoid the database hit:

1. In the called form, refer back to your other form(s) and just grab the values you need if they are used (assumes they are open of course).

2. Pass the value into the form using the OpenArgs argument.    While OpenArgs is a single property, one way to pass in multiple things is to use a delimiter and use a TAG + VALUE approach.  i.e.

60        DoCmd.OpenForm "frmEmployers", acNormal, , , acFormEdit, acDialog, "FORMMODE=ADD;SETCTRLTODATA=txtName:" & NewData & ";EXITTOFORM=frmPatients"

Here I'm calling a pop-up form from a Not In List event to add an employer.  So I want the form to be in add mode, the control txtName to be set what was in the list, and when the form is done, come back to frmPatients.

The called form looks at OpenArgs, see's the tag SETCTRLTODATA,  and calls this routine:

Sub SetControlsToData(frm As Form, strData As String)

        ' Set controls on frm to values contained in strData.
        ' Delimiter is a ":".  Format of strData is control name: value.
       
        Dim intPairNumber As Integer
        Dim varControlName As Variant
        Dim varData As Variant
10      intPairNumber = 1

20      Do
30        varControlName = dhExtractString(strData, intPairNumber, ":")
40        If varControlName = "" Then Exit Do
50        varData = dhExtractString(strData, intPairNumber + 1, ":")
60        frm(varControlName) = varData
70        intPairNumber = intPairNumber + 2
80      Loop

End Sub

 This is just one way you could do something like this and there are any number of schemes you could come up with.  But open args is a great way of passing data and avoid going back out to disk.

HTH,
Jim.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks very much Jim