How can I convert a string to an actual form label caption in Microsoft Access VBA?

Question

I have attempted to use array values to loop through different scenarios to dynamically change the caption value of labels in a form. I'm inserting a screenshot of a snippet where I am successful in printing a concatenated value to the Immediate Window. What's in that looks exactly like what I need in my code. However, while i can debug.print these concatenated string values, once I remove the debug.print, everything in my code goes sideways. I'm missing something here, but I've already spent the better part of an entire morning trying to think of what it is with no results.

I'm just concatenating "Me.lbl" + element of myProdArr + "Sales.Caption" + " = " + "element of sales/cost/payable array"

I'm doing this in an attempt to substitute the need to type every last one out so I can DRY up the code. Is this even possible or am I dreaming?

Dim SalesCap, CostCap, PayableCap As String
Dim SalesObj, CostObj, PayableObj As Object

For i = LBound(myProdArr) To UBound(myProdArr)
    SalesCap = "Me.lbl" & myProdArr(i) & "Sales.Caption"
    CostCap = "Me.lbl" & myProdArr(i) & "Cost.Caption"
    PayableCap = "Me.lbl" & myProdArr(i) & "Payable.Caption"
    If Me.DealType = "Used" Then
        Debug.Print SalesCap & " = " & """" & myUsedSalesArr(i) & """"
        Debug.Print CostCap & " = " & """" & myUsedCostArr(i) & """"
        Debug.Print PayableCap & " = " & """" & myUsedPayArr(i) & """"
    Else
        Debug.Print SalesCap & " = " & """" & myNewSalesArr(i) & """"
        Debug.Print CostCap & " = " & """" & myNewCostArr(i) & """"
        Debug.Print PayableCap & " = " & """" & myNewPayArr(i) & """"
    End If
Next i

Any ideas?

Can you post your code in a code block, not just in a screenshot? — HackSlash, Feb 06 '20 at 20:37
It should be noted that what you made is a string that contains code. The only way to go from a string to code that you can then run is by using the VBAProject model to edit your code on the fly. This is never needed, discouraged, and disabled by default. — HackSlash, Feb 06 '20 at 20:39
What you want to do is loop through the control collection and act upon the controls themselves. See this example: https://stackoverflow.com/questions/3344649/how-to-loop-through-all-controls-in-a-form-including-controls-in-a-subform-ac — HackSlash, Feb 06 '20 at 20:40
Should I use an array of the actual controls themselves? Would that work? Instead of trying to chop everything up and merely change the non-repeating portion of the control names ? — Todd Brannon, Feb 06 '20 at 20:54
The control collection is the thing, it exists already. You just use it. https://learn.microsoft.com/en-us/office/vba/api/access.controls — HackSlash, Feb 06 '20 at 20:57

ComputerVersteher · Accepted Answer · 2020-02-06T21:01:17.327

3

You can't concat properties that references a control (OK CallByName can), but you can concat strings in the Controls collection.

SalesCap = Me.Controls("lbl" & myProdArr(i) & "Sales").Caption
' result: SalesCap = Me.Controls("lblExtendedServiceSales").Caption

edited Feb 06 '20 at 21:01

answered Feb 06 '20 at 20:55

ComputerVersteher

2,638
1
10
20

This worked for me - sort of. For whatever reason (likely syntactic misuse on my part that I'm just not seeing), I could not get the code to run as intended using variables, but using Me.Controls("lbl" & myProdArr(i) & "Sales").Caption worked perfectly. All great answers with very helpful and insightful info on the control collection. – Todd Brannon Feb 08 '20 at 16:42

How can I convert a string to an actual form label caption in Microsoft Access VBA?

1 Answers1

Linked