Add photo/Delete Photo

[mtagliaferri]

I have a Contacts form whit a image box containing the photo which is linked to the table (Pic 1) and it works perfectly, I am trying to go the extra mile by adding two cmd buttons to add or delete the photo, I have e done some research but have miserably failed, I am aiming to open the browse window when clicking on the Add button which will open a specific folder within the users documents.
Any suggestions?

 

[cyanidem]

Something I use in my current project (mind it's not my own procedure, I found it somewhere):

Private Sub btAddPhoto_Click()
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
   Dim strPath As String
On Error GoTo errHandler
 
 
   ' Set up the File Dialog.
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
    With fDialog
 
      ' Allow user to make multiple selections in dialog box (or not)
        fDialog.AllowMultiSelect = False
 
      ' Set the title of the dialog box.
        fDialog.Title = "Select photo"
 
      ' Clear out the current filters, and add our own.
        fDialog.Filters.Clear
        fDialog.Filters.Add "JPEG Pictures", "*.jpg"
        fDialog.Filters.Add "BMP Pictures", "*.bmp"
        fDialog.Filters.Add "PNG Pictures", "*.png"
        fDialog.Filters.Add "All files", "*.*"
        fDialog.ButtonName = "Select"
        fDialog.InitialView = msoFileDialogViewLargeIcons
        fDialog.InitialFileName = CurrentProject.Path & "\Pics"   'or select your own initial folder
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
        If fDialog.Show = True Then
            'Loop through each file selected and add it to our list box.
            strPath = Trim(fDialog.SelectedItems(1)
            Pic1.Picture = strPath
            'and now you need to update Photo field in your table with strPath
        Else
            MsgBox "You clicked Cancel in the file dialog box."
        End If
    End With
errHandler:
    If (Err.Number = USER_CANC) Then ' User cancelled action.
        Resume Next
    Else
        Exit Sub
    End If
End Sub
 
End Sub

You will also need to declare constant:

Private Const USER_CANC As Long = 2501

and include Microsoft Office 14.0 Object Library in references (or different version, I use Access 2010)

As for deleting photo it's just replacing value in this field with "" and changing .Picture property to null I guess.

 

[mtagliaferri]

I have copied your code, but I get this error!

 

[AccessBlaster]

Did you set this reference Microsoft Office 14.0 Object Library or 15?

 

[mtagliaferri]

I have 14 enabled!

 

[cyanidem]

No, you don't.
Look again on the name of this library:
Microsoft Office 14.0 Object Library
and you have:
Microsoft Access 14.0 Object Library

 

[mtagliaferri]

I am confused!!! I have now added Microsoft Office 15.0 Object Library (Reference 1), but now I get this other error (Reference 2)

 

[cyanidem]

You are missing closing bracket, this line should look like:

strPath = Trim(fDialog.SelectedItems(1))

I know, it wasn't in code I pasted, I just cut off too much

 

[mtagliaferri]

Right, I am slowly getting closer to the solution, the cmdButton seems to be working as it opens the browsing window, it allows me to select a picture and then closes the window however it does not write the file path in the Photo field of the record therefore nothing is displayed!

 

[cyanidem]

        If fDialog.Show = True Then
            'Loop through each file selected and add it to our list box.
            strPath = Trim(fDialog.SelectedItems(1)
            Pic1.Picture = strPath
            [COLOR=red]'and now you need to update Photo field in your table with strPath[/COLOR]
        Else
            MsgBox "You clicked Cancel in the file dialog box."
        End If

There's part marked in red which you need to adjust to your needs.

 

[mtagliaferri]

I am really sorry, my knowledge in VAB is very limited to what I am learning with the help of you all. I have done some research on the forum and I have not found the correct code I am suppose to write after

Pic1.Picture = strPath

My table name is tblCrewMemeber and the field is "Photo"
Can you give me more help please!:banghead:

 

[mtagliaferri]

I am still struggling with this....I have done some research and the best result I have found is this

Pic1.Picture = strPath = ("tblCrewMember", "Photo")

but clearly not working.
I understand that the command will wright the path of the photo in the table tblCrewMember in the specific field photo, but not entirely sure what thes VBA commands are :-(

 

[cyanidem]

Ok,
think - how do you update rest of fields of the form? You need to do exactly the same with the field that contains path to the selected photo. Path is returned by variable strPath. So basically you need to set field (let's say its name is Pic1) value to strPath.

myForm.Pic1=strPath

Then you'll probably want to see that image on the form, so you need to set image control .Picture value to the value from the field Pic1 (or from strPath, whatever)

Image1.Picture=myForm.Pic1

That very simplified explanation but you can work from that, unless you just need all the code written for you and in that case I can't help, sorry. I'm not that advanced in VBA either

 

[mtagliaferri]

After long twisting and twitching I have managed (with the help of you all!!!) to solve my issue to some degree...but there is something not quite right...Once I select the record I get this message "You are update one row(s) Once you click Yes, you can't use the undo command to reverse the changes. Are you sure you want to update these records?" That is very kind of access to warn me about it, but I don't really want to see that message...I just want Access to do it quickly and with no fuss...so how do I get rid of that silly message?
Another strange thing is that once I have told access that I am happy to accept that change the picture is not updated promptly but after 30/45 seconds.....

Private Sub CmdAddPhoto_Click()

On Error GoTo errHandler
 
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
 
    With fDialog
       
        fDialog.AllowMultiSelect = False
        fDialog.Title = "Select photo"
        fDialog.Filters.Clear
        fDialog.Filters.Add "JPEG Pictures", "*.jpg"
        fDialog.Filters.Add "BMP Pictures", "*.bmp"
        fDialog.Filters.Add "PNG Pictures", "*.png"
        fDialog.Filters.Add "All files", "*.*"
        fDialog.ButtonName = "Select"
        fDialog.InitialView = msoFileDialogViewLargeIcons
        fDialog.InitialFileName = CurrentProject.Path & "\Pics"
        If fDialog.Show = True Then
        strPath = Trim(fDialog.SelectedItems(1))
        DoCmd.RunSQL "update tblCrewMember set Picture = '" & strPath & "' where IDCrewMember = " & CInt(Me.IDCrewMember.Value)
        
        Else
        MsgBox "You clicked Cancel in the file dialog box."
        End If
    End With
errHandler:
    If (Err.Number = USER_CANC) Then
        Resume Next
    Else
        Exit Sub
    End If
End Sub

 

[mtagliaferri]

I have looked around for a better solution to this but no suucess

DoCmd.RunSQL "update tblCrewMember set Picture = '" & strPath & "' where IDCrewMember = " & CInt(Me.IDCrewMember.Value)

any suggestions?

 

[mtagliaferri]

I have found the way round to remove those annoying messages by going under Access->Options->Client Settings->Confirm and untick all 3 options "Record Changes","Document Deletions","Action Queries" See Pic.
I have still not found the way to show the updated photo on the form immediately, this will tale at least 30 seconds, but I have noted that if I press the Tab button or I click on any field of the form the form updates.
I believe I have to set some command to update the form immediately after the file/photo has been selected and the "Browse Window" has been closed!
Any indication?