Retrieve query fields in a VBA variable

  • Thread starter Thread starter Vsn
  • Start date Start date
V

Vsn

Hi all,

I was wondering if there is a method to capture the out come (a specific
data field) from a query in to a VBA variable.

The thing I would like to achieve is, copy a file from which the path and
name is hold in a table field to another by the user defined directory. An
only the files from which the record in ticket in a yes/no field.

The table has data like this:-
fID fName fCheck fFileLoc
1 Henk Yes C:\Accounts\Henk.PDF
2 Geert No C:\Geert\Geert.JPG
3 John No C:\Temp\Map.PDF
4 Mike Yes D:\Mike\Calculation.XLS

From above data I would like to copy the files named in record 1 and 4
because they are checked.

Once I catch the fFileLoc in a VBA variable (one by one) I know how to cope
with the rest.

So I thought about something as looping though the data with a for...next or
do...while. I just can not grasp it really.

Can some one give me a clue, it will be most appreciated.

Thx,
Ludovic
 
something along the lines of

Dim rst As DAO.Recordset, strSQL As String
Dim strPath As String

strSQL = "SELECT fFileLoc FROM TableName " _
& "WHERE fCheck = True"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If Not (rst.BOF And rst.EOF) Then
Do
strPath = rst("fFileLoc")
<do whatever you need to do with the
filepath/name here>
rst.MoveNext
Loop Until rst.EOF
End If

rst.Close
Set rst = Nothing

hth
 
Excellent nearly just copy paste. Thx Ludovic


tina said:
something along the lines of

Dim rst As DAO.Recordset, strSQL As String
Dim strPath As String

strSQL = "SELECT fFileLoc FROM TableName " _
& "WHERE fCheck = True"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If Not (rst.BOF And rst.EOF) Then
Do
strPath = rst("fFileLoc")
<do whatever you need to do with the
filepath/name here>
rst.MoveNext
Loop Until rst.EOF
End If

rst.Close
Set rst = Nothing

hth
Click to expand...
 
Back
Top