Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-10
9
Medium Priority
?
407 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Accepted Solution

by:
Nick67 earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

782 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