Solved

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

Posted on 2013-02-05
4
243 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

777 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