Solved

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

Posted on 2011-03-10
9
399 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

11 Experts available now in Live!

Get 1:1 Help Now