jmar6729
asked on
Change Photo File Name in Directory using MS Access Field Name from Form
I have an MS Access form that includes 4 fields I need to use for a task.
[photopath]
[photoname]
[photonumber]
[photolink]
When the user enters the [photonumber], the [photonumber] is added to the [photoname] and also the [photolink], creating a hyperlink for the photo.
At this point the photo is in a directory and named by the [photonumber] only. I need code to look in the directory defined by [photopath] for the photo defined by [photonumber], then rename the photo to = [photoname].
I imagine this will use VBA code in the afterupdate event of the [photonumber] field.
[photopath]
[photoname]
[photonumber]
[photolink]
When the user enters the [photonumber], the [photonumber] is added to the [photoname] and also the [photolink], creating a hyperlink for the photo.
At this point the photo is in a directory and named by the [photonumber] only. I need code to look in the directory defined by [photopath] for the photo defined by [photonumber], then rename the photo to = [photoname].
I imagine this will use VBA code in the afterupdate event of the [photonumber] field.
you can use the Dir() function to check for file
dim sPhoto as string
sPhotodir(photopath & "\" & photonumber & ".jpg")
if sPhoto<> "" then
name photopath & "\" & sPhoto, photopath & "\" & photoname & ".jpg"
end if
dim sPhoto as string
sPhotodir(photopath & "\" & photonumber & ".jpg")
if sPhoto<> "" then
name photopath & "\" & sPhoto, photopath & "\" & photoname & ".jpg"
end if
oops, sorry Scott did not refresh.. :-)
Not a problem cap ... GMTA :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The code worked great. I just made a few changes. thanks.
Private Sub PhotoNumber_AfterUpdate()
'filesystemobject is always an object
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
Dim oldname As String
Dim Photoname As String
Dim PhotoNumber As String
If fs.FileExists(Me.photopath
'change the filename
'do that through a copy rather than a rename
oldname = Me.photopath & Me.PhotoNumber & ".jpg"
newname = Me.photopath & tool & " " & Me.PhotoNumber.Value & ".jpg"
intResponse = MsgBox("You will rename the picture " & oldname & " to " & newname & " Correct?", vbYesNoCancel, "Change Photo File Name?")
If intResponse = vbYes Then
fs.CopyFile oldname, newname 'copy the old file to the new name
'delete the original at this point
fs.DeleteFile oldname, True 'whack the old file, even if it is read-only
'requery the form if necessary
Me.Requery
Else
End
End If
Else
Exit Sub 'file did not exist
End If
End Sub
Private Sub PhotoNumber_AfterUpdate()
'filesystemobject is always an object
Dim fs As Object
Set fs = CreateObject("Scripting.Fi
Dim oldname As String
Dim Photoname As String
Dim PhotoNumber As String
If fs.FileExists(Me.photopath
'change the filename
'do that through a copy rather than a rename
oldname = Me.photopath & Me.PhotoNumber & ".jpg"
newname = Me.photopath & tool & " " & Me.PhotoNumber.Value & ".jpg"
intResponse = MsgBox("You will rename the picture " & oldname & " to " & newname & " Correct?", vbYesNoCancel, "Change Photo File Name?")
If intResponse = vbYes Then
fs.CopyFile oldname, newname 'copy the old file to the new name
'delete the original at this point
fs.DeleteFile oldname, True 'whack the old file, even if it is read-only
'requery the form if necessary
Me.Requery
Else
End
End If
Else
Exit Sub 'file did not exist
End If
End Sub
Right on.
Glad it played nice.
FileSystemObject works throughout VBA and VBScript too.
Handy thing, once you get the hang of it
Nick67
Glad it played nice.
FileSystemObject works throughout VBA and VBScript too.
Handy thing, once you get the hang of it
Nick67
Glad you got your issue sorted, but be aware that IF this is a distributed application, many corporate network techs lock down scripting (i.e. the FSO), which could give you troubles. Also, many anti-virus and anti-malware utilities will detect the use of the FSO and cause you issues as well.
I can't speak to others' experience. Desktop scripting can certainly be locked down, and make any use of VBScript impossible. FileSystemObject run in VBA runs with the Office app's security context -- which usually doesn't make for problems. With my deployment VBScript, I had to be careful how I used Shell, because with the wrong syntax, it attempted to run in the wrong context, and failed on Win7.
If you use script blocking -- which you mustn't be, since it played for you -- you may have problems.
YMMV -- and thanks to @LSMConsulting for pointing that out :)
Nick67
If you use script blocking -- which you mustn't be, since it played for you -- you may have problems.
YMMV -- and thanks to @LSMConsulting for pointing that out :)
Nick67
Name "Source File" As "Destination File"
So if your Path is stored in [PhotoPath]:
Name Me.PhotoPath & "\" & PhotoNumber & ".jpg" As Me.PhotoPath & "\" & PhotoName & ".jpg"
Note I've assumed that the file format is "jpg". If not, change that as needed.
You could run this in the AfterUpdate event. I'd first suggest checking to insure the image is there:
If Dir(Me.PhotoPath & "\" & PhotoNumber & ".jpg") = "" Then
Name Me.PhotoPath & "\" & PhotoNumber & ".jpg" As Me.PhotoPath & "\" & PhotoName & ".jpg"
Else
Msgbox "Couldn't find photo"
End If