Solved

Change Photo File Name in Directory using MS Access Field Name from Form

Posted on 2011-03-10
9
405 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:jmar6729
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 85
ID: 35095350
You can use the Name method in VBA to rename your file:

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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35095403
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

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35095415
oops, sorry Scott did not refresh.. :-)
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 85
ID: 35095584
Not a problem cap ... GMTA :)
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 35096849
OK

Filesystemobject is your friend.
It's hard to find in the VBA help -- but it rocks, and it's exactly what you need
Type in 'Filesystemobject' in the help search and it's the last entry in the Access 2003 help.
It's the go to object for all VBA file system manipulation -- create files, folders, rename them, count them, delete them -- everything


Private Sub PhotoNumber_AfterUpdate()

'filesystemobject is always an object
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")

Dim oldname As String
Dim newname As String

if fs.FileExists(me.Photopath & "\" & me.photonumber) 'does the photonumber have the .jpg on it already?
'if not, comment the line above and uncomment the one below
'if fs.FileExists(me.PhotoPath & "\" & me.PhotoNumber & ".jpg")

    'change the filename
    'do that through a copy rather than a rename
    oldname = Me.PhotoPath & Me.PhotoNumber 'append the .jpg if necessary
    newname = Me.PhotoPath & Me.PhotoName.Value & ".jpg"

    'MsgBox newname
    'MsgBox oldname
    'set a breakpoint below and uncomment the Msgboxes above to test that your strings are right

    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
    exit sub 'file did not exist
end if

Open in new window

0
 

Author Closing Comment

by:jmar6729
ID: 35114615
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.FileSystemObject")

Dim oldname As String
Dim Photoname As String
Dim PhotoNumber As String

If fs.FileExists(Me.photopath & Me.PhotoNumber & ".jpg") Then

    '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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35114654
Right on.
Glad it played nice.
FileSystemObject works throughout VBA and VBScript too.
Handy thing, once you get the hang of it

Nick67
0
 
LVL 85
ID: 35115594
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35116818
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
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question