Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2014
    Posts
    37

    Unanswered: Error 3021: No Current Record

    The Situation: I have a form with a continuous subform in which the user inputs count data on. I have a 15 button programmable keypad which the user can use as a manual counter to increment counts column. Press button 1 increment record 1 count by 1. Press button 12, increment record 12 count by 1.

    I created a Subroutine that does this which is activated on the KeyUp Event. It seems to function Ok.

    There are times that counting can happen quickly. The user may want to add 10 to the count of one thing and 3 to the count of another and its easier to use the keypad than do the additions on the the screen. BTW, much of this counting happens when the USER is not looking at the screen at all, so it needs to be consistently successful or provide meaningful feedback for a failure.

    The Problem: While I am testing this I sometimes receive an Error 3021: No Current Record when moving from one record to another via the keypad on a keyup event. (ie press button 1 5-times fast, then press button 5 fast and the error is triggered) If the shift is slow, it appears to work fine.

    I am guessing this is happening because the record has not completely refreshed from the last keyup event.

    The Questions: Am I missing something here? Should I/Can I have Access hold the keypress event until after the refresh is complete? Do I trap it and just do a beep to let the user knows that the keypress was unsuccessful? (YUCK)

    Am I doing this so inefficiently that Access is just throwing its hands up and saying No?

    Your help would be appreciated.
    Thanks.


    Code:
    Sub TallyCounter(CountsForm As String, KeyCode As Integer, Shift As Integer)
    
        Dim i As Long
        Dim frm As Form
        Dim RS As DAO.Recordset
        Dim strSQL As String
        Dim TargetRecord As Integer
        
        If (Shift = 7 Or Shift = 3 Or Shift = 6) And (KeyCode >= 65 And KeyCode <= 90) Then
            'MsgBox Chr(KeyCode) & "(" & KeyCode & "), " & Shift, vbOKOnly, "keyup" 'For Debugging
    
            'Find the number of Records we want to move from beginning of recordset. (recno - 1)
            Select Case Shift
                Case 7                                                    'ctl-alt-shift-A to ctl-alt-shift-Z
                    TargetRecord= KeyCode - 65             
                Case 6                                                    'ctl-alt-shift-A to ctl-alt-Z
                    TargetRecord= (KeyCode - 65) + 26    
                Case 3                                                    'ctl-shift-A to ctl-shift-Z 
                    TargetRecord= (KeyCode - 65) + 52    
            End Select
            
            'Clean up dirty record
            If Screen.ActiveForm.Dirty Then Screen.ActiveForm.Dirty = False
    
            
            ' Get the form and its recordset.
            'Set frm = Me.Form
            Set RS = Forms(CountsForm)!SubsampleDataSubModFrm.Form.RecordsetClone
            
            ' Move to the first record in the recordset.
            RS.MoveLast
            RS.MoveFirst
            
            ' Move to the selected record.
            If RS.RecordCount > (TargetRecord) Then
                RS.Move (TargetRecord)
                Forms(CountsForm).SubsampleDataSubModFrm.Form.Bookmark = RS.Bookmark
                'Forms(CountsForm).SubsampleDataSubModFrm.Form.ScientificName.SetFocus
            
                
            
                'Debug.Print rs.Fields("Count")
                RS.Edit  '<<<------------------NO CURRENT RECORD...SOMETIMES
                RS.Fields("Count") = RS.Fields("Count") + 1
                RS.Update
    
                'Update AuditTrail
                strSQL = "Insert into tblAuditTrail ([DateTime], [UserName], [FormName], [PK-FieldName], [PK-Value], [Action],[FieldName], [OldValue], [NewValue]) Values (#" & _
                            Now() & "#, '" & _
                            fOSUserName() & "', " & _
                            "'SubsampleDataSubModFrm', " & _
                            "'RecNo', '" & _
                            RS.Fields("RecNo") & "', " & _
                            "'EDIT', " & _
                            "'Count', " & _
                            RS.Fields("Count") - 1 & ", " & _
                            RS.Fields("Count") & ");"
    
               CurrentDb.Execute strSQL, dbFailOnError
    
            Else
                Beep
            End If
                    
            Set RS = Nothing
            'Set frm = Nothing
            KeyCode = 0
        End If
    End Sub

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    At some point you moved beyond the end of the rst.
    REMOVE THE CODE
    RS.MoveLast
    RS.MoveFirst

    the rst ALWAYS starts a the 1st record.

  3. #3
    Join Date
    Sep 2014
    Posts
    37
    Thanks for the response! I haven't yet to figured this out... more playing with the code and i think the error may be generated by the prior line:

    Forms(CountsForm).SubsampleDataSubModFrm.Form.Book mark = RS.Bookmark

    Because, on a break, the value of rs.bookmark gives me the "no current record" result.



    As far as the .movelast / .movefirst goes, I thought you needed that if you wanted an accurate .recordcount which is used in the lines following those statements. Am I mistaken?

    Is this link relevent? http://social.msdn.microsoft.com/For...orum=accessdev

    Quote Originally Posted by ranman256 View Post
    At some point you moved beyond the end of the rst.
    REMOVE THE CODE
    RS.MoveLast
    RS.MoveFirst

    the rst ALWAYS starts a the 1st record.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. I partly agree with ranman, unless you need to determine the number of rows in the recordset immediately after opening it, this is useless:
    Code:
            ' Move to the first record in the recordset.
            RS.MoveLast
            RS.MoveFirst
    .
    2. I can see 2 possible reasons for the error you get:

    a) The recordset is huge (many rows) or the system is slow for any reason and the .Move method has not yet reached the desired record. It's not very likely but I would try:
    Code:
            ' Move to the selected record.
            If RS.RecordCount > (TargetRecord) Then
                RS.Move (TargetRecord)
                Forms(CountsForm).SubsampleDataSubModFrm.Form.Bookmark = RS.Bookmark
                DoEvents
                RS.Edit  '<<<------------------NO CURRENT RECORD...SOMETIMES
                RS.Fields("Count") = RS.Fields("Count") + 1
                RS.Update
    b) There is no current record after using the .Move method. In such a case no error is raised but the .BOF or the .EOF property of the recordset is set to True, meaning that there is no current record. If you then try to access the current record (that does not exist), using the .Edit method for instance, an error "No current record" will be raised. I would then test the .BOF and .EOF properties before trying to edit the current record:
    Code:
            ' Move to the selected record.
            If RS.RecordCount > (TargetRecord) Then
                RS.Move (TargetRecord)
                If RS.Bof = False And RS.Eof = False Then
                    Forms(CountsForm).SubsampleDataSubModFrm.Form.Bookmark = RS.Bookmark
                    RS.Edit
                    RS.Fields("Count") = RS.Fields("Count") + 1
                    RS.Update
                Else
                    ' No current record: handle the case.
                End If
    
    Have a nice day!

  5. #5
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    Then it sounds like the bookmark. Could be its not in the rst.

  6. #6
    Join Date
    Sep 2014
    Posts
    37
    Determined that it is actually the line...

    Forms(CountsForm).SubsampleDataSubModFrm.Form.Book mark = RS.Bookmark

    ... i have no idea why.
    All i want is for the cursor to move to the same record that is being modified... ugh

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Isn't it a primary key you could use to find the desired record?
    Have a nice day!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    when is this code firing, what event
    is it possible you've placed the code in the wrong/inappropriate event

    set a public variable in each event, and then display that variable when the code crashes, using the immediate window
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Sep 2014
    Posts
    37
    Quote Originally Posted by healdem View Post
    when is this code firing, what event
    is it possible you've placed the code in the wrong/inappropriate event

    set a public variable in each event, and then display that variable when the code crashes, using the immediate window
    It is called from only two events.

    the Keyup() event for the main form and the Keyup() event from the subform. I have individually commented out the line calling the function from each of them and gotten the same result when called from either location. At the same time I have commented them both out and the function is not called.

  10. #10
    Join Date
    Sep 2014
    Posts
    37
    Quote Originally Posted by Sinndho View Post
    Isn't it a primary key you could use to find the desired record?
    I am sorry, but I am not sure I understand your comment. Can you clarify it for me? What I am trying to do is have the button on the keypad move the .recordsetclone to the appropriate record and add one to the count. (all of that works.) Then, I want to move the cursor on the screen/form to the same record.

    If I understand the process correctly (absolutely not a given), I am trying to synchronize the form's record set and the recordsetclone recordset. Is there a better way to do this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •