Solved

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

Posted on 2011-09-17
13
235 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
Comment Utility
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
Comment Utility
Why do you have a recordsource at all?
0
 

Author Comment

by:brlavery
Comment Utility
Do you have a suggestion, Peter? I am relatively new at writing code.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yea!

Got it to work. Thanks Nick!
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
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…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

12 Experts available now in Live!

Get 1:1 Help Now