VB FileCopy Help

[adam.greer]

Hi Guys

Trying to copy a large amount of files based on fields in a query

The code is very simple

Private Sub Command4_Click()

FileCopy Query_qryFilename.Filepath, "C:\Pulse Local Database" & Query_qryFilename.Filename


End Sub

However I get the '424 Error, Object Required'. The code works when referenced from a field in a form, but that means I can only copy one file at a time not the 100's needed.

Any ideas?

Thanks


Adam

 

[bonekrusher]

Try assigning your file path to a variable.

Also, I am not sure what "Query_qryFilename.Filename" returns, but I know your missing a "\" in " "C:\Pulse Local Database"" it should be ""C:\Pulse Local Database\"

Good Luck

 

[adam.greer]

Query_Filename.Filepath is a list of fields with the full file path of the file, including the filename. Query_Filename.Filename returns the filename with a \ at the start.

Also I am not missing the last \ because it is located in the field.

I have tested this set up fine when from a form but not a query.

Not sure what you mean by variable either.

Any other clues?

 

[bonekrusher]

OK, I wasnt sure.

A variable is like this

Dim path as string
path =  Query_Filename.Filepath

filecopy path, "C:\Pulse Local Database" & path

 

[KeithG]

you need to assign your query to a recordset and the loop through the records and copy each file.

 

[adam.greer]

Afraid theres no luck

Dim path As String
Dim name As String

path = Query_Filename.Filepath
name = Query_Filename.Filename

FileCopy path, "C:\Pulse Local Database" & name

Had the same 424 error.

If I place the fields on a form and use the code

FileCopy Form_Filename.Filepath, "C:\Pulse Local Database" & Form_Filename.Filename

It works without problem, but it only copies the current record, is there perhaps a way around that?

Thanks for your help

Adam

P.S. Just saw your post Keith I'll give it a go, any relation to Lt.Keith of CoH?

 

[KeithG]

try this;

Sub CopyFiles()
    Dim myRec As DAO.Recordset
    
    Set myRec = CurrentDb.OpenRecordset("Query_qryFilename")
    
    myRec.MoveFirst
    
    Do Until myRec.EOF
        FileCopy myRec.Fields("FilePath"), "C:\Pulse Local Database" & myRec.Fields("Filename")
        myRec.MoveNext
    Loop

End Sub

 

[adam.greer]

I receive a complie error with the line


myRec As DAO.Recordset

User-defined type not defined


Did i miss something obvious?

Adam

 

[workmad3]

you need to add the DAO reference to your project by the looks of things

 

[KeithG]

WorkMad3 is correct. In your module go to Tools/ Refrences and check the Microsoft DAO 3.6 Object Library.

 

[adam.greer]

That worked perfectly, thanks alot guys. I can use that in alot of places

Thanks again


Adam