Error 3021 appears when trying to save records...

[curtgeo]

I am having a heck of a time figuring out why I keep getting the error '3021'. "Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a record" in the following code:
Private Sub btnSaveCoupons_Click()
Dim cnn As ADODB.Connection
Dim rstTmp As ADODB.Recordset
Dim rstCoupons As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String

Dim lngClientID As Long
Dim strMessage As String


Me.txtSetFocus.SetFocus
strMessage = ""
If Nz(Me.txtRedeemedDate.Value, "") = "" Then
strMessage = strMessage & "Redeemed Date is required."
End If
If IsNull(Me.txtValue) Then
strMessage = strMessage & "Value is required"
End If
If strMessage <> "" Then
MsgBox strMessage, vbInformation, Me.Caption
Exit Sub
End If
Set cnn = CurrentProject.Connection
Set rstTmp = New ADODB.Recordset
Set rstCoupons = New ADODB.Recordset

Set cmd = New ADODB.Command


cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText


rstTmp.Open "tmpSDCCoupons", cnn, adOpenForwardOnly, adLockReadOnly
Do While Not rstTmp.EOF
strSQL = "SELECT * FROM SDCCoupons "
strSQL = strSQL & " WHERE ClientID = " & rstTmp![ClientID]
strSQL = strSQL & " AND Redeemed = False"
rstCoupons.Open strSQL, cnn, adOpenKeyset, adLockPessimistic

rstCoupons!Amount = Me.txtValue.Value
rstCoupons!Redeemed = True
rstCoupons!RestaurantID = Me.cboRestaurantLookup.Value
rstCoupons!DateRedeemed = Me.txtRedeemedDate.Value
rstCoupons.Update

strSQL = "UPDATE SDCCouponsIssued SET CountRedeemed = nz(CountRedeemed, 0) + 1 "
strSQL = strSQL & " WHERE IssuedID = " & rstCoupons!IssuedID
cmd.CommandText = strSQL
cmd.Execute

rstCoupons.Close
rstTmp.MoveNext
Loop
rstTmp.Close
Set rstTmp = Nothing
Set rstCoupons = Nothing
Set cmd = Nothing
Set cnn = Nothing

Call ClearCoupons
MsgBox "Coupons are saved.", vbInformation, Me.Form.Caption
End Sub

When I "debug" it gets hung up starting at

rstCoupons!Amount = Me.txtValue.Value
rstCoupons!Redeemed = True
rstCoupons!RestaurantID = Me.cboRestaurantLookup.Value
rstCoupons!DateRedeemed = Me.txtRedeemedDate.Value
rstCoupons.Update
I am wholly inept at solving this and humbly welcome any assistance - thank you if you can offer me any!

[pbaldy]

It sounds like rstCoupons isn't returning a record. When you're in debug mode, put this in the Immediate window:

?strSQL

which will print out the SQL so you can examine/test it. I would also start that bit with

rstCoupons.Edit

though most of my recordset experience is with DAO, so perhaps ADO doesn't require it.

[curtgeo]

Thank you! I'll give it a try!