Access VBA fso.copyfile

[accessvbahelp]

I am very new to VBA... like never wrote code before new and I need some help.
We have a huge database in access where we list our part numbers and the current revision and more information and this is what we want to be able to do

When you click the approve button the current file will be copied from its current location and placed in a new location and the current file will be deleted. The location of the current file looks like this c:\currentfolder\*multiple folders for the correct file\*dwf
We have a lot of part numbers and they are seperated in folders by the first three digits. So the path that says *multiple folders for the correct file needs to be found by the part number on the access form.
Can anyone help get me started?

 

[Andrzejek]

No fso needed.
Check your VBA help for [tt]FileCopy[/tt] and [tt]Kill[/tt]

Have fun.

---- Andy

 

[accessvbahelp]

Thank you Andrezejek! I made a few changes and now I get an error message that says "Object variable or With block variable not set.

These are my variables
Dim CurrentFolder As String
Dim NewFolder As String
Dim CurrentFile As String
Dim NewFile As String
Dim FileCopy As Object

What am I missing?

Thank you again!!

 

[accessvbahelp]

Thank you Andrezejek! I made a few changes and now I get an error message that says "Object variable or With block variable not set.

These are my variables
Dim CurrentFolder As String
Dim NewFolder As String
Dim CurrentFile As String
Dim NewFile As String
Dim FileCopy As Object

What am I missing?

Thank you again!!

 

[Andrzejek]

First - you do not have any code (yet) to have errors on. Which line does it point to with the error?

Second - avoid using reserved words as the names of your variables/procedures/database fields/etc. FileCopy is a reserved word, as is Select, If, Name, Date, Time, Loop, Do, and so on...
I would suggest using pre-fixes, like[tt]
Dim strCurrentFolder As String
Dim strNewFolder As String
Dim strCurrentFile As String
Dim strNewFile As String
Dim objFileCopy As Object
[/tt]

Have fun.

---- Andy

 

[accessvbahelp]

I made your suggestions (thank you)and now my error message is "file not found" This is the line that is highlighted

FileCopy CurrentFile, NewFile

this is what it looks like together

Folder = Left(DrawingNumber, 3)
CurrentFolder= "Dbook\currentDWFs\" & Folder 
NewFolder= "dbook\newDWFs\" & Folder 
CurrentFile= CurrentFolder & "\" & DwgNumb & ".dwf"
NewFile= NewFolder & "\" & DwgNumb & ".dwf"
FileCopy CurrentFile, NewFile

 

[Darrylles]

access,

Just because you've labelled a variable as 'CurrentFolder' does not mean that VB magically get's it for you.

Your 'CurrentFolder' variable must define the FULL path (including drive or server path) e.g:

"C:\Dbook\currentDWFs\" & Folder

ATB

Darrylles



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[accessvbahelp]

Darrylles you are correct, I do have the full path defined but for posting purposes I omitted it. Sorry for the confusion. Thank you!

 

[Andrzejek]

Try something like this:

Folder = Left(DrawingNumber, 3)[blue]
Debug.Print "Folder is " & Folder[/blue]
CurrentFolder= "Dbook\currentDWFs\" & Folder )[blue]
Debug.Print "CurrentFolder is " & CurrentFolder[/blue]
NewFolder= "dbook\newDWFs\" & Folder 
[blue]Debug.Print "NewFolder is " & NewFolder[/blue]

CurrentFile= CurrentFolder & "\" & DwgNumb & ".dwf"NewFile= NewFolder & "\" & DwgNumb & ".dwf"FileCopy CurrentFile, NewFile

Etc. And see what you get in Immediate Windows

It does not look like you are pointing to any particular drive or server or anything. So how your compiler is supposed to know where to find those pieces?

Have fun.

---- Andy

 

[accessvbahelp]

Thank you! With your help this works!