Update Recordset

[bblekfeld]

near the end of this code, right before the "ReceiptRequest.MoveNext" I need to say to update the recordset and set a the DATESENT field to today's date.

Since I already have the ReceiptRequest recordset defined...how do I go about updating a field in that particular record in that recordset programatically?

I have tried running an SQL UPDATE statement on the recordset and then tried the orginal table but doesn't work.

Not sure...please help! Thanks!!


Private Sub Command7_Click()
strsql = "SELECT DISTINCT * FROM tbl_AutoReceiptRequest WHERE DateAutoReceiptSent is null;"

Set ReceiptRequest = CurrentDb.OpenRecordset(strsql)

If Not ReceiptRequest.BOF And Not ReceiptRequest.EOF Then
While Not ReceiptRequest.EOF
emailto = ""

If (IsNull(ReceiptRequest!)) Or (ReceiptRequest![email] = "TBD") Then
MsgBox "No Email Address Available", vbCritical
ReceiptRequest.MoveNext
Else
requestID = ReceiptRequest![Trans_ID]
emailto = ReceiptRequest![email]
where = "[email] IS NULL"
If Not IsNull(ReceiptRequest![email]) Then
where = "Trans_ID = " & ReceiptRequest![Trans_ID] & ""
End If

strdocname = "ReceiptRequestReport"
subject = "MSDSonline Receipt"
msg = "Attached is a receipt per your request."

DoCmd.OpenReport strdocname, acViewPreview, "", where

If Not IsNull(ReceiptRequest![email]) Then
msg = msg
End If

DoCmd.SendObject acSendReport, strdocname, "SnapshotFormat(*.snp)", emailto, "", "", subject, msg, True, ""
'DoCmd.SendObject acReport, strdocname, "SnapshotFormat(*.snp)", emailto, , , subject, msg, True


DoCmd.Close acReport, strdocname, acSaveNo


ReceiptRequest.MoveNext
End If
Wend
End If

End Sub

 

[PHV]

Try something like this:
With ReceiptRequest
.Edit
!DATESENT = Date()
.Update
End With

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bblekfeld]

Thanks for the help!!...its closer but..

It gives me a "Database or Object" is read-only error.

 

[PHV]

Even if you remove the DISTINCT keyword form the SELECT clause ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bblekfeld]

That worked perfectly. One last question.

Do I need to close/clear out the recordset or anything at the end?

Thanks for your help!!