473,324 Members | 2,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Linking a Picture to an Access Report

171 100+
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

6 10423
ADezii
8,834 Expert 8TB
@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
iheartvba
171 100+
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
8,834 Expert 8TB
@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, 327 views)
Oct 30 '09 #4
iheartvba
171 100+
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
8,834 Expert 8TB
@iheartvba
Glad it all worked out for you, iheartvba.
Oct 30 '09 #6
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

3
by: Ken | last post by:
I have a win 2000 database of autographs and scanned photos. They are in the SAME directory. In the table, my "ImagePath" text field shows JUST the image name (i.e. "blank.jpg"). I have an image...
1
by: Tamer Sabry | last post by:
Hi, I am making a real estate database for my company. I am new to access. I made a picture field and I want to have different pictures for every record. My problem is no matter if I have an OLE...
5
by: chrisse_2 | last post by:
Hi, All the records in my database will contain at least one picture. At the moment all the images are part of the database as ole objects although the database is way to big and there is only...
6
by: Salad | last post by:
Hi: I have a Bill of Lading template from the printer that I saved as a BMP. I created a new report and in the report's picture property told it the BMP file name. It brought the image in just...
3
by: meyvn77 | last post by:
Hello - I am looking for the best way to store images in a Access DB. My Idea - I have a table with 150,000 records. These recoreds represent a Crash (Traffic Accident). I have 50 different...
4
by: NASAdude | last post by:
I'm working on distributing a database using VS Tools for Office, and am having trouble with linked images on forms/reports. The image objects have a default path\file set on the .Picture property,...
3
by: Parasyke | last post by:
I have a form that has a bound textbox displaying a picture path (in another folder) for an image (e.g. C:\Documents and Settings\My Documents\My Pictures\SymbolVRC 6940.jpg). I need this actual...
1
by: Parasyke | last post by:
I have a report that I want to print for a product specification sheet with a photo of the product. I have the photos stored in a separate folder from my Access 2000 database. I have a form that...
3
by: Parasyke | last post by:
Thanks anyone in advance... I have a product report that prints out data about the product including a picture... at least that's what I need to happen. On my report I have a linked Image with the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.