Solved

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

Posted on 2011-03-10
9
404 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 84
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 84
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 84
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

752 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