Solved

How do I loop through the names of unbounded objects?

Posted on 2011-09-14
7
271 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search for text in a .txt file 14 45
Combobox row source 2 21
Archiving Access table older than 6 months 9 39
Exporting Access Tables as CSV 3 24
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

809 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