Results 1 to 3 of 3
  1. #1
    curtgeo is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    4

    Error 3021 appears when trying to save records...

    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!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    curtgeo is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2012
    Posts
    4
    Thank you! I'll give it a try!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Run Time Error 3021 - Access 2002 SP3
    By alpinegroove in forum Programming
    Replies: 9
    Last Post: 01-24-2012, 04:38 PM
  2. Save Error
    By srmezick in forum Access
    Replies: 4
    Last Post: 11-15-2011, 10:57 AM
  3. Replies: 8
    Last Post: 05-16-2011, 06:01 PM
  4. Runtime Error 3021
    By paddon in forum Programming
    Replies: 12
    Last Post: 03-14-2011, 12:14 PM
  5. Replies: 3
    Last Post: 03-30-2010, 08:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums