Use variable to designate label name in VBA

[whiteman]

Can someone please tell me whether it is possible to use a variable expression to stipulate the label name in the following VBA code line:
[forms]![formname]![label].Caption="string"
I want to assign 100 different values to 100 different labels on my form. It should be done easily using two array variables with the following loop, but the syntax for the label name won't work. I have tried many different syntax structures, but to no avail. I am thinking that it cannot be done in VBA code, but perhaps in DAO format. However, I know nothing of DAO. How can I accomplish this loop?


For CT=1 to 100
[Forms]![formname]!["sn"&CT].Caption=valuearray(CT)
next CT

[CreganTur]

Welcome to the forums- always good to see new members.

I'm guessing that this is for a main form, since you didn't mention anything about subforms.

First suggestion- in the code behind a form you don't have to write out the whole Forms!FornName structure to refer to objects on the currently open form- you can shorten it( and simplify it) using 'Me'.
Me.LabelName.Caption would perform the same actions- it's just a lot cleaner.

Also, when you use 'Me' you need to use a dot (.), not a bang (!)- the reason for this is that using the dot (.) will allow VBA to use Intellisense to help you complete your methods. Using a bang will turn intellisense off (for that bit of code).


How can I accomplish this loop?

For CT=1 to 100
[Forms]![formname]!["sn"&CT].Caption=valuearray(CT)
next CT
I'm not sure why you would want to use this particular loop because, as it is written, it will go through every value of your array in succession and not stop until it reaches 100. You need to choose the label name you want to use from the array first, and then set it to the caption value. Let me know if you need help with that (code samples would be helpful [use the VBA button to wrap code in VBA tags- makes it easier to read and formats code according to VBIDE]).


I am thinking that it cannot be done in VBA code, but perhaps in DAO format. However, I know nothing of DAO.
DAO stands for Data Access Objects- it is a method of creating connections to databases mainly for the purposes of creating and manipulating recordsets. You use VBA to create DAO connections, you just access the DAO object library. This isn't what you want to use to change a label's caption.

[whiteman]

CreganTur, thanks. You offered some helpful advice, but I don't think you addressed my primary problem. I am trying to use this loop to transfer 100 different label captions onto 100 different labels on my form. I only way I can see to do that easily is to use a variable for the label names which are sn1 through sn100. I haven't found any way to write syntax which won't generate error messages. Can a variable ("sn"&ct) be used somehow to designate the label name. I know a variable can be used for a caption value. Please took a second look at my loop with the above explanation in mind.

[Andy Pope]

Try this,



For CT = 1 To 100
Me.Controls("sn" & CT).Caption = valuearray(CT)
Next CT


Where Me referes to the Form object.