December 20, 2011 at 4:19 am
Hi,
I am trying to autopopulate a Textbox45 from another Textbox41(already autopopulated) and have entered this code in the control source property of Textbox45-
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = ' " & [Me].[Text41] & " ' ")
and added this code in the After Update Event property of Textbox41-
Private Sub Text41_AfterUpdate()
[Me].[Text45] = DLookup("DIVISION_CD", "dbo_DIVISION", "[LINE_CD] = ' " & [Me].[Text41] & " ' ")
End Sub
But I get an error- #Name? when I view the form to test it. Could someone please tell what I am doing wrong and if there is another way to do this ?
Thanks,
Paul
December 27, 2011 at 2:34 pm
Is LINE_CD in your dbo_DIVISION table a text column? My first though is that if it is not, the single quotes in your DLOOKUP can be causing problems.
December 28, 2011 at 1:19 am
This cannot work for several reasons.
1. In the control source property of Textbox45, you cannot use [Me] because it is not defined in that context (Me is unknown there if you prefer).
2. There are spaces between the single-quotes and the reference to [Me].[Text41] in the criteria expression of the DLookUp function:
"[LINE_CD] = ' " & [Me].[Text41] & " ' "
3. Even if it would work, you would not be able to change the value of Text45 in the procedure Sub Text41_AfterUpdate(): once a function is assigned to the ControlSource property of a control, the Value property of this control is read-only and trying to change the Value property would cause an error ("Run-time error '2448': You can't assign a value to this object").
Here's one solution:
1. Leave the ControlSource property of Text45 empty.
2. Use this code in the module of the form:
Option Compare Database
Option Explicit
Private Function GetDivisionCD() As Variant
GetDivision_CD = DLookup("DIVISION_CD", "dbo_DIVISION", "LINE_CD = '" & Me.Text41.value & "'")
End Function
Private Sub Form_Current()
Me.Text45.value = GetDivisionCD
End Sub
Private Sub Text41_AfterUpdate()
Me.Text45.value = GetDivision_CD
End Sub
Have a nice day!
December 28, 2011 at 8:18 am
The comment about [Me] being out of context for the data source is correct. That can be modified by using the following syntax: [Forms]![FormName]![Text41]
In the AfterUpdate event for Text41, you only need to write the following: Me.ReCalc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply