Use query results to copy files?

[Gary]

Hi, I have a query that returns results like:

Name: ID: Picture ID File Name:
Bob 1234 bobs_id_pic.jpg
Tina 5678 tinas_id_pic.jpg
....

Is there a way to use these results to (automatically) copy all the id_pics
to another folder (assuming all ID pics are in the same folder)? Thanks!

 

[mscertified]

Yes, but it's not a trivial task.
You would have to open a recordset on the query results and then use the
file system object to do the copying.
If there aren't too many, it will be quicker to do it manually.

-Dorian

 

[Gary]

Well, we're talking usually somewhere in the range of 500-1500. The number
varies widely depending on other search criteria I didn't mention. So I'm
tired of doing it manually... =) How non-trivial are we talking? Like do I
need to be an expert-guru with this stuff? Thanks for your reply.

-Gary

 

[Klatuu]

Assuming all the pictures are in the same folder, you can use the Dir
function to drive a loop to copy them to a new folder.

Dim strFileName As String
Dim strSourcePath as String
Dim strTargetPath as String

strSourcePath = "C:\IdPictures\"
strTargetPath = "C:\NewIdPictures\"
strFileName = Dir(strSourcePath & "*.jpg")

Do While strFileName <> vbNullString
FileCopy strSourcePath & strFileName, strTargetPath & strFileName
Dir()
Loop

 

[Klatuu]

Gary, I ignored the query part. This doesn't require a query unless the
query filters records. If that is the case, let me know and we can revise.

 

[Gary]

Thank you for your help! I am fairly new to Access, or at least to Visual
Basic... Yes the query does filter the records. If this makes it harder, I
could just make it a make-table query, although I'd like to avoid that step
if possible.

-Gary

 

[Klatuu]

Avoid make table queries, they can sometimes be useful, but come with some
overhead. You can use a query as a recordset. Here is a revised version:
Public Function TransferPics()
Dim strFileName As String
Dim strSourcePath as String
Dim strTargetPath as String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("MyQueryName")

If rst.Recordcount = 0 Then
MsgBox "No Pictures Found"
Else
strSourcePath = "C:\IdPictures\"
strTargetPath = "C:\NewIdPictures\"

With rst
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = ![PictureFieldName]
FileCopy strSourcePath & strFileName, strTargetPath &
strFileName
Loop
End With
rst.Close
Set rst = Nothing
End If

 

[Gary]

Sweet! It's almost working, just two problems (I think):

1) The line:
Set rst = CurrentDb.OpenRecordset("test_GET_PICTURES")
Is telling me "too few parameters, Expected 1.". My query uses a
message-input box to filter results, i.e. the user enters "3", and my query
will find all ID#'s starting with "3". (Again. if absolutely necessary I
could use a make-table query)

2) In the loop the strFIleName is always the first one (not moving down the
rows).

Thank you SO much for all your help!

-Gary

Avoid make table queries, they can sometimes be useful, but come with some
overhead. You can use a query as a recordset. Here is a revised version:
Public Function TransferPics()
Dim strFileName As String
Dim strSourcePath as String
Dim strTargetPath as String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("MyQueryName")

If rst.Recordcount = 0 Then
MsgBox "No Pictures Found"
Else
strSourcePath = "C:\IdPictures\"
strTargetPath = "C:\NewIdPictures\"

With rst
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = ![PictureFieldName]
FileCopy strSourcePath & strFileName, strTargetPath &
strFileName
Loop
End With
rst.Close
Set rst = Nothing
End If
--
Dave Hargis, Microsoft Access MVP

Thank you for your help! I am fairly new to Access, or at least to Visual
Basic... Yes the query does filter the records. If this makes it harder, I
could just make it a make-table query, although I'd like to avoid that step
if possible.

-Gary

 

[Gary]

I fixed issue number 2 by adding "rst.MoveNext" into the loop.

-Gary

Sweet! It's almost working, just two problems (I think):

1) The line:
Set rst = CurrentDb.OpenRecordset("test_GET_PICTURES")
Is telling me "too few parameters, Expected 1.". My query uses a
message-input box to filter results, i.e. the user enters "3", and my query
will find all ID#'s starting with "3". (Again. if absolutely necessary I
could use a make-table query)

2) In the loop the strFIleName is always the first one (not moving down the
rows).

Thank you SO much for all your help!

-Gary

Avoid make table queries, they can sometimes be useful, but come with some
overhead. You can use a query as a recordset. Here is a revised version:
Public Function TransferPics()
Dim strFileName As String
Dim strSourcePath as String
Dim strTargetPath as String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("MyQueryName")

If rst.Recordcount = 0 Then
MsgBox "No Pictures Found"
Else
strSourcePath = "C:\IdPictures\"
strTargetPath = "C:\NewIdPictures\"

With rst
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = ![PictureFieldName]
FileCopy strSourcePath & strFileName, strTargetPath &
strFileName
Loop
End With
rst.Close
Set rst = Nothing
End If
--
Dave Hargis, Microsoft Access MVP

Thank you for your help! I am fairly new to Access, or at least to Visual
Basic... Yes the query does filter the records. If this makes it harder, I
could just make it a make-table query, although I'd like to avoid that step
if possible.

