Moving Documents

[Rich1968]

Hello All,



I want to move documents associated with one customer from my main document folder to another. I've looked at some sample code but nothing seems to work

I want to run a query to get the document list
Run the code to move these files ( PDF Files )
and It's done

Any help would be greatly appreciated.

Thanks
Rich

[Rawb]

Is the document list saved somewhere in your DB? If not you can do it in several different ways, some of which don't need your DB at all...

[Rich1968]

Hello Rawb,

No the list is not saved in my DB. I use a hyperlink to open the PDF docs if I need them. The PDF files are for various clients, so I need to run the query to get the list.

I want to run a query to get the document list
Run the code to move these files ( PDF Files )
and It's done

Thanks
Rich

[June7]

So you do have documents listed in a table? What criteria would be in the query to filter the records? What is the path to 'main' document folder? Are you moving documents from local folder to central server?

[Rich1968]

The list of documents are in a table.

Here's what I'm trying to accomplish. I own a small collection agency. I am closing it, and I want to give my clients a database with all their records. I have pdf files that were sent to me when they placed the accounts. The database part of this is a piece of cake because its already done. The pdf doc's I have are in "K" drive. There are thousands of pdf files. I want to run a query in access for each client to get the list of pdf files associated with my client, Then move those files to a separate folder. I need help, Please.

Thanks
Rich

[June7]

Pseudocode for what you want:

Open recordset of client IDs
Start Loop
Open another recordset of documents filtered by clientID of first recordset
Start inner loop
Copy file
Move to next document record
Repeat loop until end of document records
Close documents recordset
Move to next client record
Repeat loop until end of client records

Now which part of this process do you not understand?

[Rich1968]

Thanks for your advice.

[Rich1968]

After hours of trying to figure this out, I now admit it, I don't get it. The reason I like access is the code wizards. Grrrrrrrrr

[June7]

Something like:

Dim rsClients As DAO.Recordset, rsDocs As DAO.Recordset
Set rsClients = CurrentDb.OpenRecordset("SELECT DISTINCT ClientID FROM tblDocuments;")

While Not rsClients.EOF
Set rsDocs = CurrentDb.OpenRecordset("SELECT ClientID, DocumentName FROM tblDocuments WHERE ClientID=" & rsClients!ClientID;")
While Not rsDocs.EOF
MkDir "C:\ClientDocs\" & ClientID
FileCopy "C:\ClientDocs\" & rsDocs!DocumentName, "C:\ClientDocs\" & ClientID " \" & rsDocs!DocumentName
rsDocs.MoveNext
Wend
rsDocs.Close
rsClients.MoveNext
Wend

Or using 1 recordset:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT ClientID, DocumentName FROM tblDocuments;")
While Not rs.EOF
If Dir("C:\ClientDocs\" & ClientID, vbDirectory) = "" Then MkDir("C:\ClientDocs\" & ClientID)
FileCopy "C:\ClientDocs\" & rsDocs!DocumentName, "C:\ClientDocs\" & ClientID " \" & rsDocs!DocumentName
rs.MoveNext
Wend

Of course, I don't know your file structure nor table and field names so these are rough examples.

[Rich1968]

June,

Thanks for your help. Here's what I have so far, I get an error in the select statement. Your help would be greatly appreciated.

Sub MovePDF()
On Error GoTo Err_Proc
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DebtorID, FROM tblDocuments;")
While Not rs.EOF

If Dir("C:\ClientDocs\" & DebtorID, vbDirectory) = "" Then MkDir ("C:\ClientDocs\" & DebtorID)

FileCopy "C:\ClientDocs\" & rsDocs!DebtorID, "C:\ClientDocs\" & DebtorID & rsDocs!DebtorID
rs.MoveNext

Wend
Exit_Proc:
On Error Resume Next
rs.close
Set rs = Nothing
Exit Sub
Err_Proc:
MsgBox err.Number & " " & err.Description
Resume Exit_Proc
End Sub

[June7]

The comma after DebtorID is issue. Don't you need field for document name?

[Rich1968]

The Field Name Is "DebtorID" The table is "tblDocuments"

Thanks
Rich

[June7]

Look at my example code again. It uses 2 fields.

[Rich1968]

Ok so ClientID would be my CustomerID, and Documentname would be DebtorID?

[June7]

It is if DebtorID is the name for the document you want to move.

I don't know your data structure. You tell me name of field that has document name.