Linking a Picture to an Access Report

[iheartvba]

Hi,
I am trying to link a picture to an access report as follows:
1. The location of the Picure is stored as a hyperlink field in a table
2. The On Load Event of the report triggers the following code"

Private Sub Report_Load()
Set cnn = CurrentProject.Connection
'Table containing Logo Path
rst.Open "tblClientDetails", cnn, adOpenDynamic, adLockBatchOptimistic
    With rst
        Me.Logo.Picture = !Logo
    End With
Set rst = Nothing
Set cnn = Nothing
End Sub

I have 2 issues with this

My first issue is that whenever a hyperlink is saved in a table it shows the path incorrectly for example C:\Logo.jpg shows as "..\..\..\Logo. jpg" so the code doesn't recognise the path.

My second issue is that the hyperlink needs to be customisable by the user I have currently done this through the

DoCmd.RunCommand acCmdInsertHyperlink

command.

Is there an easier way the user can specify the location of the file, where the first abovemention issue is also avoided

Thanks

Kamal

[ADezii]

Hi,
I am trying to link a picture to an access report as follows:
1. The location of the Picure is stored as a hyperlink field in a table
2. The On Load Event of the report triggers the following code"

Private Sub Report_Load()
Set cnn = CurrentProject.Connection
'Table containing Logo Path
rst.Open "tblClientDetails", cnn, adOpenDynamic, adLockBatchOptimistic
    With rst
        Me.Logo.Picture = !Logo
    End With
Set rst = Nothing
Set cnn = Nothing
End Sub

I have 2 issues with this

My first issue is that whenever a hyperlink is saved in a table it shows the path incorrectly for example C:\Logo.jpg shows as "..\..\..\Logo. jpg" so the code doesn't recognise the path.

My second issue is that the hyperlink needs to be customisable by the user I have currently done this through the

DoCmd.RunCommand acCmdInsertHyperlink

command.

Is there an easier way the user can specify the location of the file, where the first abovemention issue is also avoided

Thanks

Kamal

1. Reports do not have a Load() Event.
2. You can store the Absolute Path to a Graphic File (Logo) in a Table within the Database, then dynamically Load it into an Image Control on the Report.
3. When the following Report Opens, it will dynamically Load the Image File specified by the Path given in the [Path] Field of tblLogo into an Image Control in the Report Header.
   

   Private Sub Report_Open(Cancel As Integer)
     Me![imgLogo].Picture = DLookup("[Path]", "tblLogo")
   End Sub

[iheartvba]

Hi ADezii,
From absolute path I assume you mean a text string. My code also works with an absolute path, the reason I had used a hyperlink was because it allowed the user to change the path by browsing windows explorer rather than copying and pasting the path into the table. Is there a way I can still allow the user to specificy the path to the picture by browsing windows explore using the method you have given.

Thanks

[ADezii]

Hi ADezii,
From absolute path I assume you mean a text string. My code also works with an absolute path, the reason I had used a hyperlink was because it allowed the user to change the path by browsing windows explorer rather than copying and pasting the path into the table. Is there a way I can still allow the user to specificy the path to the picture by browsing windows explore using the method you have given.

Thanks

The following code will:

1. Open the Standard Microsoft Office File Open Dialog Window.
2. Display only Bitmaps (*.bmp), and JGEGs (*.jpg). You can modify this if you like and Add more Filters.
3. Allow only a single File to be selected.
4. Once the User selects a File:
   1. The code checks and sees if there is a Record in tblLogo, if there is none, it Appends the Absolute Path of the Selected File to the [Path] Field.
   2. If a Record exists, it Updates the [Path] Field to the Absolute Path of the File selected.
5. The code has been thoroughly tested and is fully operational.
6. You can Customize several Options in the File Open Dialog, but do not change the AllowMultiSelec t Setting.
7. Any questions, feel free to ask.
8. NOTE: You must set a Reference to the Microsoft Office XX.X Object Library.
9. Download the Attachment to get a better picture of what is going on.

'FIRST, set a Reference to the Microsoft Office XX.X Object Library
Dim strButtonCaption As String
Dim strDialogTitle As String
Dim varFileSelected As Variant

'Define your own Captions if necessary
strButtonCaption = "&Open"
strDialogTitle = "Select Logo File"

With Application.FileDialog(msoFileDialogOpen)
  With .Filters
    .Clear
    .Add "Bitmaps", "*.bmp"
    .Add "JPEGs", "*.jpg"
  End With
  'The Show Method returns True if 1 or more files are selected
    .AllowMultiSelect = False                   'Critical Line
    .ButtonName = strButtonCaption
    .InitialFileName = vbNullString
    .InitialView = msoFileDialogViewDetails     'Detailed View
      .Title = strDialogTitle
  If .Show Then     'File selected
     'Absolute Path to Graphic File stored in varFileSelected
     varFileSelected = .SelectedItems(1)        'Can only be 1 File selected
       If DCount("*", "tblLogo") = 0 Then
         CurrentDb.Execute "INSERT INTO tblLogo ([Path]) VALUES ('" & _
                            varFileSelected & "')", dbFailOnError
       Else
         CurrentDb.Execute "UPDATE tblLogo Set tblLogo.[Path] = '" & _
                            varFileSelected & "'", dbFailOnError
       End If
  End If
End With

[iheartvba]

Thanks Adezzi,
as always an easy to understand procedure that did exactly what I wanted.

Your help is much appreciated.

[ADezii]

Thanks Adezzi,
as always an easy to understand procedure that did exactly what I wanted.

Your help is much appreciated.

Glad it all worked out for you, iheartvba.

[NeoPa]

This was one of the first problems I found a good answer to here on Bytes when I first joined three years ago. Mary, the Dragon Queen, was able to answer from her mine of experience. I've had it in my main company database ever since.

I have a table (tblControl) with an OLE Object field (Logo) in it. Only one record is required, but in situations requiring more flexibility there is room for more. An index field (Company) in the table allows for this flexibility.

The contents of the OLE Object field are a link to a JPEG file on the system (taking up no space in the database).

In any report where I want to include this picture (It's actually the company logo) I have a Bound Object Frame control with a .ControlSource of :

=DLookUp('Logo','tblControl','[Company]=''MyCompanyName''')