How do I code a form and report to load multiple images without setting a recordsource?

I have a working code that loads multiple pictures into a grid on a form (I use it as a seating chart for my students). I currently have the record source property for the form set and as expected, when I print, I get redundant copies of the form for each record in the recordset. How do I re-write the code to leave the form's record source blank yet still open the query (qryDeptStudents) and get a nice set of orderly pictures? I would like to do this in a report as well. Any suggestions?

Also, I am getting numerous error code 2220s  even though all images load every time I run the code (I get some error code 0s, too). I had to turn off the error line to display a preselected image when the appropriate image for a student's record was missing. Any ideas here as well?

Private Sub Form_Load()

 'Set error trap
 On Error Resume Next
 Dim strPath As String
 Dim db As Object
 Dim rst As DAO.Recordset
 Set rst = Me.RecordsetClone
    With rst
        If .RecordCount Then
        Do Until .EOF
            'When the last record is reached the code stops
            Me.Bookmark = .Bookmark
            ' Assign ID as path for images
            strPath = Me.ID
            'Establish path for existing images and default path for missing immages
            Debug.Print "Error code: " & Err
            'If Err <> 0 Then
                'strPath = CurrentProject.Path & "\Resources\Images\ths.png"
                strPath = CurrentProject.Path & "\Resources\Images\Students\" & strPath & ".jpg"
            'End If
            'Load picture and name to form
            Me.Controls("Seat" & Me.Chair).Picture = UCase(strPath)
            Me.Controls("Student" & Me.Chair) = Me.Last & ", " & Me.First
            strPath = ""
        End If
    End With
    Set rst = Nothing

End Sub

Open in new window

Who is Participating?
Nick67Connect With a Mentor Commented:
Your original code snagged the recordset from the form
Set rst = Me.RecordsetClone
Now, you want to deep-six the form's record source.
Set rst = currentdb.openrecordset("WhateverTheFormsQueryUsedToBe",dbopenDynaset)

Transmogrify this a little
        'When the last record is reached the code stops
            'Me.Bookmark = .Bookmark
            'no record source so no bookmark!
            ' Assign ID as path for images
            'no record source for the form so Me.ID isn't going to play
            strPath = rst!ID
            'strPath = Me.ID

These to will probably need some alteration

            'Load picture and name to form
            Me.Controls("Seat" & Me.Chair).Picture = UCase(strPath)
            Me.Controls("Student" & Me.Chair) = Me.Last & ", " & Me.First
But I don't understand what the last line is doing
Replace Me.  with rst! when the Me. was refering to controls that are no longer relevant because you deep-sixed the form's record source
brlaveryAuthor Commented:
Ignore the "Set db As Object" line (07). I forgot to omit that.

Also, line 27 is NOT remarked and is fully functional.
Why do you have a recordsource at all?
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

brlaveryAuthor Commented:
Do you have a suggestion, Peter? I am relatively new at writing code.
Post a sample and a zipfile with pictures (dummy or otherwise) and I'll have a gander at the error messages.
And you shouldn't be printing a form!  (Although some other Experts have disagreed with my adamant stand on that issue :)
That's what reports are for :)
brlaveryAuthor Commented:
It will take a couple of days for me to post the file. My school's server farm crashed (funny - I thought redundant meant we'd never need to woory about crashes). I have no access to the most recent code and I have to clean up an older version to the point that I generated this request.)
On your last question I had posted a sample to demo how to do tackle your problem.
I have updated that sample, and added an unbound form, so you can see how the two differed.

In the sample, the code changes is very simple.
set rst = Me.recordsetclone
gets replaced with
set rst = currentdb.openrecordset("tblPicPaths") and that's it.

Put the files from the zipfile in c:\temp to play with it
brlaveryAuthor Commented:

Your db is helpful to clean up my coding. (Not to burst any bubbles, but) I solved the form issues a week ago with your help. My problem is the report. I have tried and tried to get the report to do what the form does. I added your changes to the report's code and it is partially working. It finds and debug.prints the correct student ID number, but now I am trying to figure out how to change the two lines:

            Me.Controls("Seat" & Me.Chair).Picture = UCase(strPath)  
            Me.Controls("Student" & Me.Chair) = Me.Last & ", " & Me.First  

BTW, the second line displays each student's name underneath their pictures for easy identification. Each control box is called Student### and like the Seat###, it calls upon the selected record ID to get their Last and First names and displays them in the current box after painting the image above. Any help?
<How do I re-write the code to leave the form's record source blank yet still open the query (qryDeptStudents) and get a nice set of orderly pictures?>
I was not under the impression that a report was what was being manipulated here.
Give me a bit and I'll create a report so you can see how that would work.
brlaveryAuthor Commented:
I updated your sample db to look closer to mine. The row are backwards, but that's no big deal.

Here's an unbound report.
Same idea, put it in c:\temp

There's more unbound controls, and two more lines of code.
Code goes in the Detail_Format event.

You could do a bound report, with 8 columns and bound controls.
You'd have one image control, and two textboxes, and a whack of formatting fun.
But it's doable too
brlaveryAuthor Commented:

Got it to work. Thanks Nick!
Here's an example with a bound report using two columns.
You'd need 8, but the idea is the same
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.