Solved

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

Posted on 2011-09-17
13
237 Views
Last Modified: 2012-05-12
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
        .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
        
            '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"
            'Else
                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 = ""
        
            .MoveNext
        Loop
        End If
    End With
    
    rst.Close
    Set rst = Nothing

End Sub

Open in new window

0
Comment
Question by:brlavery
  • 6
  • 6
13 Comments
 

Author Comment

by:brlavery
ID: 36555480
Ignore the "Set db As Object" line (07). I forgot to omit that.

Also, line 27 is NOT remarked and is fully functional.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36555719
Why do you have a recordsource at all?
0
 

Author Comment

by:brlavery
ID: 36557074
Do you have a suggestion, Peter? I am relatively new at writing code.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 36569004
Your original code snagged the recordset from the form
Set rst = Me.RecordsetClone
Now, you want to deep-six the form's record source.
Ok
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36569019
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 :)
0
 

Author Comment

by:brlavery
ID: 36599147
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.)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36599795
Ok,
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
pictures.zip
0
 

Author Comment

by:brlavery
ID: 36600196
Nick,

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?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36600227
Ahhhhh,
<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.
0
 

Author Comment

by:brlavery
ID: 36600315
I updated your sample db to look closer to mine. The row are backwards, but that's no big deal. Pictures.zip
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36600336
Ok,

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
pictures.zip
0
 

Author Comment

by:brlavery
ID: 36600366
Yea!

Got it to work. Thanks Nick!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36600879
Here's an example with a bound report using two columns.
You'd need 8, but the idea is the same
pictures.zip
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

778 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