Cycle Through Query Results

[Guest]

I am trying to automatically open up MS Office to send an e-mail. The
contents of the e-mail will be a string that I am having trouble constructing
from a query. The query lists primary keys.

In pseudocode, I am trying to do something like this:

dim message as string
message = ""
for index in 1 through (number of query results) loop
name = dlookup("[name]","contacts","[index]" = index
building = dlookup("[building]","contacts","[index]" = index
adults = dlookup("[adults]","contacts","[index]" = index
children = dlookup("[children]","contacts","[index]" = index
date = dlookup("[date]","contacts","[index]" = index
special_requests = dlookup("[request]","contacts","[index]" = index
message = message & newline & name & building & adults & children & date
& special_requests
end loop
send an e-mail to (e-mail address removed) with message equal to variable message


I already know how to send the e-mail. I'm just trying to find a way to
loop through the query results. Any ideas?

Thanks!

Nick

 

[David C. Holley]

Please don't operate any heavy machinery as I suspect that you're a bit
intoxicated. Using a recordSet object can be faster if you're doing
multiple lookups and/or dealing with multiple records

Dim rs as DAO.Recordset

Set rs = CurrentDb.Openrecordset([SQLStatement], opendbForwardOnly [or
is it dbOpenForwardOnly? can't remember]

If Not rs.EOF then
rs.close
Set rs = Nothing
else
While not rs.EOF
[message = ""]
[message = message & rs.Fields("name")]
[message = message & rs.Fields("building")]
[Send message]
Wend
rs.close
Set rs = Nothing
end if