Solved

How do I assign series of controls to variable in an MS Access report?

Posted on 2013-02-05
4
246 Views
Last Modified: 2013-02-06
I have a series of labels in my MS Access report named in sequence as lbl_stu1, lbl_stu2, lbl_stu3, etc. When the report Activates, I would like to assign each label to a Control variable and loop through the number of records in a table and assign a text value in each control with a corresponding record number in the table.

I thought this would work:

Dim ctrlStuLbl As Control

    ctrlStuLbl = Nothing

'   loop through all student records
'   --------------------------------------------------
    For i = 1 To 20

        Set ctrlStuLbl = ("lbl_stu" & i)

        ctrlStuLbl.Caption = DLookup("[stu_name]", "t_students", "[stu_auto_id] =" & i)

    Next i
But I get an error on: Set ctrlStuLbl = ("lbl_stu" & i)

I'm sure the answer is simple, but Access help is as useless as usual, and an internet search keeps leading me down the wrong path.

Any help is greatly appreciated in advance!

Thanks!
0
Comment
Question by:CRMEHC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 500 total points
ID: 38857417
You can't refer to controls like that and you only use Set with objects, "lbl_stu" & i is a string.

Try this.
Me.Controls("lbl_stu" & i).Caption = DLookup("[stu_name]", "t_students", "[stu_auto_id] =" & i)

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 38857704
You must use...

  Set ctrlStuLbl  = Me.Controls("lbl_stu" & i)
0
 

Author Closing Comment

by:CRMEHC
ID: 38860832
Exactly what I need! Works perfectly! Thank you! And it's much cleaner without having to assign a variable first.
0
 

Author Comment

by:CRMEHC
ID: 38860854
peter57r - Sorry, but your solution didn't work. I still get an error message.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question