By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,738 Members | 1,493 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,738 IT Pros & Developers. It's quick & easy.

Linking a Picture to an Access Report

100+
P: 171
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"

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Load()
  2. Set cnn = CurrentProject.Connection
  3. 'Table containing Logo Path
  4. rst.Open "tblClientDetails", cnn, adOpenDynamic, adLockBatchOptimistic
  5.     With rst
  6.         Me.Logo.Picture = !Logo
  7.     End With
  8. Set rst = Nothing
  9. Set cnn = Nothing
  10. End Sub
  11.  
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
Expand|Select|Wrap|Line Numbers
  1. 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
Oct 29 '09 #1

✓ answered by ADezii

@iheartvba
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 AllowMultiSelect 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.
Expand|Select|Wrap|Line Numbers
  1. 'FIRST, set a Reference to the Microsoft Office XX.X Object Library
  2. Dim strButtonCaption As String
  3. Dim strDialogTitle As String
  4. Dim varFileSelected As Variant
  5.  
  6. 'Define your own Captions if necessary
  7. strButtonCaption = "&Open"
  8. strDialogTitle = "Select Logo File"
  9.  
  10. With Application.FileDialog(msoFileDialogOpen)
  11.   With .Filters
  12.     .Clear
  13.     .Add "Bitmaps", "*.bmp"
  14.     .Add "JPEGs", "*.jpg"
  15.   End With
  16.   'The Show Method returns True if 1 or more files are selected
  17.     .AllowMultiSelect = False                   'Critical Line
  18.     .ButtonName = strButtonCaption
  19.     .InitialFileName = vbNullString
  20.     .InitialView = msoFileDialogViewDetails     'Detailed View
  21.       .Title = strDialogTitle
  22.   If .Show Then     'File selected
  23.      'Absolute Path to Graphic File stored in varFileSelected
  24.      varFileSelected = .SelectedItems(1)        'Can only be 1 File selected
  25.        If DCount("*", "tblLogo") = 0 Then
  26.          CurrentDb.Execute "INSERT INTO tblLogo ([Path]) VALUES ('" & _
  27.                             varFileSelected & "')", dbFailOnError
  28.        Else
  29.          CurrentDb.Execute "UPDATE tblLogo Set tblLogo.[Path] = '" & _
  30.                             varFileSelected & "'", dbFailOnError
  31.        End If
  32.   End If
  33. End With

Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,704
@iheartvba
  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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Report_Open(Cancel As Integer)
    2.   Me![imgLogo].Picture = DLookup("[Path]", "tblLogo")
    3. End Sub
Oct 29 '09 #2

100+
P: 171
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
Oct 29 '09 #3

ADezii
Expert 5K+
P: 8,704
@iheartvba
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 AllowMultiSelect 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.
Expand|Select|Wrap|Line Numbers
  1. 'FIRST, set a Reference to the Microsoft Office XX.X Object Library
  2. Dim strButtonCaption As String
  3. Dim strDialogTitle As String
  4. Dim varFileSelected As Variant
  5.  
  6. 'Define your own Captions if necessary
  7. strButtonCaption = "&Open"
  8. strDialogTitle = "Select Logo File"
  9.  
  10. With Application.FileDialog(msoFileDialogOpen)
  11.   With .Filters
  12.     .Clear
  13.     .Add "Bitmaps", "*.bmp"
  14.     .Add "JPEGs", "*.jpg"
  15.   End With
  16.   'The Show Method returns True if 1 or more files are selected
  17.     .AllowMultiSelect = False                   'Critical Line
  18.     .ButtonName = strButtonCaption
  19.     .InitialFileName = vbNullString
  20.     .InitialView = msoFileDialogViewDetails     'Detailed View
  21.       .Title = strDialogTitle
  22.   If .Show Then     'File selected
  23.      'Absolute Path to Graphic File stored in varFileSelected
  24.      varFileSelected = .SelectedItems(1)        'Can only be 1 File selected
  25.        If DCount("*", "tblLogo") = 0 Then
  26.          CurrentDb.Execute "INSERT INTO tblLogo ([Path]) VALUES ('" & _
  27.                             varFileSelected & "')", dbFailOnError
  28.        Else
  29.          CurrentDb.Execute "UPDATE tblLogo Set tblLogo.[Path] = '" & _
  30.                             varFileSelected & "'", dbFailOnError
  31.        End If
  32.   End If
  33. End With
Attached Files
File Type: zip Logo.zip (15.5 KB, 292 views)
Oct 30 '09 #4

100+
P: 171
Thanks Adezzi,
as always an easy to understand procedure that did exactly what I wanted.

Your help is much appreciated.
Oct 30 '09 #5

ADezii
Expert 5K+
P: 8,704
@iheartvba
Glad it all worked out for you, iheartvba.
Oct 30 '09 #6

NeoPa
Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. =DLookUp('Logo','tblControl','[Company]=''MyCompanyName''')
Oct 31 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.