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
Solved

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

Posted on 2013-02-05
4
244 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
  • 2
4 Comments
 
LVL 33

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

839 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