Solved

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

Posted on 2011-03-10
9
400 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 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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 35095415
oops, sorry Scott did not refresh.. :-)
0
 
LVL 84
ID: 35095584
Not a problem cap ... GMTA :)
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2016 Debugging 7 42
Run Time Error 3075 15 43
ms/access hyperlink/ftp 7 34
Update Access FrontEnd by Version # 9 4
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

914 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now