?
Solved

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

Posted on 2011-09-17
13
Medium Priority
?
242 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

752 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