423,309 Members | 2,194 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Display images in your DB without using Attachment Fields

twinnyfo
Expert Mod 2.5K+
P: 2,532
The later versions of MS Access have a field type called Attachment. This allows the user to "upload" documents and images into the database, so that all things are stored neatly in one place. However, as one advances in one's Access prowess, one finds that this new field types causes a few problems. One problem is that the size of your DB can grow significantly if you store a lot of documents in your Attachment fields. The second problem is that using an Attachment field isn't quite as straightforward as working with a regular text or number field. However, one thing that many users use Attachment field for is to display images on their forms.

This article provides an alternate method for displaying images associated with a record.

Let's say you have a Form frmCoolCars. Each record in your main Table tblCars has an image of the car associated with that record. Rather than uploading your image to the DB, you can save that image to a common location. For example, C:\Users\twinnyfo\Documents\DB\Cars\Images\. Create an additional text field in your table called CarImage. In that field, eneter the pull path and filename of your image: C:\Users\twinnyfo\Documents\DB\Cars\Images\Ferarri .jpg.

Now, just add an image control to your form (imgCar). Set the .PictureType Property to Linked, but make sure you do not include anything for the .Picture property. This comes later.

Now, in the OnCurrent Event of your Form, add the following code;

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. On Error GoTo EH
  3.  
  4.     Me.imgCar.Picture = Me.CarImage
  5.  
  6.     Exit Sub
  7. EH:
  8.     MsgBox "There was an error going to the current record!" & vbCrLf & vbCrLf & _
  9.         "Error: " & Err.Number & vbCrLf & _
  10.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  11.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  12.     Exit Sub
  13. End Sub
Nothing "rocket sciency" about this one, but just very useful.

Keep in mind that you're probably gonna wanna double-check a few things, like making sure the image exists, before you set the image file. You can also add code in your form to browse for image files and save that location/filename, and there would even be possibilities for adding multiple images for one record. BUT, this article is just a quickie on the basics--just in case you ever wanted to do something like this, so all the bells and whistles have been removed.
Jul 3 '18 #1
Share this Article
Share on Google+
4 Comments


zmbd
Expert Mod 5K+
P: 5,279
Piggybacking from twinnyfo's article

This type of question comes up very often:
(One such thread)

One of my favorite ways of doing this is through a custom function that pulls the image location and feeds it into the form or document. I do this for my class roster - take a picture, store it on the drive, and the attendance form shows the student's mugshot - handy for the subs!
TO make this work, insert the image control and bind the control source to the function. Voila!


=fncPhotoIdLoc([ctrl_people_photo],"photoid")
[ctrl_people_photo] = the control name containing the image file's name
"photoid" = the directory where the images are located
The following code should clear this up...

This method also works very well in reports!

