How do I loop through the names of unbounded objects?

Hello, experts!

I have created a seating chart form that is supposed to look up a student's [ID] number and assigned [chair] from a table, and then load a corresponding image based off of the student's ID number (ex: 12345678.png for student ID 12345678). There are 64 unbound objects in four rows of 16 each. They are labeled Seat101, Seat102, Seat103, etc. which correspond to the table values for [chair] (101, 102, 103, etc.). Chairs are assigned by row then seat (Row 3, seat 12 would be 312).

I would like a loop that automatically chooses the correct object control based off of the student's seat assignment and then populates that object with the student's image. The code is attached. Thank you.
Private Sub Form_Open(Cancel As Integer)

    ' Set error trap
    On Error Resume Next
    
    Dim strPath As String
    Dim strChair As String

    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    With rst
        If .RecordCount Then
            .MoveFirst
            Do Until .EOF    'When the last record is reached the code stops
                Me.Bookmark = .Bookmark
                      
                ' Assign ID as path for images
                strPath = Me.ID
            
                'Determine current chair
                strChair = Me.Chair
                strChair = "Seat" & strChair
                
                'Establish path for existing images and default path for missing immages
                If Err <> 0 Then
                    strPath = CurrentProject.Path & "\Resources\Images\ths.png"
                Else
                    strPath = CurrentProject.Path & "\Resources\Images\Students\" & strPath & ".png"
                End If
                
                'Load picture to form
                Me!strChair.Picture = UCase(strPath)
                strChair = ""
                strPath = ""
                .MoveNext
            Loop
        End If
    End With
    rst.Close
    Set rst = Nothing

End Sub

Open in new window

brlaveryAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Nick67Connect With a Mentor Commented:
The format for
Me."GeneratedControlNameForSeat---".Picture blah, blah,blah to get the pictures to show?
is this
Me.Controls(SomeValidStringValueThatIsTheNameOfAControl).Picture = BlahBlahBlah
0
 
Nick67Commented:
Ok,
I take it that the form itself is bound to a recordset containing 64 student ID's
At its heart you need two nested loops

dim myRow as integer
dim myColumns as integer

for myrow = 1 to 4
    for mycolumn = 1 to 16
        me.controls("Seat" & myrow & IIF(mycolumn < 10,"0" & mycolumn,mycolumn).Picture = SomeStringVariableYouCreate
    next mycolumn
next myrow

Now, you need to wrap those two loops in a Do While rs.eof = false loop that lets you work out SomeStringVariableYouCreate in row and column order
That's one way

Given that it seems me.Chair may also give the "Seat101" string, you just need to replace line 32 (Me!strChair.Picture = UCase(strPath)
with
me.controls(Me.strChair.value).Picture = UCase(strPath)
0
 
Jeffrey CoachmanMIS LiasonCommented:
I, personally would rather have a sample DB...

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.


JeffCoachman
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
brlaveryAuthor Commented:
Let me clarify the issue a bit. My code will generate the correct label for each control object based off of the individual record (Student x with chair 101 = Seat101 = name of unbounded control object for the picture). The problem I am having is placing that generated control name into a single line of code (or a relatively small number of lines). How do I write something like:

Me."GeneratedControlNameForSeat---".Picture blah, blah,blah to get the pictures to show?
0
 
brlaveryAuthor Commented:
After a little tinkering, I got it to work. Nick67's code worked when I  took out the strChair string and re-worked it to this:

Me.Controls("Seat" & Me.Chair).Picture = strPath

Thanks for the help!

P.S. For those of you out there trying to make a control collection work with pictures, make sure you change the unbounded objects into image frame. I initially forgot to do that, but caught my mistake early on.
0
 
brlaveryAuthor Commented:
See my follow up comment on the question itself.
0
 
Nick67Commented:
Here's how I would have done it.
Throw it in C:\temp to play with it

Sorry you struggled with it
pictures.zip
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.