?
Solved

How do I loop through the names of unbounded objects?

Posted on 2011-09-14
7
Medium Priority
?
286 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:brlavery
  • 3
  • 3
7 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 36539254
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36539261
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
 

Author Comment

by:brlavery
ID: 36542576
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 26

Accepted Solution

by:
Nick67 earned 1500 total points
ID: 36543861
The format for
Me."GeneratedControlNameForSeat---".Picture blah, blah,blah to get the pictures to show?
is this
Me.Controls(SomeValidStringValueThatIsTheNameOfAControl).Picture = BlahBlahBlah
0
 

Author Comment

by:brlavery
ID: 36555144
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
 

Author Closing Comment

by:brlavery
ID: 36555147
See my follow up comment on the question itself.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36555301
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Implementing simple internal controls in the Microsoft Access application.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

593 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