Enumerating values in a field in a table

  • Thread starter Thread starter Janie
  • Start date Start date
J

Janie

I have a table. Each record is unique. One particular field holds a value
which must correspond with the name of a folder in a specific directory. I
know how to check for the existance of a directory. And I've done this
action before going through each item in a listbox using a For Each ... Next
construct. But I've never done it using a field in a table as my data
source. How do I write a For Each where I would say "For Each Value in X
Field in Y Table ... Next Value"

Or is a For Each the wrong method?

Get my objective?

As ever, many thanks for any suggestions.

Janie
 
I have a table. Each record is unique. One particular field holds a value
which must correspond with the name of a folder in a specific directory. I
know how to check for the existance of a directory. And I've done this
action before going through each item in a listbox using a For Each ... Next
construct. But I've never done it using a field in a table as my data
source. How do I write a For Each where I would say "For Each Value in X
Field in Y Table ... Next Value"

Or is a For Each the wrong method?
Click to expand...

Yes, it is the wrong method.

You'll need to open a Recordset based on the table and loop through it. VBA
doesn't have a direct "reach" into tables, and in any case a table is an
unordered bag of data, not an array!

It takes a bit of code, unfortunately; something like

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strFilename As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)
Do Until rs.EOF
strFilename = rs![X Field]
<do something useful with the filename>
rs.MoveNext ' move to the next record
Loop ' Access will exit the loop at the end of the records


John W. Vinson [MVP]
 
Yep -- the old light bulb came on right after I posted my question. I used
the Do Until EOF strategy and got my 300+ folders made in a jiffy. Here's
what I used:

Sub FolderList()
Dim db As DATABASE, rst As Recordset, str As String
Dim fs, f2, fd
Dim MyPath As String

MyPath = "C:\Projects\Project Folders\"

Set fs = CreateObject("Scripting.FileSystemObject")
Set fd = fs.GetFolder(Mypath)
Set db = CurrentDb
Set rst = db.OpenRecordset("tblActiveProjects")

Do Until rst.EOF
str = rst!strProjectNames

For Each f2 In fd.subfolders
If f2.Name = str Then
GoTo cont
End If
MkDir Mypath & str
Next
cont:
rst.MoveNext
Loop

End Sub

Thanks for caring & sharing, John


John W. Vinson said:
I have a table. Each record is unique. One particular field holds a value
which must correspond with the name of a folder in a specific directory. I
know how to check for the existance of a directory. And I've done this
action before going through each item in a listbox using a For Each ... Next
construct. But I've never done it using a field in a table as my data
source. How do I write a For Each where I would say "For Each Value in X
Field in Y Table ... Next Value"

Or is a For Each the wrong method?
Click to expand...

Yes, it is the wrong method.

You'll need to open a Recordset based on the table and loop through it. VBA
doesn't have a direct "reach" into tables, and in any case a table is an
unordered bag of data, not an array!

It takes a bit of code, unfortunately; something like

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strFilename As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)
Do Until rs.EOF
strFilename = rs![X Field]
<do something useful with the filename>
rs.MoveNext ' move to the next record
Loop ' Access will exit the loop at the end of the records


John W. Vinson [MVP]
Click to expand...
 
Back
Top