This is some of my older code - not as elegant as the stuff I write today; however, it works! By default I have an image directory under the directory the database is opened from... simple to modify it to use a table field and a dialog box to pull the photo location...
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function fncPhotoIdLoc(z_in_ctrlwfilename As Control, z_in_folderwphotos As String)
  5.     '
  6.     'I use the function directly in the control as the control source for text
  7.     'set the image control as linked and set the picture to this function.
  8.     'seems to work for functions and reports.
  9.     '=fncPhotoIdLoc([people_photo],"photoid")
  10.     '
  11.     Dim z_folderwphotos As String
  12.     Dim z_int As Integer
  13.     Dim z_ext As String
  14.     Dim z_currentdb_path As String
  15.     Dim z_photoid_path As String
  16.     Dim z_photoid_full As String
  17.     '
  18.     'error trap
  19.     On Error GoTo z_errortrap
  20.     '
  21.     'Get the current database - we'll need this for error trap
  22.     z_currentdb_path = CurrentProject.Path
  23.     '
  24.     'check for null/missing or wrong file type
  25.     If (z_in_ctrlwfilename.Value + "") = "" Then
  26.         Err.Raise 5
  27.     Else
  28.         '
  29.         'find out if the correct file extensions are being referenced
  30.         z_int = InStr(z_in_ctrlwfilename.Value, ".")
  31.         z_ext = Mid(z_in_ctrlwfilename.Value, z_int)
  32.         Select Case LCase(z_ext)
  33.             Case ".tif", ".tiff", ".gif", ".jpeg", ".jpg", ".png", ".bmp"
  34.                 '
  35.                 'good value - so do nothing.
  36.                 'Why use a select-case... because I dislike a ton of and/or's in if-then statements
  37.                 '
  38.             Case Else
  39.                 Err.Raise 5
  40.         End Select
  41.     '
  42.     End If
  43.     '
  44.     If (z_in_folderwphotos + "") = "" Then
  45.         Err.Raise 5
  46.     Else
  47.         z_folderwphotos = z_in_folderwphotos
  48.     End If
  49.     '
  50.     'do the location
  51.     z_photoid_path = z_currentdb_path & "\" & z_folderwphotos & "\"
  52.     '
  53.     'final path
  54.     z_photoid_full = z_photoid_path & z_in_ctrlwfilename
  55.     '
  56.     'determine if the file is available otherwise toss error
  57.     If Len(Dir(z_photoid_full)) = 0 Then Err.Raise 5
  58.     '
  59.     'return the path for the image
  60. z_resume_from_error:
  61.     fncPhotoIdLoc = z_photoid_full
  62. Exit Function
  63. z_errortrap:
  64.     '
  65.     'there's an error so set the value to my default error image
  66.     'Debug.Print Err.Number & " - " & Err.Description
  67.     z_photoid_full = z_currentdb_path & "\imgerror\_missing.jpg"
  68.     Resume z_resume_from_error
  69. End Function
and of course... the example database... it's currently in Access2013 - I do have an Acc2003 version if needed, the older versions are no longer supported - sigh
Attached Images
File Type: jpg FormDesignView.JPG (108.1 KB, 66 views)
Attached Files
File Type: zip bytesthread_950020_pictures_in_reports.zip (556.4 KB, 18 views)
Aug 1 '18 #2

PhilOfWalton
Expert 100+
P: 1,290
As a matter of interest, there used to be a problem with images flickering as they loaded. Whether this was due to a much earlier version of Access/Windows, or slower processors, I know not.

What you had to do was change the registry settings. I believe there are a number of possible registry keys in Windows 10 including
Expand|Select|Wrap|Line Numbers
  1. HKEY_CURRENT_USER\Software\Microsoft\Shared Tools\Graphics Filters\Import\PICT\Options
  2.  
Expand|Select|Wrap|Line Numbers
  1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\REGISTRY\MACHINE\Software\Wow6432Node\Microsoft\Shared Tools\Graphics Filters\Import\JPEG\Options
  2.  
Expand|Select|Wrap|Line Numbers
  1. HKEY_USERS\S-1-5-21-210287491-3005076100-493087875-1001\Software\Microsoft\Shared Tools\Graphics Filters\Import\PICT\Options
  2.  
They all end with ShowFileDialog, and if you can find the correct one, that needs to be set to "No"

Phil
Aug 1 '18 #3

zmbd
Expert Mod 5K+
P: 5,279
NB: Editing the registry is not for the novice, slight errors can render the PC unbootable to windows and beyond a novice's ability to repair.

1st thing to try if there are image flickering is to go into the image control's properties and set the tab stop to NO. Then reorder the Form's tab stops so that the image control is at the bottom of the series... at least that worked for me in Acc97 and newer.

There are a lot of reasons for the potential flickering... even tab controls
Allen Browne: Flicker with tab controls
Aug 1 '18 #4

PhilOfWalton
Expert 100+
P: 1,290
As I said, I am going back a long way, certainly to 32 bit computers and Windows 95 or windows 2000.

Yes, I certainly agree that one should be extremely wary of editing the registry, but in this case the clue is in the name "ShowProgressDialog".

I can't remember what happened, whether if set to "Yes", the picture revealed itself in stages, or whether some sort of timer was shown. Anyway, changing the setting to "No" meant that the whole picture appeared at once, although there was a small delay.

Phil
Aug 2 '18 #5