Copying Files Based on Customer ID

[Learn2010]

I have a set up where users have their own front-end linked to a shared SQL back-end. After opening the Customer record, they have a button to click that will transfer data from the SQL DB to a DB on their C: Drive. This data is used at off-site locations. Upon returning to the base, new or edited data is uploaded to the SQL back-end.

One of the issues we have is that we have files stored in one folder on the shared drive that are linked to the Customer records. The users need to be able to view these while off-site. Each file is named with the Customer ID and a brief description. If the Customer ID is 12345, a file may be named 12345 Record1, 12345 Letter1, etc.

Is there a way to copy these, based on the Customer ID, to a folder on their C: Drive? This needs to be part of the procedure that sends the data to the C: Drive. The form with the button has the Customer ID on it.

 

[arnelgp]

Issue:
FileCopy "\\sharedfoldername" & [ID] & "*. *" "c:\foldername"

 

[Learn2010]

I was just now able to get back to this. I appreciate your quick response. This folder is buried and here is a path.

FileCopy "\\F1.F2\F3\F4\F5\F6\F7?" & Forms!frmMain!frmMainSub.Form.frmGetNumber.Number & "*. *" "c:\F8"

I get a message "Compile error Expected: end of statement" and the "c:\F8" is highlighted. Can you tell me what is wrong with this?"

Thank you.

 

[Mark_]

FileCopy "\\F1.F2\F3\F4\F5\F6\F7?" & Forms!frmMain!frmMainSub.Form.frmGetNumber.Number & "*. *" "c:\F8"

should read

FileCopy "\\F1.F2\F3\F4\F5\F6\F7?" & Forms!frmMain!frmMainSub.Form.frmGetNumber.Number & "*. *" & "c:\F8"

Missed the final " & "

 

[Learn2010]

I made the changes and ran it again. I tried some variations of that and ran them several times. It highlights FileCopy and gives the message "Compile error: Argument not optional." Here is what I currently have:

FileCopy "\\F1.F2.org\F3\F4\F5\F6\F7" & Forms!frmMain!frmMainSub.Form!frmGetNumber.Number & "*. *" & "c:\F8\F9"

Thank you.

 

[arnelgp]

Dim strFileName As String
strFileName = Dir("\\F1.F2.org\F3\F4\F5\F6\F7\" & Forms!frmMain!frmMainSub.Form!frmGetNumber.Number & "*. *")
While strFileName <> ""
FileCopy ("\\F1.F2.org\F3\F4\F5\F6\F7\" & strFileName) ("C:\F8\F9\" & strFileName)
strFileName = Dir()
Wend

 

[Learn2010]

Here is my actual code for the FileCopy line. I replaced letters with an x and numbers with a 1.

FileCopy ("\\xxx1.xxxxx.org\xxxx-1\xxxxxxxxxx xxxxxxxx\xxxxxxx xxxxxx\xxxxxxxx\1-1111" & strFileName) ("c:\xxxxxx\xxxxxxxxx" & strFileName)

I get a Compile error: syntax error.

 

[arnelgp]

add another backslash if 1-1111 is a folder:


Dim strFileName As String
strFileName = Dir("\\F1.F2.org\F3\F4\F5\F6\F7\" & Forms!frmMain!frmMainSub.Form!frmGetNumber.Number & "\*. *")
While strFileName <> ""
FileCopy ("\\F1.F2.org\F3\F4\F5\F6\F7\" & Forms!frmMain!frmMainSub.Form!frmGetNumber.Number & "\" & strFileName) ("C:\F8\F9\" & strFileName)
strFileName = Dir()
Wend

 

[Learn2010]

I added the slash, clicked off the line and got a Compile error: expected =, and the cursor went to the end of the line. I added an = sign, clicked off and got another Compile error: Expected expression

 

[arnelgp]

frmGetNumber is a subform within frmMainSub? is it?
if it is, change this:



Forms!frmMain!frmMainSub.Form!frmGetNumber.Number


to:


Forms!frmMain!frmMainSub!frmGetNumber.Form!Number

 

[Learn2010]

I changed it and got a syntax error on this line. It highlighted the whole line.

FileCopy ("\\xxx1.xxxxx.org\xxxx-1\xxxxxxxxxx xxxxxxxx\xxxxxxx xxxxxx\xxxxxxxx\1-1111" & strFileName) ("c:\xxxxxx\xxxxxxxxx" & strFileName)

 

[Mark_]

For ease of debugging, try the following;

DIM asSource as STRING
Dim asDest as STRING

asSource = "\\xxx1.xxxxx.org\xxxx-1\xxxxxxxxxx xxxxxxxx\xxxxxxx xxxxxx\xxxxxxxx\1-1111\" & strFileName
asDest = c:\xxxxxx\xxxxxxxxx\" & strFileName

debug.print "Source " & asSource
debug.print "Destination " & asDest

FileCopy (asSource, asDest)

This way you can doublecheck where you copy from and where you copy to.

 

[Learn2010]

FileCopy (asSource, asDest) gives a syntax error. I tried it without the parentheses and it errored it as a bad file name.

Also, strFileName is in there.Should it be declared?

 

[Mark_]

Sorry about that, looked at the wrong code sample.

Should be

Dim fso As Object
Dim asSource as STRING
Dim asDest as STRING

asSource = "\\xxx1.xxxxx.org\xxxx-1\xxxxxxxxxx xxxxxxxx\xxxxxxx xxxxxx\xxxxxxxx\1-1111\" & strFileName
asDest = "c:\xxxxxx\xxxxxxxxx\" & strFileName

debug.print "Source " & asSource
debug.print "Destination " & asDest

fso.CopyFile asSource, asDest

Not sure how you generate the long text literals though.

Also, strFileName is in there.Should it be declared?

Should be. I am assuming you would be using in your code. strFileName is the file name you had been using previously and I'm not sure where you were filling it so I left it as is.

 

[Learn2010]

I get a Runtime error '91'

Object variable or With block variable not set

and this line is highlighted in Yellow:

fso.CopyFile asSource, asDest

 

[Mark_]

I've a button that I used for testing. Code that works on my machine is

Private Sub Btn_Copy_Click()
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")

 fso.CopyFile Me.Txt_From, Me.Txt_To
  
End Sub

Me.Txt_From and Me.Txt_To are unbound list boxes.

 

[Learn2010]

I can't seem to get this to work. So, I have another option. I have attached a picture of a subform on a Main form. On this subform is a list of files linked to the Customer record. It has a Description and an actual link to the file. The file can be any type and it opens in its native format.

Is there a way to program this so that I could have a button for each record on the subform that, when clicked, it would copy that file to another location? This way the user would have the option to pick and choose the files they wish to take with them.

The files are on a shared drive and I need to copy them to the user's C:\Drive.

Thank you.