-Gary

:

Gary, I ignored the query part. This doesn't require a query unless the
query filters records. If that is the case, let me know and we can revise.
--
Dave Hargis, Microsoft Access MVP


:

Hi, I have a query that returns results like:

Name: ID: Picture ID File Name:
Bob 1234 bobs_id_pic.jpg
Tina 5678 tinas_id_pic.jpg
...

Is there a way to use these results to (automatically) copy all the id_pics
to another folder (assuming all ID pics are in the same folder)? Thanks!

 

[Klatuu]

Sorry about that, I left that line out.

As to the parameter prompt, I would suggest you add an unbound control on
your form to put in the value you want to filter on and refer to it in your
query:

WHERE ID = Forms!MyForm!MyControl

of course, substitute real names.
--
Dave Hargis, Microsoft Access MVP

I fixed issue number 2 by adding "rst.MoveNext" into the loop.

-Gary

Sweet! It's almost working, just two problems (I think):

1) The line:
Set rst = CurrentDb.OpenRecordset("test_GET_PICTURES")
Is telling me "too few parameters, Expected 1.". My query uses a
message-input box to filter results, i.e. the user enters "3", and my query
will find all ID#'s starting with "3". (Again. if absolutely necessary I
could use a make-table query)

2) In the loop the strFIleName is always the first one (not moving down the
rows).

Thank you SO much for all your help!

-Gary

Avoid make table queries, they can sometimes be useful, but come with some
overhead. You can use a query as a recordset. Here is a revised version:
Public Function TransferPics()
Dim strFileName As String
Dim strSourcePath as String
Dim strTargetPath as String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("MyQueryName")

If rst.Recordcount = 0 Then
MsgBox "No Pictures Found"
Else
strSourcePath = "C:\IdPictures\"
strTargetPath = "C:\NewIdPictures\"

With rst
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = ![PictureFieldName]
FileCopy strSourcePath & strFileName, strTargetPath &
strFileName
Loop
End With
rst.Close
Set rst = Nothing
End If
--
Dave Hargis, Microsoft Access MVP


:

Thank you for your help! I am fairly new to Access, or at least to Visual
Basic... Yes the query does filter the records. If this makes it harder, I
could just make it a make-table query, although I'd like to avoid that step
if possible.

-Gary

:

Gary, I ignored the query part. This doesn't require a query unless the
query filters records. If that is the case, let me know and we can revise.
--
Dave Hargis, Microsoft Access MVP


:

Hi, I have a query that returns results like:

Name: ID: Picture ID File Name:
Bob 1234 bobs_id_pic.jpg
Tina 5678 tinas_id_pic.jpg
...

Is there a way to use these results to (automatically) copy all the id_pics
to another folder (assuming all ID pics are in the same folder)? Thanks!

 

[Gary]

Great, it's working perfectly! Thank you so much for your help!

-Gary

Sorry about that, I left that line out.

As to the parameter prompt, I would suggest you add an unbound control on
your form to put in the value you want to filter on and refer to it in your
query:

WHERE ID = Forms!MyForm!MyControl

of course, substitute real names.
--
Dave Hargis, Microsoft Access MVP

I fixed issue number 2 by adding "rst.MoveNext" into the loop.

-Gary

Sweet! It's almost working, just two problems (I think):

1) The line:
Set rst = CurrentDb.OpenRecordset("test_GET_PICTURES")
Is telling me "too few parameters, Expected 1.". My query uses a
message-input box to filter results, i.e. the user enters "3", and my query
will find all ID#'s starting with "3". (Again. if absolutely necessary I
could use a make-table query)

2) In the loop the strFIleName is always the first one (not moving down the
rows).

Thank you SO much for all your help!

-Gary


:

Avoid make table queries, they can sometimes be useful, but come with some
overhead. You can use a query as a recordset. Here is a revised version:
Public Function TransferPics()
Dim strFileName As String
Dim strSourcePath as String
Dim strTargetPath as String
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("MyQueryName")

If rst.Recordcount = 0 Then
MsgBox "No Pictures Found"
Else
strSourcePath = "C:\IdPictures\"
strTargetPath = "C:\NewIdPictures\"

With rst
.MoveLast
.MoveFirst
Do While Not .EOF
strFileName = ![PictureFieldName]
FileCopy strSourcePath & strFileName, strTargetPath &
strFileName
Loop
End With
rst.Close
Set rst = Nothing
End If
--
Dave Hargis, Microsoft Access MVP


:

Thank you for your help! I am fairly new to Access, or at least to Visual
Basic... Yes the query does filter the records. If this makes it harder, I
could just make it a make-table query, although I'd like to avoid that step
if possible.

-Gary

:

Gary, I ignored the query part. This doesn't require a query unless the
query filters records. If that is the case, let me know and we can revise.
--
Dave Hargis, Microsoft Access MVP


:

Hi, I have a query that returns results like:

Name: ID: Picture ID File Name:
Bob 1234 bobs_id_pic.jpg
Tina 5678 tinas_id_pic.jpg
...

Is there a way to use these results to (automatically) copy all the id_pics
to another folder (assuming all ID pics are in the same folder)? Thanks!