help please...get values from recordset based on another recordset

[Guest]

I am having trouble with the following code. I have a query [qryevalsnotsent]
to determine 1) if evaluation has been sent and 2) where it needs to be sent
to. I need help to accomplish 2 things here that has me stomped. After I run
the query 1)I need to update the emailsent field in table pending_evals based
on the evalID from query. Currently it updates all records in pending_evals
table.I believe the answer to that will answer my send goal which is to place
the values of that particular record in an email. Does that make sense? Any
help would be greatly appreciated.

Private Sub SendDetail_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer
Dim rst2 As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryevalsnotsent")
'Count of unsent e-mails
intCount = DCount("[evalID]", "[pending_evals]" _
, "[EmailSent]=0")
rst.MoveFirst
Do Until rst.EOF
strEmailAddress = rst![EmailName]
strSubject = "Evaluation from Potential Client in " & rst![eval_county]
& ", " & rst![eval_state]

strEMailMsg = rst![evalID] & " this is eval # that will be emailed."

'this is where I need the values from the pending_evals table - based on
record # from rst![evalID]

DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
'Run update to update the sent mail check box
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE pending_evals SET pending_evals.EmailSent = 1;"
DoCmd.SetWarnings True
MsgBox "All Evaluations have been sent", vbInformation, "Thank You"
End If
End Sub

 

[Steve Schapel]

Jack,

One solution would be to move the RunSQL. It could go before the
recordset loop, and happen for each email, so you would need a Where
clause, something like this...
"UPDATE pending_evals SET pending_evals.EmailSent = 1 WHERE EvalID=" &
rst!EvalID

Alternatively, if you want to do it in one hit afterwards, you will need
to run the Update based on qryevalsnotsent instead of pending_evals.