On record creation copy a folder and rename top level based on field values

Hi,

I have an access database whereby files will be stored against a project table/form via a hyperlink, i also have a dummy folder plan on a network drive.

What i'm wanting to do is when a record is created on the form to copy the dummy folder plan and rename the top level based on two of the fields from the record and then update the hyperlink to the new path.

e.g. dummy = c:\dummy\....

copy and rename to c:\Record ID\....

update hyperlink field on the record to c:\record id\

Is this possible?

Many thanks

Brendan
eyeisystemsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
my question is how frequent you want to do this?
immediately after a creation of a record or you can have some scheduled task to do the moving?

if a record is deleted, do you want to keep such folder?
eyeisystemsAuthor Commented:
Hi,

Many thanks for replying.

It would be immediately after creation of the record and of the record was deleted the folder and files would need to remain.

Best regards

Brendan
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
btw, is your form bounded to your Table or it's an unbound form?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

eyeisystemsAuthor Commented:
Hi,

Form is bound to table.

Cheers

Brendan
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok, you may try customize this:
(not yet tested as no Access with me right now)

Private Sub Form_AfterInsert() 
   NewID = ID.Value
   SQL = "Update yourTable set hyperlink = 'C:\" & NewID & "\' where ID = " & NewID
   currrentDB.execute SQL
   call CopyFolder "C:\dummy\" , "C:\" & NewID & "\'
End Sub

Private Sub CopyFolder(FromPath As String, ToPath As String)

    Dim FSO As Object

    If Right(FromPath, 1) = "\" Then
        FromPath = Left(FromPath, Len(FromPath) - 1)
    End If

    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If

    FSO.CopyFolder Source:=FromPath, Destination:=ToPath

End Sub

Open in new window

eyeisystemsAuthor Commented:
Hi Ryan,

I get an error on the line
call CopyFolder "C:\dummy\" , "C:\" & NewID & "\'

Open in new window


As ambiguous name detected: Form_AfterInsert

Any ideas?

Cheers

Brendan
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
do you have 2 Private Sub Form_AfterInsert()  in your codes?

and perhaps you can also rename the subroutine and try again.

>>call CopyFolder "C:\dummy\" , "C:\" & NewID & "\'

change to:

call fCopyFolder "C:\dummy\" , "C:\" & NewID & "\'

and then:

Private Sub CopyFolder(FromPath As String, ToPath As String)

to:

Private Sub fCopyFolder(FromPath As String, ToPath As String)
eyeisystemsAuthor Commented:
Hi Ryan,

I get a compile error Expected: end of statement and its highlighting "C:\dummy\"

Presuming a syntax error of some sort?

Cheers

Brendan
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
hi sorry my bad, try:

CopyFolder "C:\dummy\" , "C:\" & NewID & "\"

or

call CopyFolder( "C:\dummy\" , "C:\" & NewID & "\" )

instead...
eyeisystemsAuthor Commented:
OK, new error :) but feels like we're nearly there!

'currrentDb.Execute SQL'  Object Required

Cheers

Brendan
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
omg.., I must be very tired while providing the codes...

try this instead:

currentDb.Execute SQL
eyeisystemsAuthor Commented:
Doh, can't believe i didnt spot that!

Now it says for the same line, too few parameters expected 1.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
this is the full scripts that tested works for me.

Option Compare Database

Private Sub Form_AfterInsert()
   NewID = ID.Value
   SQL = "Update Table1 set hyperlink = 'C:\" & NewID & "\' where ID = " & NewID
   CurrentDb.Execute SQL
   CopyFolder "C:\dummy\", "C:\" & NewID & "\"
   MsgBox "Done! " & Now()
End Sub

Private Sub CopyFolder(FromPath As String, ToPath As String)
    Dim FSO As Object

    If Right(FromPath, 1) = "\" Then
        FromPath = Left(FromPath, Len(FromPath) - 1)
    End If
    
    If Right(ToPath, 1) = "\" Then
        ToPath = Left(ToPath, Len(ToPath) - 1)
    End If
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    If FSO.FolderExists(FromPath) = False Then
        MsgBox FromPath & " doesn't exist"
        Exit Sub
    End If
    If FSO.FolderExists(ToPath) = False Then
        FSO.CreateFolder ToPath
    End If
    
    FSO.CopyFolder Source:=FromPath, Destination:=ToPath
    Set FSO = Nothing
End Sub

Open in new window

Database2.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eyeisystemsAuthor Commented:
Perfect! I should learn to copy and paste better!

Cheers

Brendan
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.