Record Count Error - RecordCount in Microsoft Access


	Back to RecordCount Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Record Count Error

Robert Stott

3 years ago

Record Count: I use a set of Record Navigation (First, Last, Next, Previous, Add & delete) buttons on my record input forms. Last week I watch your Record Count TechHelp Video and though it would interesting to have count displayed between Previous and Next buttons; the button functions are code in VBA not Macros.
I did and every worked great; then I need to delete a record and got an Error ' Run-time error '3167' Record is deleted. This took me to Form_Current sub with RecCountBox = Me.Recordset.AbsolutePosition + 1 & " of " & Me.Recordset.RecordCount - Highlighted.
I think this has something to with the total record being decreased by 1, but I have no idea how to approach this problem.

I am also trying figure out how to make Public Function for the Buttons. I just copy and past them into the new form and make sure they are all named correctly and they I copy the code and past the code. The code is simple 6 sub routines (1 for each button) but so far I have not had much luck. Do you cover anything on this topic in your training classes?

	Kevin Robertson @Reply 3 years ago
Have you tried requerying the recordset? Requery in Place

Robert Stott OP @Reply

3 years ago

I have tried Requery but no luck
This is the Code that causing the problem:
Private Sub Form_Current()
    Me.Refresh
    RecCountBox = Me.Recordset.AbsolutePosition + 1 & " of " & Me.Recordset.RecordCount

'Refreshes Button Status when Form is opened and moving from record to record

    If Me.NewRecord Then
        Exit Sub
    Else
'        MsgBox = "On Current"
       EnableDisableButtons
       DBEnableDisableButtons
    End If
End Sub

Even though there is an error the record gets deleted so I need to deal with deleting a record which intern reduces the record count.

I've done some trial and error analysis and tried to research online; I put MsgBox's in every sub that is envolved in this process and comes down to Form_Current, but it's all leading no where.

Robert Stott OP @Reply

3 years ago

I ended up writing a whole new subroutine that handles the deleted record; I had some help!

Private Sub btnDeleteRecord_Click()

    Dim rst As Recordset
    Dim strBookmark As String

    On Error GoTo Err_btnDeleteRecord_Click

    Set rst = Me.RecordsetClone
   rst.MoveNext
        If Not rst.EOF Then                 ' if not end-of-file
            strBookmark = rst.Bookmark      ' ...save the next record's bookmark
            rst.Bookmark = Me.Bookmark      ' ...go back to the record to delete
            rst.Delete                      ' ...delete the record
            Me.Requery
            Me.Bookmark = strBookmark       ' ...and return to the saved bookmark
        Else
            rst.Delete                      ' ...just delete the record
            Me.Requery
            rst.MoveLast                    ' ...move to the last record
            Me.Bookmark = rst.Bookmark      ' ...position the form to it
        End If

Exit_btnDeleteRecord_Click:
        Exit Sub

Err_btnDeleteRecord_Click:
        MsgBox Err.Description
        Resume Exit_btnDeleteRecord_Click
End Sub

This thread is now CLOSED. If you wish to comment, start a NEW discussion in RecordCount.

Next Unseen

New Feature: Comment Live View