Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I loop through the names of unbounded objects?

Posted on 2011-09-14
7
Medium Priority
?
282 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
[X]
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
  • 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

715 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