Solved

How do I loop through the names of unbounded objects?

Posted on 2011-09-14
7
276 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 26

Accepted Solution

by:
Nick67 earned 500 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email question 19 93
SQL Server 2005 running VERY slow on Virtual Machine 18 67
Calculation in a Report 13 42
Access 2003 query, remove dupes 17 25
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

739 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