Send Files to Another Folder (1 Viewer)

[Learn2010]

We tried this in different ways and it hasn't worked, yet. I have another idea. Let me explain. We have a networked database with a SQL backend that is on a network drive. The users have a frontend, also located on the network drive. They work offsite a lot. Since this is not a web based database, I had to improvise so they could take the data with them and update it when they return to the site.

The user loads the Customer record. One of the subforms has a button that when clicked, loads data from the current Customer record to another database located on their C:\Drive, which is obviously linked with their frontend. Upon returning to the site, the database on their C:\Drive is able to load new data and update old data in the SQL backend. That works well.

Here is the issue I have. We receive various communications from and for the Customers, save them in a networked folder, then link them to the Customer record. These include emails, Word documents, PDF’s, etc. The users need to be able to view these when they are offsite.

I have attached a screenshot of the subform which shows the files that are linked to the Customer record. It has a brief description of the file and it has a link to the file. They could open each file and save it to a specific folder on their C:\Drive and take them with them.

I need to automate this process somehow. Is there a way that a button on the subform, when clicked, would send this specific file to the designated folder?

Another option is this. The files are named with the Customer ID, a space, then a short description. The Customer ID could be on the subform as well. I could put a button on the subform that would open the folder where the files are stored. I would need it to be able to identify these files and go to them so the user could use the SendTo command to send them to that folder on their C:\Drive.

Anybody got an idea how to do this?

Thank you.

 

[Ranman256]

something like this:
click the button, it scan thru the list , exports each file to the users MyDocuments.
Set the given field names to your field names.

'-----------
Private Sub btnExportAllRecs_Click()
'-----------
Dim vDir, vName, vSrc, vTarg

vDir = getMyDocs()
With Me.Recordset
   .MoveFirst
   While Not .EOF
       vName = .Fields("ClientName").Value & "_" & .Fields("FileName").Value
       vSrc = .Fields("FileName").Value
       vTarg = vDir & vName
       Copy1File vSrc, vTarg
      
      .MoveNext
   Wend
End With
End Sub



'-----------
Public Function getMyDocs()
'-----------
Dim vDir, vUsr

On Error GoTo errDocs
vUsr = Environ("UserProfile")
vDir = vUsr & "\My Documents\"
If Not DirExists(vDir) Then
    vDir = vUsr & "\Documents\"
    If Not DirExists(vDir) Then
       vDir = "c:\temp"
       MakeDir vDir
    End If
End If
getMyDocs = vDir
Exit Function
errDocs:
MsgBox "Cannot find temp folder", vbInformation, "getMyDocs():" & Err
End Function


'-----------
Public Function DirExists(ByVal pvDir) As Boolean
'-----------
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
DirExists = FSO.FolderExists(pvDir)
Set FSO = Nothing
End Function


'-----------
Public Sub Copy1File(ByVal pvSrc, ByVal pvTarg)
'-----------
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile pvSrc, pvTarg
Set FSO = Nothing
End Sub

 

[Learn2010]

In experimenting with the above, I added a button to the subform that opens the folder that holds the linked files. I am using:

START OF CODE
Dim Foldername As String
Foldername = "\\xxx1.xxxxx.xxx\xxxx-1\xxxx\xxxx\xxxx\1-1111\"
Shell "C:\WINDOWS\explorer.exe """ & Foldername & "", vbNormalFocus
END OF CODE

On the subform is the Customer ID, in this case 123456. I would need to add two things here.
1) Sort the records alphanumerically
2) Go to the records that start with the Customer ID 123456. The sort would put them all together. Then I could use the SendTo option.

Can that be done?

 

[Learn2010]

Ranman256,

This looks as though it may solve my problem. I am not that good with the code. I am trying to figure a way to get the corresponding files from one location to another. The naming convention we use is to name the files with the Customer ID and a brief description.

On the subform, I have the Customer ID. In my sample it would be 123456. In the folder named 1-1111, which is located in the folder \\xxx1.xxxxx.xxx\xxx-1\xxxxxxxxxx xxxxxxxx\xxxxxxx xxxxxx\xxxxxxxx\, there are other files with the Customer ID, which is what I am trying to copy over. There is 123456 File1 and 123456 File2. Will your code identify these and copy them over?


Thank you.