multiple images on a report

[vassilis]

I have a database that contains members of a club. The main table (members)
contains among other details the photograph of the member as a BLOB.

The problem I have is when I try to print their ID cards.
I have a report MembershipIDCard which takes its data from an SQL (Mail Out
SQL) that selects the members we want to run the report on. The form have
bound fields like firstname and surname. And should also display the
photograph of each member.

For the report I am using the following code
On Error GoTo ErrHandler

Dim varRetVal As Long 'Returned Value: number bytes written
to file
Dim intTries As Integer 'Count of tries
Dim mso As Recordset
Dim ymso, x As Integer

errormessage.Visible = False
Set mso = db.OpenRecordset("Mail Out SQL", DB_OPEN_DYNASET)
If mso.RecordCount <> 0 Then
Do Until mso.EOF
If Not IsNull(Me!ImageName) Then
If Len(strTempFileName) > 0 Then 'Kill and set to zero-length if
repeat
Kill strTempFileName
strTempFileName = ""
End If
strTempFileName = TempFile(False) 'Get a unique temporary filename
If Len(strTempFileName) <= 0 Then 'Error in obtaining filename
MsgBox "Unable to obtain temporary file in Sub Form_Open",
vbCritical, "Images in Access Example"
Cancel = True
showPhoto
errormessage.Caption = "No photograph found"
errormessage.Visible = True
Else
strTempFileName = Left$(strTempFileName, Len(strTempFileName) -
3) & mso("ImageFileExtension") 'Me.RecordsetClone("ImageFileExtension")
If Len(Dir(strTempFileName)) > 0 Then 'If there's not a
conflict (already such a file)
Kill strTempFileName 'delete it
End If
hidePhoto
varRetVal = WriteBLOB(mso, "ImageItem", strTempFileName)
If varRetVal <= 1 Then 'Writing to file failed
MsgBox "Unable to to write image to file in Sub Form_Open",
vbCritical, "Images in Access Example"
showPhoto
errormessage.Caption = "No photograph found."
errormessage.Visible = True
End If
Me![Photo].Picture = strTempFileName
End If
Else
showPhoto
errormessage.Caption = "There is no photograph."
errormessage.Visible = True
End If

mso.MoveNext
Loop
End If

mso.Close

When I am running the report I get the correct details for the members but
it is the same photograph that is displayed for all members.

Can someone point out what am I doing work and how I should correct it?

Thanks
Vassilis

 

[Larry Linson]

From an error message in your code, it have placed the code in the Open
event (which event fires just once at the beginning of the Report) and are
trying to loop through the entire Recordset, setting pictures into the Image
Control.

You need to put your code, for just the one picture in the current Record
(not looping through the Recordset) in the Print event of the Detail Section
of the Report (the Format event may fire multiple times and waste
processing).


Larry Linson
Microsoft Access MVP

I have a database that contains members of a club. The main table (members)
contains among other details the photograph of the member as a BLOB.

The problem I have is when I try to print their ID cards.
I have a report MembershipIDCard which takes its data from an SQL (Mail
Out SQL) that selects the members we want to run the report on. The form
have bound fields like firstname and surname. And should also display the
photograph of each member.

For the report I am using the following code
On Error GoTo ErrHandler

Dim varRetVal As Long 'Returned Value: number bytes written
to file
Dim intTries As Integer 'Count of tries
Dim mso As Recordset
Dim ymso, x As Integer

errormessage.Visible = False
Set mso = db.OpenRecordset("Mail Out SQL", DB_OPEN_DYNASET)
If mso.RecordCount <> 0 Then
Do Until mso.EOF
If Not IsNull(Me!ImageName) Then
If Len(strTempFileName) > 0 Then 'Kill and set to zero-length if
repeat
Kill strTempFileName
strTempFileName = ""
End If
strTempFileName = TempFile(False) 'Get a unique temporary
filename
If Len(strTempFileName) <= 0 Then 'Error in obtaining filename
MsgBox "Unable to obtain temporary file in Sub Form_Open",
vbCritical, "Images in Access Example"
Cancel = True
showPhoto
errormessage.Caption = "No photograph found"
errormessage.Visible = True
Else
strTempFileName = Left$(strTempFileName, Len(strTempFileName) -
3) & mso("ImageFileExtension") 'Me.RecordsetClone("ImageFileExtension")
If Len(Dir(strTempFileName)) > 0 Then 'If there's not a
conflict (already such a file)
Kill strTempFileName 'delete it
End If
hidePhoto
varRetVal = WriteBLOB(mso, "ImageItem", strTempFileName)
If varRetVal <= 1 Then 'Writing to file failed
MsgBox "Unable to to write image to file in Sub Form_Open",
vbCritical, "Images in Access Example"
showPhoto
errormessage.Caption = "No photograph found."
errormessage.Visible = True
End If
Me![Photo].Picture = strTempFileName
End If
Else
showPhoto
errormessage.Caption = "There is no photograph."
errormessage.Visible = True
End If

mso.MoveNext
Loop
End If

mso.Close

When I am running the report I get the correct details for the members but
it is the same photograph that is displayed for all members.

Can someone point out what am I doing work and how I should correct it?

Thanks
Vassilis