Copy and rename image files and return the relativepath of the images and back-end file

[josephbupe]

Hi.

From the code below, I want to programmatically copy and rename the files I am adding into the table tblObjects to be same as the primary key which is ObjectID, i.e. /Images/1.jpeg etc and then return the relative filepath containing all the images to be in a folder called "Images" within the current project folder.

Currently, I cant even populate the table; When i select a file through the file dialog to populate the table, i am getting this error:

error 75 in cmdBrowse_Click procedure: path/file access error.

Below is the code:

Private Sub cmdBrowse_Click()
On Error GoTo Err_Handler

Dim PathStrg As String
Dim relativePath As String
Dim dbPath As String
'Code compatible with both 32-bit & 64-bit Office
' Set options for the file dialog box.
 Dim F As FileDialog
Set F = Application.FileDialog(msoFileDialogFilePicker)
F.Title = "Locate the image file folder and click on 'Open' to select it"

' Clear out any current filters, and add our own
F.Filters.Clear
F.Filters.Add "Image files", "*.jpg;*.jpeg"

' Set the start folder. Open in default file folder if blank
F.InitialFileName = Nz(Application.CurrentProject.Path & "\Images\",   "C:\") 'modify this as appropriate

' Call the Open dialog procedure.
F.Show

' setup new file name and appropriate DB subfolder
      relativePath = "\Images\" & Me.txtObjectID & ".jpg"
      dbPath = Application.CurrentProject.Path
    
    'copy selected file with new name and subfolder
      FileCopy LCase(PathStrg), dbPath & relativePath
    
    'update the table field with the new file name and relative location
      Me!ImagePath.Value = relativePath
    'display the image from the subfolder of the DB
      Me.Requery

  Exit_Handler:
  Exit Sub

  Err_Handler:
If Err <> 5 Then 'err=5 user cancelled
    MsgBox "Error " & Err.Number & " in cmdBrowse_Click procedure: " & Err.Description
End If
Resume Exit_Handler

End Sub

 

[theDBguy]

Hi. I am thinking either Debug.Print the file path or step through the code and examine the variable content to see exactly why you're getting that error.

 

[josephbupe]

Hi. I am thinking either Debug.Print the file path or step through the code and examine the variable content to see exactly why you're getting that error.

Actually, the error is pointing me to this part of the code:

'copy selected file with new name and subfolder
FileCopy LCase(PathStrg), dbPath & relativePath

 

[theDBguy]

Actually, the error is pointing me to this part of the code:

And what is inside those variables? Make sure they are valid paths.

 

[josephbupe]

And what is inside those variables? Make sure they are valid paths.

OK, please, I have attached the databse file for expert perusal, if you don't mind to look. Sorry i am a total amateur.

 

[theDBguy]

OK, please, I have attached the databse file for expert perusal, if you don't mind to look. Sorry i am a total amateur.

Hi. Okay, I ran your code and here's the deal. Take a look at the following screenshot.

In arrow #1, you declared the variable you used in the FileCopy code (the highlighted line), but you never populated it with any value. That's why in arrow #3, you can see the value of the variable (when you hover your mouse over it) is an empty string ("").

Also, you used the FileDialog object but never really assigned the filepath the user picked from the file browser to any variable. That's probably because you commented that line out as shown in arrow #2.

Hope that helps...

 

[josephbupe]

Ok that has been corrected and now images can be uploaded,

What i need now is to only populate the table with a relative path contaning the images, not the full path. Right now i can only scratch my head wondering how to achieve that.

 

[theDBguy]

Ok that has been corrected and now images can be uploaded,

What i need now is to only populate the table with a relative path contaning the images, not the full path. Right now i can only scratch my head wondering how to achieve that.

Relative (path) to what? Please show us some examples.

 

[josephbupe]

Relative (path) to what? Please show us some examples.

Ah, sorry for not including more information earlier. I meant to say that the database would be mobile on a thumb drive, and so I would like to have all its folders and files in them to stay linked to the front-end within the same folder. The first time i tried to move the database on a thumb drive away from the PC where I had populated it with about 200 records with images, i couldn't link back the images because i had populate the full path to the images in the table.

I think instead of E:\WORKS OF ART DATABASE\Images\_600.jpg, it should be something like \Images\_600.jpg

I just don't know how to achieve that.

 

[bastanu]

Add the following function to a standard module and use it in a update query to update the field to just the file name (i.e. "_600.jpg"):

Public Function StripLast (path As String) As String
    Dim x As Long
    Dim y As Long
    y = Len(path) + 1
    x = 1
    x = InStr(x, path, "\", vbDatabaseCompare)
    Do While x > 0
        y = x
        x = InStr(x + 1, path, "\")
    Loop
    StripLast = Right(path, Len(path) - y)
End Function

Then code your forms\reports, etc. to use your relative path using CurrentProject.Path

Cheers,

 

[josephbupe]

Add the following function to a standard module and use it in a update query to update the field to just the file name (i.e. "_600.jpg"):

Public Function StripLast (path As String) As String
    Dim x As Long
    Dim y As Long
    y = Len(path) + 1
    x = 1
    x = InStr(x, path, "\", vbDatabaseCompare)
    Do While x > 0
        y = x
        x = InStr(x + 1, path, "\")
    Loop
    StripLast = Right(path, Len(path) - y)
End Function

Then code your forms\reports, etc. to use your relative path using CurrentProject.Path

Cheers,

Hello bastanu.

Thank you for your advise and the module you just posted for me. However, let me confess to you that I have limited knowledge of VB programming.

Please, see the attached copy of the the accdb file for your perusal.

 

[theDBguy]

I just don't know how to achieve that.

I think you do. You're already doing it.

relativePath = "\Images\" & Me.txtObjectID & ".jpg"

 

[bastanu]

Exactly, all you need is to use the function I provided to extract the file name only from the full name (and path).
Here is an updated sample with the update query.

Cheers,