Linking Image Files

[Mark J]

I'm working with a database that gets the majority of it's data via ODBC links. I've created a form that is an information page that draws from a half dozen tables in a single query. As I bring up each new record in the form, I need to bring in a picture of the individual whose info is displayed.
The field "ImageFileName' is part of my query. How do I get Access to go get the proper Image that matches the current record displayed? I have the image table linked in the query by: SSN, so that should be all set. Another issue is that some SSN's have multiple photos on file. I'd like to have the ability to tab through each photo for each SSN.

 

[charityg]

Your sample northwind database has a form that displays pictures. Look at the employees form.

 

[karatelung]

I really don't much about including image files, but it sounds like you'll need a subform for the images linked by the SSN if you want to tab through multiple photos for the person.

 

[Mark J]

I checked out Northwind, and those photos are kept in a table as OLE objects. My files come from ODBC links, and they come in to the table as text. I can’t change the data type. I even ran a make table query of the data and then went to the table to change the data type. That doesn’t work either.
The data (photos) are on a network drive, and when I run a query from the ODBC linked table, it gives me the file name, ex. 00001234.jpg. I need for Access to go to the query or the field in the form, find the file name, and then go get the file and open it as an image.
I’m not well versed in SQL or Visual Basic. I’m sure there is a way to write a command that can do what I need, but I haven’t a clue where to begin.
Thanks for your help!
Mark

 

[KevinM]

Insert an image on your form (any will do).
Remove the path and file name from the properties to delete that pic.

For the following example the name of the pic is MyPic and the name of the field that stores the matching ID/Name in the table is MyID

Create a new public module
Change 'String' to whatever field type 'MyID'
Change 'MYID' field name to YOUR ID Field name

Function GetPic(MyID As String)
On Error Resume Next
Dim mypath As String
Dim MyFile As String

' Path where pictures are stored
mypath = "C:\Pics"

'Change file extn to those of stored images
MyFile = MyID & ".JPG"
MyPic= mypath & MyFile
End Function

On the OnCurrent event of the form put...

MyPic.Picture = GetPic(MyID)

This should import and display the picture with the relevant ID field.

This is the best way to display images on a form /report withoit actually storing the images in a table.

HTH

 

[Mark J]

Thanks HTH!
I won't be back at work until Tuesday, so I'll try it then.
Mark

 

[Erik]

Linking images in reports

The code that you have posted is probably what I am looking for, but I am still struggling to get it to work properly - probably confused on something. Below is how I have modified the code to work for me in a report that runs - the report needs a signature for an individual which is a bitmap image stored in a Signatures folder. Each image is named the same as the corresponding record in the field labled "ID". Here's what I've done:

I've created this module named "GetPic"

Function GetPic(MyID As Integer)

On Error Resume Next
Dim mypath As String
Dim MyFile As String

' Path where pictures are stored
mypath = "C:\INVLOG\Signatures"

'Change file extn to those of stored images
MyFile = [ID] & ".bmp"
MyPic = mypath & MyFile

End Function

There is no event called "On Current" in the report, so I put this code in the "On Open" event for the report:

MyPic.Picture = GetPic(MyID)

However, when I try to run the code, I get this compile error:

Variable not defined (MyID) is highlighted which tells me that I am not defining MyID correctly.

It's not clear to me if I am supposed to be renaming "MyID" to be the field name that stores the matching ID number, which in this case is named "ID". That's because there is also a line of code:

MyFile = [MyField]& ".JPG"

in which I am assuming that "MyField" is supposed to be that field I have named ID.

Can you try to guide me here, as this is pretty important to what I'm working on. Please let me know if I am using the wrong event for the Report in which I am placing the line: My.Pic.Picture = GetPic(ID).

Thanks very much ... Erik

 

[KevinM]

There is a typo in my original code (apologies)...

MyFile = [ID] & ".bmp"

Should be...MyFile = [MyID] & ".bmp"

Where 'MyID' is YOUR unique ID name, so change it to that.

 

[Laurentech]

Erik - try your code in the Detail_Format event of your report.

Larry

 

[Erik]

In this case, the field that contains the unique ID number that links to the image is called "ID". Is that causing a problem? If so, I can change the database to have it called something else.

 

[Erik]

Larry: I do not see the Detail_Format event. I only see On Open, On Close, On Activate, On Deactivate, On No Data, On Page, and On Error. Can you tell me how to get the the Detail_Format event? Thanks for you help ... Erik

 

[Laurentech]

When you are in the code window, there are two drop boxes near the top. Select Detail in the first one and Format in the second one.

Larry

 

[Erik]

Larry: Thanks for telling me how to work with Detail and Format. That has saved me. I simply put this code in there:

Dim DBpath As String
DBpath = [Forms]![frmUpdateRecord]![Path]

'If Dir$(DBpath & [ID] & "_1t.bmp") = [ID] & "_1t.bmp" Then
Me.MyPic.Picture = DBpath & [ID] & ".bmp"

This code works. I created a field named path that stores the path where the Signature IDs are stored. ID is the individuals employee ID which is the same as the signature image. It works great. Now I'll unremark the If part of the code so that it works if the signature does not exist. Thanks again ... Erik

 

[Laurentech]

I'm glad you got it working the way you wanted it to Erik.

Larry

 

[isaacalves27]

Sorry but i am a newbi... I have read a lot about images on forms, but my db is not so big so i would like to try the thing with ther OLE Object anyway. In my table the field :"Photo" is a OLE object. And in my form i opened a dependnt object window. Then i paste the file in to that place and i just can see the name of the file. "photo159.jpg" Why is that?

 

[CliftonW]

Hi Erik

I followed yoru instructions as best I could but for some reason when I followed this instruction:

On the OnCurrent event of the form put...

MyPic.Picture = GetPic(MyID)

I got the message:

Compile error:
Expected variable or procedure, not module

Can you assist at all. My extact code is:


'------------------------------------------------------------
' Photo Module
'
'------------------------------------------------------------
Public Function PhotoModule(MembersPhoto As String)
On Error Resume Next
Dim MyPath As String
Dim MyFile As String

MyPath = "F:/MemebersPhotos/"
MyFile = [MembersPhoto]
MyPic = MyPath & MyFile

End Function

Private Sub Form_Current()

MyPic.Picture = PhotoModule(membersphotos)

End Sub

Grateful for any help you or anyone can provide.

Regards.