Moving Documents

[Rich1968]

June I really appreciate your help with this.



The data structure is as follows

Make table query:

ClientID
DebtorID

The debtorID and the PDF files have the same name. So in the debtorid colume is a list of the files.

Set rs = CurrentDb.OpenRecordset("SELECT DebtorID, FROM tblDocuments;")

or
in the place of DocumentName "DebtorID"
Set rs = CurrentDb.OpenRecordset("SELECT ClientID, DocumentName FROM tblDocuments;")

Thanks Rich

[June7]

Then this makes sense:

Set rs = CurrentDb.OpenRecordset("SELECT ClientID, DebtorID FROM tblDocuments;")

But why would a MAKE TABLE action be needed?

[Rich1968]

The document is in the DebtorID Field. There are hundreds of pdf files that are listed in the DebtorID Field. When I run the code it has an error 424 Object required.

Option Compare Database
Sub MovePDF()
On Error GoTo Err_Proc
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT ClientID, DebtorID FROM tblDocuments;")

While Not rs.EOF

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

FileCopy "C:\ClientDocs\" & rsDocs!DebtorID, "C:\ClientDocs\ & ClientID & 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 FileCopy syntax is wrong. Do not enclose variables within quote marks. Missing a \.

FileCopy "C:\ClientDocs\" & rsDocs!DebtorID, "C:\ClientDocs\" & ClientID & "\" & rsDocs!DebtorID

Does the DebtorID value have the .pdf extension?

[Rich1968]

Yes it does have a pdf extension, When I run the code it returns "Too Few Parameters"
The Client Directory was not made also.

[June7]

This is the error after corrections?

I don't know why. The recordset code looks good to me.

Step debug. Do values from recordset get read?

Refer to link at bottom of my post for debugging guidelines.

[Rich1968]

Hello June I'm able to get back to this. This the last part of my project. I keep getting a variable not defined for the DebtorID. Here's my code:

Sub MovePDF()

On Error GoTo Err_Proc

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT DebtorID, FROM PDF;")

While Not rs.EOF
If Dir("C:\ClientDocs\" & DebtorID, vbDirectory) = "" Then MkDir ("C:\ClientDocs\" & DebtorID)
FileCopy "C:\CMSDocs\" & 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

The Qry ( PDF ) returns 2 fields 1. DebtorID (This the list of PDF Docs I want to move) and 2. ClientID

Thanks

Rich

[June7]

The recordset SQL statement has only one field - DebtorID. Remove the comma or include another field.

If you want to move the documents listed in DebtorID into a folder named for the ClientID, you need to look at post 9 again because your code won't work.

[Rich1968]

June,

Here's the code I have so far. Please take one last look at it. I have noted the items that I think are issues. I appreciate your time helping me.


Sub MovePDF()

On Error GoTo Err_Proc

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT DebtorID FROM PDF;") 'PDF is the qry'

While Not rs.EOF
If Dir("C:\ClientDocs\" & DebtorID, vbDirectory) = "" Then MkDir ("C:\ClientDocs\" & DebtorID)
'Error on DebtorID, Variable not defined.
FileCopy "C:\CMSDocs\" & rsDocs!DocumentName, "C:\ClientDocs\" & DebtorID & "\" rsDocs!DocumentName
'CMSDocs is were the pdf files are located'
'ClientDocs is were I want the files to be copied to'
'DebtorID is the field name listing the PDF Files'
' "\" error Expected end of statement

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]

If DebtorID is the field with the document names, why would you name the new folder with that?

You declare a recordset object named rs yet the copy code references rsDocs. If DebtorID is the field with the document names, why does the code use DocumentName?

Exactly what do you want to do?

Do you want to create a new folder for a client? Do you want that folder to be named with client ID? Do you want to copy into that client's folder only their documents? If yes, then the recordset needs two fields - one with the document names and one with client names. Then reference YOUR field names in the code.

'Error on DebtorID, Variable not defined. This is because need to prefix with recordset object variable: rs!DebtorID

[Rich1968]

June, I'm almost there!!!

'If DebtorID is the field with the document names, why would you name the new folder with that?
' The new folder is named ClientDocs

'You declare a recordset object named rs yet the copy code references rsDocs. If DebtorID is the field with the document names, why does the code use DocumentName?
' Correction made

'Exactly what do you want to do?
' I want to copy the pdf files listed in my query to a the new directory ClientDocs

'Do you want to create a new folder for a client?
' NO I just want to copy the PDF files listed in my query "PDF" into the Folder ClientDocs.

'Do you want that folder to be named with client ID?
' No I want the folder to be named ClientDocs

'Do you want to copy into that client's folder only their documents?
'Yes, They are generated from the query "PDF"

'If yes, then the recordset needs two fields - one with the document names and one with client names. Then reference YOUR field names in the code.
'Given my answers above please explain

'Error on DebtorID, Variable not defined. This is because need to prefix with recordset object variable: rs!DebtorID
'Made the change and it works now

[June7]

I thought purpose of this process was to move client documents from one catchall folder into separate client folders, otherwise, what is the point of copying all the documents to same folder?

[Rich1968]

Ok. What I am doing is closing a business. (A collection agency)I have hundreds of clients, with thousands of PDF files associated with there accounts. For my largest clients I want to give them a database with only there accounts, and PDF files. SO in order to accomplish this I have built the database with only accounts. Now all I need to do is separate the PDF files. I want to do this in a way that copies the files from the main PDF folder to a folder called ClientDocs. After I have done this, I will rename the folder by the client name.

Are you telling me that I can do this in one code and separate the PDF's by client name? If so I will shower you with Love and admiration for the rest of your life!!!!

Sorry I got away from the business at hand. I was going to do this for only my largest clients. That's why I wanted to run this code multiple times and rename the folder ClientDocs to the Clients name (ie Smith Co.) .

June I can't tell you how much I appreciate your help. I am retiring in April, and want to leave on the best of terms with my clients. This would be a blessing!!!

Thanks

Rich De Gray

[June7]

The code I suggested back in post 9 was to create an individual folder for each client and copy their associated files into their individual folder, all in one procedure. The code uses ClientID but if you want client name, adapt code. The recordset SQL statement might have to be a join of tables. Use the Query Builder to help get correct syntax.

[Rich1968]

Very cool!!! Ok I will be working on this tonite....News at 8

Rich