[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 673
  • Last Modified:

Change label captions in Access VBA SEQUENTIALLY

I have a form with labels called lbl1, lbl2, lbl3 ....... lbl60

I want to write code that will refer to each label in turn and change the caption.

This code does NOT work:
Dim X as Integer

For X = 1 to 60
   Me.lbl & X & .Caption = "X"
Next X

I DON'T want to write 60 lines of code like this:
Me.lbl1.Caption = "1"
Me.lbl2.Caption = "2"
Me.lbl3.Caption = "3"
Me.lbl60.Caption = "60"

Any Ideas what I am doing wrong?


3 Solutions
Dale FyeCommented:
me.controls("lbl" & X).Caption = X
Rey Obrero (Capricorn1)Commented:

use this

For X = 1 to 60
   Me("lbl" & X) .Caption = X
Next X
The correct syntax has already been posted.

<Any Ideas what I am doing wrong?>

A couple of things.  

1.  To use a control name as a variable, you need to build/define it as such.  To iterate through sequesntial conrol names, you need to append your loop variable to the "base string".  The beginning of the string needs to be in quotes since it is a constant value, not a variable.
      "lbl" & X
The syntax in earlier posts allows you to use strings in quotes and/or variables:
      Me.Controls("lbl" & x)
The syntax you initially posted will fall over with an error in part because Me.lbl is not a recognized control on your form.

2.  = "X"

The right hand side of your expression has issues too.  Even if the left hand side were syntactically ok, putting your loop variable in quotes like this turns it into a literal value.  Instead of showing a sequence, your 60 captions would all simply be "X".   Dropping the quottes allows the captions of your labels to be set to the value of your loop variable at each iteration.
Biggles1Author Commented:
Thanks everyone.  Worked like a charm! (what was I thinking?)


Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now