selecting query as recordsource in module and running until eof

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to use a module to select a query as a recordsource, then sort
through the records until it reaches the end. For each record it will
process a list of functions. The query pulls all related data from a
client and contact database. The functions will see if the data meets
the specified requirements, then send an email to the contact with
information from that record if it's a match. I have the middle part,
it's the selection of the query and the do until ???.eof that I haven't
been able to figure out.

thanks
 
I need to use a module to select a query as a recordsource, then sort
through the records until it reaches the end. For each record it will
process a list of functions. The query pulls all related data from a
client and contact database. The functions will see if the data meets
the specified requirements, then send an email to the contact with
information from that record if it's a match. I have the middle part,
it's the selection of the query and the do until ???.eof that I haven't
been able to figure out.
Click to expand...


You do not need to go the long way around by using a form's
record source to do what you described (maybe there's more
to it?).

Regardless, you need to use a recordset to loop through each
record:

Dim db As DAO.Database
Dim rs As DAO.Database
Set db = CurrentDb()
Set rs = db.OpenRecordset("queryname", . . . )
Do Until rs.EOF
'do your thing
rs.MoveNext
Loop
rs.Close : Set rs Nothing
Set db = Nothing
--
Marsh
MVP [MS Access]

P.S. Start a new thread is you need help with code to send
email.
 
Dimension a recordset first, then load the query into it and use the
..MoveNext method to work through the records sequentially. Look at the help
on the OpenRecordset arguments if this doesn't work the first time.

Dim rs As DAO.Recordset
Dim recCount As Integer
Set rs = CurrentDb.OpenRecordset("queryName", dbOpenSnapshot)
'force accurate record count
rs.MoveLast
recCount= rs.RecordCount
rs.MoveFirst 'return to first record to begin loop
Do While Not rs.EOF
'Do stuff here
rs.MoveNext
Loop
rs.Close
 
It's starting to come back to me now. I have the email function
already. I used it in many access programs I wrote about 4 years ago
in Access2000. Haven't used it since and am having to re-learn it all.

Thanks for all the help.
 
Back
Top