Solved

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

912 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now