Post reply

DLOOKUP Syntax

  • December 20, 2011 at 4:19 am

    #73825

    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

    #1425876

    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

    #1425977

    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

    #1426104

    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