Setting controlsource to Dlookup

[Merlene]

Good Day,

I am attempting to set the controlsource of a textbox on a form to a dlookup function but it isn't working. The field just appears blank. When I check the code in the immediate window I get compile error: variable not yet created in this context.

I am relatively new to Access but I've been trying to figure this out on my own with no luck so far.

Me.txtPreviousActionPlan1.ControlSource = _ 
    Nz(DLookup("Q3Milestone1", _
               "TBL_ActionPlan", _
               "SingleName =" & "'" & [SingleName] & "'"), _
       "")

Any help or guidance is appreciated.

[twinnyfo]

Merlene,

It appears you are trying to set the Control Source in VBA? It is possible to do this in VBA, but in your particular case, it is not necessary. You should be able to do this by simply setting the control’s Control Source property to:

= Nz(DLookup("Q3Milestone1", _
             "TBL_ActionPlan", _
             "SingleName = '" & [SingleName] & "'"), _
     "")

Hope this hepps.

[Merlene]

Hi Twinny,

Yes, I've tried it directly in the control source to ensure that the function was otherwise working however, I wanted the control source to be dependent on the quarter. Here's my full code:

Private Sub Form_Load()

    If GetQuarter() = 4 Then
        Me.txtPreviousActionPlan1.ControlSource = _
            Nz(DLookup("Q3Milestone1", _
                       "TBL_ActionPlan", _
                       "SingleName = '" & [SingleName] & "'"), _ 
               "")
     End If

End Sub

[twinnyfo]

Merlene,

Exactly what is it that you are trying to do? Becuase your latest post made it more confusing than the original.

A ControlSource for a control is the source of the data for the particular control. This can either be a field on a table or data that is derived from another source. Thus the ControlSource of a text box will take one of two general forms: [FieldName] (from an underlying Table or Query) or "= A Value or calculated formula" (that is assigned to the Control and displayed on the form).

Your code above is doing neither, which is why it does not work; but this also begs my original question in this post: "What are you trying to do?" Because it is not clear.

If you are truly trying to change the Control Source of the text box, then you must assign a Field Name from the underlying Table or Query which is part of the current underlying record source of the form:

Me.txtPreviousActionPlan1.ControlSource = "Q3Milestone1"

If you are trying to change the value displayed by the text box, then, you ignore the Control Source and simply assign a value to the text box:

Me.txtPreviousActionPlan1 = _
    Nz(DLookup("Q3Milestone1", _ 
               "TBL_ActionPlan", _
               "SingleName = '" & [SingleName] & "'"), _
       "")

Again, it is unclear which you are aiming at.

[Merlene]

My apologies for the confusion.
- The record source for my form is from a query : Qry_UpdateWL
- I am attempting to change the control source of the textbox : txtPreviousActi onPlan1
- I would like the control source to change based on the current quarter. The data in the textbox reflects an officer's comments which changes each quarter.
- The comments are contained in the table: TBL_ActionPlan
- The Field Name with the comments for quarter 4 is : Q3Milestone1
- The query on which the record source is based also contains the Field Name Q3Milestone1
- In attempting to get the control source to work I read that it was not necessary to link to the query and that it was sometimes easier to use the table directly for DLookup function. So I tried that instead (which is why the Dlookup is not linked to the same query as the rest of the form)

I will revert to using the record source query in the Dlookup to see if it works.

[Merlene]

It worked! Thanks so much for your patience and again I apologize for not being clear from the beginning! My code is now:

Private Sub Form_Load()

    If GetQuarter() = 4 Then
        Me.txtPreviousActionPlan1.ControlSource = "Q3Milestone1"
 
    End If

End Sub

[twinnyfo]

I'm glad I coudl hepp! Let us know if there is anything else!