Extract and Save File Path

[jpb0880]

This Thread was split from:
https://bytes.com/topic/access/answe...re-access-form


Hello ADezii,

I am trying to use the cool button you created in a form on my database. I have a place for 6 images and need a button for each. I am not well versed in access and need some help. I can get the form file you posted great but when I copy the code over to the copied button I must be leaving something undone.

Sorry for the crude explanation. Thanks in advance for any help you may have. I can send my form over if necessary.

John

[jpb0880]

Hello ADezii,

I found another one of your excellent articles "Need "Browse for File" Button. I followed your instructions on how to set the reference to the Microsoft Office Object Library. It worked!! I just changed the imageXX to match where I wanted the photo and it popped right in. However when I close the form and reopen its gone. I need to find a way for it to put the path of the photo in the table to save it.

I have 6 image boxes on my form and each one has the control source linked to a text field in a table. I have created six of your buttons and edited the code for each button to pull and load the pictures for each associated image box. I just need to know how to get it to post the path in the text field in the table so that the photos will show up when the form is closed and reopened. I was trying to avoid the embedding process and linking to a folder with the photos.

I also need a delete button to remove the image in case of a mistake or no image is desired for one of the image boxes.

[ADezii]

I need to find a way for it to put the path of the photo in the table to save it.

1. The Absolute PATH to the Graphic File is stored in .SelectedItems(1). After the Image is displayed in the Image Control you need to write this Value to a Field in the Table to which your Form is bound.
   

   'Code intentionally omitted
   If .Show Then
               'Can due this because AllowMultiSelect = False
               Me![Image1].Picture = .SelectedItems(1)
                 CurrentDb.Execute "UPDATE tblPictures SET PicPath = '" & .SelectedItems(1) & "' WHERE [ID] = " & _
                                    Me![ID], dbFailOnError
             End If
   'Code intentionally omitted

2. In the Current() Event of the Form you need to Load the Graphic into the Image Control using LoadPicture() based on a Unique [ID] Field for each Record.
   

   Private Sub Form_Current()
     If Not Me.NewRecord Then
       Me![Image1].Picture = LoadPicture(DLookup("PicPath", "tblPictures", "[ID] = " & Me![ID]))
     End If
   End Sub

P.S. - The above Code Blocks are 'Air Code' only and has not been tested.

[jpb0880]

Hello,

Thanks very much for looking at my question. I am afraid that my skills are so poor that I don't quite know where to past the code. I need to study and keep trying to see if I can get the info into the correct place.

I went to the form properties and created an on current - event procedure and pasted the code you included for part #2. I need to understand how to enter the table name and field identifier.

I don't have a clue where to put the code in item #1?

Thanks!

[ADezii]

I am a little confused as to the exact nature of your request. Kindly Upload a copy of your Database stripped of any sensitive information.

[jpb0880]

Hello ADezii,

Thanks for your reply.

I have attached the database and a few photo files for testing.

Thank you very much for your help and code.

I need to get the picture selected with the browse button under each photo to put in the photo and paste the path into the table.

Kindest Regards!

[ADezii]

This would be the general, oversimplified, idea on how to store the Absolute File Path of a Graphic File for a specified ID. The Code was only applied to the first Image Control. Download the Attachment to get a better idea of what is going on. The Paths to Graphic Files for IDs should actually be stored in a Related/Child Table.

'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim varItem As Variant
 
With Application.FileDialog(msoFileDialogFilePicker)
   With .Filters
     .Clear
     .Add "JPEG Files", "*.jpg"
     .Add "Bitmap Files", "*.bmp"
     .Add "Graphic Interchange Format Files", "*.gif"
   End With
       'The Show Method returns True if 1 or more files are selected
       .AllowMultiSelect = False
       .FilterIndex = 1     'Bitmap files
       .ButtonName = "Select Graphic"
       .InitialFileName = vbNullString
       .InitialView = msoFileDialogViewDetails
       .Title = "Load Graphic File!"
          If .Show Then
            'Can due this because AllowMultiSelect = False
            Me![Image86].Picture = .SelectedItems(1)
            CurrentDb.Execute "UPDATE [BJ's Native American Jewelry Collection] SET [FilePath] = '" & _
                               .SelectedItems(1) & "' WHERE [ID] = " & Me![ID]
              MsgBox "The Filepath " & .SelectedItems(1) & " has been stored in [BJ's Native American Jewelry Collection] for [ID] " & _
                      Me![ID] & "."
          End If
End With

[jpb0880]

Hello ADezzi,

That example was worth a thousand words! It all works great now.

I had to also make a delete button and I used slightly modified your code. Probably the wrong way but it worked to delete the photos.

Private Sub Command57_Click()

CurrentDb.Execute "UPDATE [BJ's Native American Jewelry Collection] SET [Front Photo] = '" & "' WHERE [ID] = " & Me![ID]

Me.Refresh

End Sub

Also what is the advantage to store the graphic file paths in a Related/Child Table??

Thanks so much for all your help!

[ADezii]

Also what is the advantage to store the graphic file paths in a Related/Child Table??

To Normalize the Data, eliminate Repeated Fields, and to eliminate Redundancy. Certain Fields in your Table would be repeated for every Graphic File PATH stored which is not very efficient and definitely violates the concept of Normalization. When I get a chance, I will create a Normalized Version of your DB for you, but it will not be for a few days. Good Luck with your Project.

[jpb0880]

Wow! Thanks!

Did the code I used above look OK for the delete button? It seems to work but I don't want to have any cause any problems down the line.

Looks like snow is on the way for us both again! I am in Maryland.