Moving PDF Files

[Rich1968]

Hello All,

I'm Trying to move PDF Files associated with a customer to there specific folder

The query gathers the data, Gives me a list of files by account number

Now I want to use the results of the query to move those files

I have tried to modify code I found on here but I'm not very good at this. Can anyone help!

Thanks

Rich1968

 

[Minty]

Show us what you have and where the errors are - give us your field names and maybe some sample data.

As a start write down a flow, then achieve the individual tasks;

You will need to check the directory exists that you want to move the files to
If not you will need to create it.
Once the destination folder is created copy the file.
Check the file is there.
Delete the original.

Do this one step at a time for a single file then you can create a loop to do this for a file list.

Search for Dir() related function for the first parts.

 

[Rich1968]

Here's what I have so Far

Sub MovePDF()
On Error GoTo Err_Proc

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DebtorID, *.PDF FROM tblDocuments;")
While Not rs.EOF

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

FileCopy "C:\ClientDocs\" & rsDocs! * .PDF, "C:\ClientDocs\" & CustomerID & 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

 

[Minty]

Okay - you haven't said what was / where it is failing but I'd hazard a guess it doesn't like this;
"SELECT DebtorID, *.PDF FROM tblDocuments;"

*.pdf is not a field it's the criteria. Create your query in the query designer then view the SQL and use that as your record source.

You probably want something like "SELECT DebtorID, txtFilename FROM tblDocuments WHERE txtFilename Like '*.pdf';"

 

[Rich1968]

Here's my code. It will create the folder for the ClientId but will not copy the DebtorID pdf to the new folder. I'm stumped

Sub PDF()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT ClientID, DebtorID FROM tblDocuments WHERE NOT ClientID IS NULL and NOT DebtorID IS Null ORDER BY ClientID, DebtorID;")

While Not rs.EOF

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

If Dir("C:\CMSDocs\" & rs!ClientID & "\" & rs!DebtorID) <> "" Then

FileCopy "C:\CMSDocs\" & rs!DebtorID, "C:\ClientDocs\" & rs!ClientID & "\" & rs!DebtorID

End If

rs.MoveNext

Wend

End Sub

 

[Minty]

I think you also need to make the DebtorID folder if it doesn't exist?

You appear to only be checking folders - so Filecopy won't work as you appear to be trying to copy folders not files?