Lost in For Each loop

  • Thread starter Thread starter shaggles
  • Start date Start date
S

shaggles

I have a database that tracks notices sent by various
regulatory organizations. The main form shows the notice
information with a subform datasheet listing the impacted
departments. The users want to be able to click a button
on the form and have an email go to each of the
departments on the subform. It seems like a for each loop
is the right thing to use but I don't know how to
reference the notice number or the email address. I tried:

For Each Me!Notice_Num In tblNotices

but that didn't even come close to working. I keep
getting an error message Type Mismatch. Can anyone help?
 
The For Each loop has to reference a logical element of a collection. It
usually works well to loop through fields in a recordset, or elements in an
array, or maybe the tables in a database's TableDefs collection. I think
what you want to do is walk through a recordset and return all the
Notice_Num values:

Dim db As Database
Set db = CurrentDb

Dim rst As Recordset
Set rst = db.OpenRecordset("MyTable")

While Not rst.EOF

' Your code here. You will access
' table values using rst!Notice_Num

rst.MoveNext
Wend

This will loop through every record in the table. If you want to limit the
scope, set a Where Condition thus:

Set rst = db.OpenRecordset("SELECT * FROM MyTable WHERE [SomeField] = '" &
SomeTextValue & "'")

HTH
Paul Johnson
 
I get an error message say database is not a defined
datatype when I type "Dim db as Database".
-----Original Message-----
The For Each loop has to reference a logical element of a collection. It
usually works well to loop through fields in a recordset, or elements in an
array, or maybe the tables in a database's TableDefs collection. I think
what you want to do is walk through a recordset and return all the
Notice_Num values:

Dim db As Database
Set db = CurrentDb

Dim rst As Recordset
Set rst = db.OpenRecordset("MyTable")

While Not rst.EOF

' Your code here. You will access
' table values using rst!Notice_Num

rst.MoveNext
Wend

This will loop through every record in the table. If you want to limit the
scope, set a Where Condition thus:

Set rst = db.OpenRecordset("SELECT * FROM MyTable WHERE [SomeField] = '" &
SomeTextValue & "'")

HTH
Paul Johnson

I have a database that tracks notices sent by various
regulatory organizations. The main form shows the notice
information with a subform datasheet listing the impacted
departments. The users want to be able to click a button
on the form and have an email go to each of the
departments on the subform. It seems like a for each loop
is the right thing to use but I don't know how to
reference the notice number or the email address. I tried:

For Each Me!Notice_Num In tblNotices

but that didn't even come close to working. I keep
getting an error message Type Mismatch. Can anyone
Click to expand...
help?


.
Click to expand...
 
I'm guessing you need to set a reference to the Microsoft DAO 3.6 reference
library. You do this from the Visual Basic Editor, in the Tools/References
menu item.

Besides that, if you want to abbreviate the code, you can do without the
"db" object variable and create your recordset object from the CurrentDb
object:

Set rst = CurrentDb.OpenRecordSet("MyTable")
or
Set rst = CurrentDb.OpenRecordSet( _
"SELECT * FROM MyTable " & _
"WHERE [SomeField] = '" & _
SomeTextValue & "'"")

Paul Johnson

shaggles said:
I get an error message say database is not a defined
datatype when I type "Dim db as Database".
-----Original Message-----
The For Each loop has to reference a logical element of a collection. It
usually works well to loop through fields in a recordset, or elements in an
array, or maybe the tables in a database's TableDefs collection. I think
what you want to do is walk through a recordset and return all the
Notice_Num values:

Dim db As Database
Set db = CurrentDb

Dim rst As Recordset
Set rst = db.OpenRecordset("MyTable")

While Not rst.EOF

' Your code here. You will access
' table values using rst!Notice_Num

rst.MoveNext
Wend

This will loop through every record in the table. If you want to limit the
scope, set a Where Condition thus:

Set rst = db.OpenRecordset("SELECT * FROM MyTable WHERE [SomeField] = '" &
SomeTextValue & "'")

HTH
Paul Johnson

I have a database that tracks notices sent by various
regulatory organizations. The main form shows the notice
information with a subform datasheet listing the impacted
departments. The users want to be able to click a button
on the form and have an email go to each of the
departments on the subform. It seems like a for each loop
is the right thing to use but I don't know how to
reference the notice number or the email address. I tried:

For Each Me!Notice_Num In tblNotices

but that didn't even come close to working. I keep
getting an error message Type Mismatch. Can anyone
Click to expand...
help?


.
Click to expand...
Click to expand...
 
Back
Top