atthe end of a recordset... AbsolutePosition seems to not equal RecordCount

[markjkubicki]

on a form, with records that are sorted in the form's recordset query,


i have a command button with this code

Private Sub cmdNextType_2_Click()
    Application.Echo False
    strPrev_Current = Me.Type 'store value for action: Return To Previous Record
    Call EODbNewType(Me) 'check if next record would be a new type, prompt to add if yes
    '<...>

Public Sub EODbNewType(frm As Access.Form)
    'check if next record is a 'new' record

    If frm.RecordsetClone.RecordCount = frm.RecordsetClone.AbsolutePosition Then    
        With frm
            gsMsgTitle = "END of DATABASE"
            gsMsgText = "You are at the end of the database;" _
            & vbCrLf & "Do you want to create a new record?   "
            gsMsgResponse = MsgBox(gsMsgText, vbQuestion + vbYesNo + vbDefaultButton1, gsMsgTitle)
            If gsMsgResponse = vbYes Then
                frm.Dirty = False
                DoCmd.GoToRecord , , acNewRec
                '<...>

HOWEVR,
when i am at the last record (view = single record)

frm.RecordsetClone.RecordCount
AND
frm.RecordsetClone.AbsolutePosition

are not equal, and i cannot tell why (and previously it used to work; so i had to have...)


thnx and appreciation in advance,
m.

[CJ_London]

in what way are they not equal? i.e. what values are being returned?

and what is the purpose of this code?

[markjkubicki]

on the first attempt, both are equal
however, after opting to add a new record, then another...
the count increases, but the position says the same (even though i am at the new record, which in the sorted order is the new last record)

the intent is simple:
when the user is at last record and attempts (in a command button) to navigate to the next record, they get a notice: "you're at the last record in this project. do you want to add a new record, or stay where you are at". if the choose to add a new record, there's all sorts of code that populates into the new record based on...




BTW
is there a way to save your 'watched' expressions in VBA? every time i close the project, lock-up / freeze, or blah, blah, blah, when i reopen, they are gone and i need to redefine them (can do, just annoying.)

[CJ_London]

to find current position, better to use bookmark

not sure why your code worked before since absoluteposition starts from base 0 whereas recordcount is base 1. i.e. if the recordset has one record, the recordcount will be 1 and the absoluteposition 0. absoluteposition can never equal recordcount. So I'm somewhat confused by your comment 'on the first attempt, both are equal'

that's why I asked what the values were - unfortunately you didn't provide an answer that helps

see this link - might explain it better than I can
https://docs.microsoft.com/en-us/off...n-property-dao

[markjkubicki]

you are correct
i pasted an erroneous edit of the code...
it actually reads:

If frm.RecordsetClone.RecordCount = frm.RecordsetClone.AbsolutePosition + 1 Then

(note the '+1' )

... if they are equal, then you must be at the last record; and you are clicking the go to the next record command button (there are no more records to advance to); what do you want to do? (w/choices in a pop-up form to determine the next appropriate code records)

the first time the user presses cmdNextType (while at the last record) , the code reads that frm.RecordsetClone.RecordCount IS EQUAL TO frm.RecordsetClone.AbsolutePosition + 1 and take appropriate action for being at the last record
...if the user wants, creates a new record (which will be next in a series, and consequently, last in the sorted record set, then displays it)
but on an immediately subsequent click, the RecordCount is shown to increase (accounting for the new record), but the AbsolutePosition, now at the new record and last in the sorted recordset, does not increase.

[Welshgasman]

But that record has not been committed?, as you can press ESC and it will not exist?

[Micron]

Perhaps just If Me.NewRecord Then...?

There's no need to count anything to figure out if you're on a new record or not. That probably should go in the Current event.

[moke123]

How about toggling the AllowAdditions property and trapping the 2105 error?
Set allow additions to false. When you reach the last record and try to advance it will error.
In your error handler you fire a yes/no messagebox and take appropriate action.

something like

Private Sub Command0_Click()


    On Error GoTo Command0_Click_Error


    Me.AllowAdditions = False


    DoCmd.GoToRecord , , acNext


    On Error GoTo 0
    Exit Sub


Command0_Click_Error:


    If Err.Number = 2105 Then
        Select Case MsgBox("You are at the end of the database" & vbNewLine & "Do you want to create a new record?", vbYesNo)


        Case vbYes
            Me.AllowAdditions = True
            DoCmd.GoToRecord , , acNewRec
            Resume Next


        Case vbNo
            Exit Sub
        End Select
    End If


    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command0_Click, line " & Erl & "."


End Sub

When done make sure to reset allow additions back to false, maybe in the forms afterupdate event.

[Micron]

If going that route, watch out if using the data mode property when opening the form.

[moke123]

If frm.RecordsetClone.RecordCount = frm.RecordsetClone.AbsolutePosition + 1 Then

I wonder if it is the use of recordsetclone rather than recordset.

In my NavBar class I use

If frm.Recordset.RecordCount = frm.Recordset.AbsolutePosition + 1 Then

and have no issues with it.

Here's a sample with a few different ways to initialize it.