Help Getting Photos To update in Access XP

[Jim229]

Please I need some help. I have been working to duplicate a function that works great in the Sample DB Northwind.mdb which is included in the MS Office XP installation.

The function I need is the Photo function that allows for adding and updating the Employee photo in the Employee Form. I have tried copying the code in the functions and Modules section and duplicate the buttons (I used Different names but adjusted the code accordingly). After several hours of getting the error "Variable Undefined" for the argument (msoFileDialogFilePicker) in the "Sub getFileName()" section I found that problem and corrected for it.

Now the code shows unsupported function or action for the "If",Marked in Bold and Underlined in this post, statement just below that and when I comment that section out the code runs and it shows me the Access File Open window in the correct Directory but no matter what I do I can't select a file by clicking or double clicking or even typing the file name in. I can right click the image frame and select the correct picture using the function in the drop down list but that is the only way I can do it and I can't depend on the users who will be using it to do this process correctly.

Here is the code I have so far:

Module 1:

Option Compare Database
Option Explicit

Public Function DisplayImage(ctlImageControl As Control, strImagePath As Variant) As String
On Error GoTo Err_DisplayImage

Dim strResult As String
Dim strDatabasePath As String
Dim intSlashLocation As Integer

With ctlImageControl
    If IsNull(strImagePath) Then
        .Visible = False
        strResult = "No image name specified."
    Else
        If InStr(1, strImagePath, "\") = 0 Then
            ' Path is relative
            strDatabasePath = CurrentProject.FullName
            intSlashLocation = InStrRev(strDatabasePath, "\", Len(strDatabasePath))
            strDatabasePath = Left(strDatabasePath, intSlashLocation)
            strImagePath = strDatabasePath & strImagePath
        End If
        .Visible = True
        .Picture = strImagePath
        strResult = "Image found and displayed."
    End If
End With
    
Exit_DisplayImage:
    DisplayImage = strResult
    Exit Function

Err_DisplayImage:
    Select Case Err.Number
        Case 2220       ' Can't find the picture.
            ctlImageControl.Visible = False
            strResult = "Can't find image in the specified name."
            Resume Exit_DisplayImage:
        Case Else       ' Some other error.
            MsgBox Err.Number & " " & Err.Description
            strResult = "An error occurred displaying image."
            Resume Exit_DisplayImage:
    End Select
End Function

form New Employee Functions:

Option Compare Database
Option Explicit
Dim path As String

Private Sub Save_Click()
On Error GoTo Err_Save_Click
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
    
End Sub

Private Sub AddPicture_Click()
    ' Use the Office File Open dialog to get a file name to use
    ' as an employee picture.
    getFileName
End Sub

Private Sub Form_RecordExit(Cancel As Integer)
    ' Hide the errormsg label to reduce flashing when navigating
    ' between records.
    errormsg.Visible = False
End Sub

Private Sub RemovePicture_Click()
    ' Clear the file name for the employee record and display the
    ' errormsg label.
    Me![ImagePath] = ""
    hideImageFrame
    errormsg.Visible = True
End Sub

Private Sub Form_AfterUpdate()
    ' Requery the ReportsTo combo box after a record has been changed.
    ' Then, either show the errormsg label if no file name exists for
    ' the employee record or display the image if there is a file name that
    ' exists.
    Me!ReportsTo.Requery
    On Error Resume Next
        showErrorMessage
        showImageFrame
        If (IsRelative(Me!ImagePath) = True) Then
            Me![ImageFrame].Picture = path & Me![ImagePath]
        Else
            Me![ImageFrame].Picture = Me![ImagePath]
        End If
End Sub

Private Sub ImagePath_AfterUpdate()
    ' After selecting an image for the employee, display it.
    On Error Resume Next
        showErrorMessage
        showImageFrame
        If (IsRelative(Me!ImagePath) = True) Then
            Me![ImageFrame].Picture = path & Me![ImagePath]
        Else
            Me![ImageFrame].Picture = Me![ImagePath]
        End If
End Sub
Private Sub Form_Current()
    ' Display the picture for the current employee record if the image
    ' exists.  If the file name no longer exists or the file name was blank
    ' for the current employee, set the errormsg label caption to the
    ' appropriate message.
    Dim res As Boolean
    Dim fName As String
    
    path = CurrentProject.path
    On Error Resume Next
        errormsg.Visible = False
        If Not IsNull(Me!Photo) Then
            res = IsRelative(Me!Photo)
            fName = Me![ImagePath]
            If (res = True) Then
                fName = path & "\" & fName
            End If
            
            Me![ImageFrame].Picture = fName
            showImageFrame
            Me.PaintPalette = Me![ImageFrame].ObjectPalette
            If (Me![ImageFrame].Picture <> fName) Then
                hideImageFrame
                errormsg.Caption = "Picture not found"
                errormsg.Visible = True
            End If
        Else
            hideImageFrame
            errormsg.Caption = "Click Add/Change to add picture"
            errormsg.Visible = True
        End If

End Sub

Sub getFileName()
    ' Displays the Office File Open dialog to choose a file name
    ' for the current employee record.  If the user selects a file
    ' display it in the image control.
    Dim fileName As String
    Dim result As Integer
    
    With Application.FileDialog(msofileDialogFilePicker)
        .Title = "Select Employee Picture"
        .Filters.Add "All Files", "*.*"
        .Filters.Add "JPEGs", "*.jpg"
        .Filters.Add "Bitmaps", "*.bmp"
        .FilterIndex = 3
        .AllowMultiSelect = False
        .InitialFileName = CurrentProject.path
        result = .Show
[U][B]        If (result <> 0) Then
            fileName = Trim(.SelectedItems.Item(1))
            Me![ImagePath].Visible = True
            Me![ImagePath].SetFocus
            Me![ImagePath].Text = fileName
            Me![FirstName].SetFocus
            Me![ImagePath].Visible = False
        End If[/B][/U]
    End With
End Sub

Sub showErrorMessage()
    ' Display the errormsg label if the image file is not available.
    If Not IsNull(Me!Photo) Then
        errormsg.Visible = False
    Else
        errormsg.Visible = True
    End If
End Sub

Function IsRelative(fName As String) As Boolean
    ' Return false if the file name contains a drive or UNC path
    IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\") = 0)
End Function

Sub hideImageFrame()
    ' Hide the image control
    Me![ImageFrame].Visible = False
End Sub

 

[Uncle Gizmo]

Is [ImagePath] a control on your form?

 

[Jim229]

That was the problem. I posted that a while back and forgot about it. As it turned out the code was correct but the reference on the form wasn't and I hadn't looked at the sample form at the time I made this posting. Once I included the reference in the form as a hidden field everything started working. Thanks for responding.

 

[Simon_MT]

The first thing whilst the Sample Northwind database does something, don't take as the gospel truth. The first thing you need to do is to have one image format: jpeg is as good as any. The image file should be named according to the EmployeeID this can be a AutoNumber or Initials. This way the database will automatically load image.

The picture is an aide memoir and doesn't need any intervention by the user. The image should already be there or the database can find it later.

Simon

 

[Jim229]

I agree with that concept in an Employee database. I was adapting the code for use in something a lot more complicated and needed to be able to address multiple users, some of which are not exactly computer literate. In the case of this particular project I needed to anticipate problems from multiple areas and simplify the process as much as possible.

The final code utilizes the standard Windows File open window that they are all used to and compensates for one user using Jpegs and another using BMP images since it is capable of using all the listed formats in the code. You or I understand that jpeg and jpg are essentially the same, some of the people using this tool do not. since I could not depend on all of them using the same tool and format to download pictures from cameras I had to all for different formats.

In a perfect world I would be able to spend the time training them to use the jpg format and tools for downloading the photos in the correct size format but unfortunately that was not an option in this application and the forms it was used in.

Thanks for the input and the help.

 

[Simon_MT]

I would still suggest that someone takes the responsibly for the capture of images. I have had cases where images were 2Mb and users wondering why there database crashes. If you use images there has to some control of the images that are used.

Simon

 

[Jim229]

I fully agree, unfortunately it's not always an option. I would prefer to have included a file size attribute check even though I'm not storing the image directly in the database. But this was a limitation for me as I am not an expert in Vb programming. I prefer to program in traps and this would have been a relatively easy trap for me to program in C++ or some similar language but with the time constraint I had and limitations put on by the user this was the option I had.

Again, thanks for your reply and input.

 

[DCrake]

How are you currently capturing images now? Are you using a digital camera or are you using a built in/plug and play webcam?

 

[Simon_MT]

If I remember correctly, interogating the ImageControl does not yield the Image File size although you are get the aspect. Dbpix allows both the image aspect and the file size although this is a paid for ActiveX component.

I not this is reiterating the point about images, I am use to lazy users that dump images into directories which bring the whole database to a standstll. Whilst this is not what you want to heard it is better than some standards are used:

500 pixel maximum size this can vary but 1000 pixel size is four times that of a 500 pixel image. Low res images should be used, you can generally get a good enough image at 60% of the original. File sizes to the 500 pixel should be about 50KB.

Simon

Simon

 

[Jim229]

DCrake,

currently as the code works it utilizes the windows open file dialog box to grab the photo and put it in the form and load the location into the database field so it can be loaded as often as needed.

As far a a picture source, I have pictures from cameras coming in and unfortunately no control over which program they use download those into the master photo directory, Still photos from Video feeds that are processed with yet 2 other software tools depending on who is processing at the time and video stills processed from Security camera feeds from 22 Independent locations. The employees processing these photos into the directory for use work for 2 different companies, 1 of which has no intrest in training their people to use these tools and the other has multiple tools for doing the same job and each tool has a different default format for saving images (bmp and jpg are the primary formats I tun into but gif pops up every now and again.) The second company would like to train everyone to use the same program but don't want to pay for it leaving me with a choice to do work for free or leave it to the company to sort out. Their requirement when I was given this task was to be able to process both the primary types and to use the Standard Windows Open File Dialog.

 

[Simon_MT]

Here is a function to update the Image Flag Hieght and Width

Function SetPicture()

Dim FullPath As String
Dim ImageW As Integer
Dim ImageH As Integer

    With CodeContextObject
        FullPath = GetImageDir & .[Image File]
        ImageW = CInt(.[ImageControl].ImageWidth / 15)
        ImageH = CInt(.[ImageControl].ImageHeight / 15)

        If Dir([FullPath]) = Empty And .[Image] = -1 Then
            .[Image] = 0
        ElseIf Dir([FullPath]) <> Empty Then
            If .[Image] = 0 Then
               .[Image] = -1
            End If
            If .[Image Width] <> ImageW Or .[Image Height] <> ImageH Then
                .[Image Width] = ImageW
                .[Image Height] = ImageH
'                .[Image Size] = .[ImageControl].ImageBytes
            End If
        End If
    End With

End Function

Normally I would use dbpix but I beleive this worth. The file size is commented out as this is not supported.

Simon

 

[Jim229]

Thank you for the help. This but of code should simplify the process a great deal.

 

[Max D]

You are making a lot of work here. Sure, its good to play with code, but some people would like to spend time developing DB instead of fighting Access poor imaging.

I'd like to suggest the component we have developed for images, AccessImagine - http://access.bukrek.net
You just need to place it on form and link to picture field - and it will do the rest. Shortly, it saves images as JPEGs, automatically resamples too big ones, manages external image storage (if you need it), you can insert image from file, clipboard, drag-and-drop, get it from scanner or camera.

Many people say its the best thing for Access they have found yet ;-)

 

[Uncle Gizmo]

It looks good D Max, does it work with Webcams?

 

[Max D]

For now it supports TWAIN sources only. Some webcams do, but some need third-party connector (WIA->TWAIN).