Using Data from a Table in Variables and Loops

[Neily]

Hi again,

Separate question, so separate thread.

I have a table with names and associated e-mail addresses in it.
Is there a way to loop through each record on the table and collect the name
and e-mail address in variables.

eg.

For i = 1 to Total Records

personname = Table("Address").Record(i).field("name").value
email = Table("Address").Record(i).field("email").value

Call EmailOutProc

Next i

I'm under no illusions that this is not correct, but hopefully someone can
get enough of the gist to point me in the right direction.

Thanks
Neil

 

[Douglas J Steele]

You need to create a recordset, and loop through the recordset.

I always use DAO. If you're using Access 2000 or 2002, you'll need to go
into the References and add a reference to DAO if you haven't already done
so to use this code:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT [name], email FROM Address"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do While rsCurr.EOF = False
personname = rsCurr![name]
email = rsCurr!email
Call EmailOutProc
rsCurr.MoveNext
Loop

Note that name isn't a good choice for a field name: it's a reserved word,
so you can experience problems using it. If you absolutely can't change it,
make sure you use square brackets around it, as in my example.

And I'm assuming you actually need to pass values to EmailOutProc, as in
Call EmailOutProc(personname, email)