Solved

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

Posted on 2011-09-17
13
236 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now