For Each inside a For Each?

  • Thread starter Thread starter JC
  • Start date Start date
J

JC

So, I'm a bit inexperience with For Each loops, but I've got an interesting
problem:

I'm running an Access DB and wrote some code that goes through a recordset
and takes values out of it and throws it into an excel sheet. That part
works fine.

Next up, while writing the data out to excel, I want it to look at the data
in one excel column and if it matches a value in a 2nd recordset, then put
that value down in excel as well (basically trying to put data from two
different recordsets in the same excel sheet).

To do this, I tried to write a for each loop inside of another. Apparently
this doesn't work because I'm already running a for each loop. So, is there
any way around that or are there any other ideas out there?

Thanks in advance,
- Jeff
 
Yes, you can nest for each loops. Here is an example:

Sub ListFieldNames(strTblName)
'Lists the fields in a table
Dim dbf As Database
Dim tdfs As TableDefs
Dim tdf As TableDef
Dim fld As Field
Dim blnFoundIt As Boolean

Set dbf = CurrentDb
Set tdfs = dbf.TableDefs

For Each tdf In tdfs
If tdf.Name = strTblName Then
Debug.Print "Table " & strTblName & " contains " &
tdf.Fields.Count _
& " Fields"
For Each fld In tdf.Fields
Debug.Print fld.Name
Next fld
blnFoundIt = True
Exit For
End If
Next tdf
If Not blnFoundIt Then
MsgBox "Table " & strTblName & " Not Found in Database", _
vbExclamation, "ListFieldNames"
End If
Set fld = Nothing
Set tdfs = Nothing
Set dbf = Nothing
Set tdf = Nothing
End Sub
 
As Klatuu said, you most certainly can next For Each loops. Why don't you
post the code that's giving you the problem and we'll have a look at it to
see what the problem might be.


Rob
 
Back
Top