December 20, 2011 at 9:39 am
Hi,
I have a added a code in the After Update event of a textbox which I need to call to display value in another textbox. This is what I have entered in the After Update event:
------------------------------------
Private Sub Text41_AfterUpdate()
[Me].[Text45] = DLookup("DIVISION_CD", "dbo_DIVISION", "[LINE_CD] = '" & [Me].[Text41] & "'")
End Sub
------------------------------------
Can some one pls tell what code or syntax I need to add in the text box control source to call the above After Update event ?
Thanks,
Paul
January 2, 2012 at 9:04 am
Sorry but what you are doing is not usual or customary.
If you want to execute the code then it should not be in the after update event code.
Generally an after Update Event is fired and the User Changes the value to the control.
I did not look at your code to close but stay away from DLOOKUP if you can for a number of reasons (bad performance, code is not straight forward, etc)
Perhaps you should state what you want to happen.
i.e. I need to update ... to ... when ... occurs or whaever.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 2, 2012 at 9:55 am
Agree about Welsh's comments on DLOOKUP......
have you tried "Refresh" or "Requery"....either on the textbox or the parent form?...after your code
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 3, 2012 at 3:21 am
If you want to assign a procedure to the ControlSource property of a control, this procedure MUST be a function, it does not work with a Sub. Notice however that, from the moment you assign a function to the ControlSource property of a control, this control is locked (read-only): it won't be possible to type any value into it or change it with some VBA code. In other words, you have created the equivalent of a computed column in the form.
If you want to go this way, you can use:
Private Function UpdateText45()
UpdateText45 = DLookup("DIVISION_CD", "dbo_DIVISION", "LINE_CD = '" & Me.Text41.Value & "'")
End Function
And, in the "Properties" window, type this on the "Control Source" line of the "Data" tab:=UpdateText45()
If you do so, you must also remove the "Text41_AfterUpdate" sub procedure: it would cause an error ("Run-time error '-2147352567 (80020009)' You can't assign a value to this object"), as the value of the control "Text45" cannot be changed any more.
Be aware that the way the criteria of the DLookUp function is assembled will cause an error if the value of "Text41" is Null. You should test for a Null value (using the IsNull function) or convert a possible Null value to an empty string (using the Nz function), e.g.
If Not IsNull(Me.Text41.Value) Then
UpdateText45 = DLookup("DIVISION_CD", "dbo_DIVISION", "LINE_CD = '" & Me.Text41.Value & "'"
End If
Or:
UpdateText45 = DLookup("DIVISION_CD", "dbo_DIVISION", "LINE_CD = '" & Nz(Me.Text41.Value, "") & "'")
Notice that both won't necessarily yield the same result.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply