dcount and form.currentrecord don't agree (1 Viewer)

[John Sh]

I have a function called "Buttons" that enables / disables "First, Last., Previous, Next" buttons depending on the current record.
This works fine with various tables except one.
The call to "Buttons" is in the form's current event.
The table in question has 8280 records but the form.currentrecord, after clicking the "Last" button, shows 8729 records.
The variable "db" has the name of the table to be processed.
Why is it so?

Code below.

Private Sub btnLast_Click()
    DoCmd.GoToRecord , , acLast
End Sub


Public Sub Buttons(db As String)
    Dim frm As Form
    Dim btnOKP As Boolean
    Dim btnOKN As Boolean
    Set frm = Screen.ActiveForm
    btnOKP = True
    btnOKN = True
    If (frm.CurrentRecord = 1 Or frm.NewRecord) Then
        btnOKP = False
    End If
    msgbox frm.CurrentRecord & "   " & DCount("*", db)
    If (frm.CurrentRecord = DCount("*", db) Or frm.NewRecord) Then
        btnOKN = False
    End If
    With frm
        .btnPrevious.Enabled = btnOKP
        .btnFirst.Enabled = btnOKP
        .btnNext.Enabled = btnOKN
        .btnLast.Enabled = btnOKN
    End With
End Sub
[ATTACH type="full"]105391[/ATTACH][ATTACH type="full"]105392[/ATTACH][ATTACH type="full"]105393[/ATTACH][ATTACH type="full"]105394[/ATTACH]

The images show, in order, the buttons after the "last" and "First" buttons are clicked and a message box that shows the currentrecord number after the "First" and "Last" buttons are clicked. In the first screen shot the "Last" and "Next" buttons should be disabled.
I have tried a "Compact and Repair" but the problem persists.

 

[arnelgp]

do you have Filter in effect on the form?

 

[moke123]

Is this an attempt at avoiding an error when you try to move to the next record when your at the last record? Or moving previous when at the first record?

See the attached NavBar class. You'll see it uses the recordset absolute positon to determine the position of the recordset. When you initialize the class, the second to last argument when set to true will cycle the records (ie. at the last record the next button takes you back to the first and vice versa) and if false you'll get a msgbox that you're at the first or last record.

The relevant code is in "Sub m_objbtoNext_Click()" and "Sub m_objbtoPrev_Click()"

You might be able to adapt the concept to your needs.

EDIT: I replaced the original file with a new rewritten class incorporating the disable button method as 1 of the 3 choices. The demo shows all 3 methods on 1 form.

 

[moke123]

If (frm.CurrentRecord = DCount("*", db) Or frm.NewRecord) Then

Why use the table record count when the form's recordset recordcount is available?

 

[moke123]

Hmm, Interesting.
I was just playing around with my example and added the following code to the class.

Private Sub lockMe()

    If frm.Recordset.RecordCount = frm.Recordset.AbsolutePosition + 1 Then
        Me.btonext.Enabled = False
    Else
        Me.btonext.Enabled = True
    End If
    
    If frm.Recordset.AbsolutePosition = 0 Then
        Me.btoprev.Enabled = False
    Else
        Me.btoprev.Enabled = True
    End If

End Sub

I called it in each of the button click events in the class, as well as the initialization code and it seems to do what you were looking for.

I may add this as an option in my class.

 

[John Sh]

Is this an attempt at avoiding an error when you try to move to the next record when your at the last record? Or moving previous when at the first record?

See the attached NavBar class. You'll see it uses the recordset absolute positon to determine the position of the recordset. When you initialize the class, the second to last argument when set to true will cycle the records (ie. at the last record the next button takes you back to the first and vice versa) and if false you'll get a msgbox that you're at the first or last record.

The relevant code is in "Sub m_objbtoNext_Click()" and "Sub m_objbtoPrev_Click()"

You might be able to adapt the concept to your needs.

No, it is not an attempt, but code that does the job that it was designed to do. That is to eliminate the possibility of an error message and remove the requirement for an ugly message box.
Also the cyclic order of your class is not desirable in my case.
My code, as it stands, does the job I require.
It seems as if you have missed the point of my question which is "how is the form.currentrecord showing a record number some 500 records greater than there are in the table.

 

[John Sh]

Why use the table record count when the form's recordset recordcount is available?

One, or the other, would give the same result of 8280 records.
Rather than suggest alternates when they are not necessary, please address my question.

 

[moke123]

The table in question has 8280 records but the form.currentrecord, after clicking the "Last" button, shows 8729 records.

What does the navigation bar at the bottom of your form say for recordcount?

Using the forms recordset recordcount and the .AbsolutePosition are better indicators of where you are in the forms recordset.

 

[CJ_London]

simpler code might be

Private Sub Form_Current()

    with Recordset
          btoprev.Enabled= .AbsolutePosition>0
          btonext.Enabled = .AbsolutePosition<.RecordCount-1
     end with

End Sub

with regard the rather unnecessary comment

Rather than suggest alternates when they are not necessary, please address my question.

suggest you tell us what db is, since without knowing what it is, impossible to say - I assume a table. Also need to know what recordset relates to the form - and if it is a query, what is the query sql. Nothing you have provided confirms both db and the form are using the same recordset

 

[John Sh]

suggest you tell us what db is, since without knowing what it is, impossible to say - I assume a table. Also need to know what recordset relates to the form - and if it is a query, what is the query sql. Nothing you have provided confirms both db and the form are using the same recordset

from my original post:
<The table in question has 8280 records but the form.currentrecord, after clicking the "Last" button, shows 8729 records.
<The variable "db" has the name of the table to be processed.
<Why is it so?

I do not need alternatives to my code. I need to know why a table "db" that has 8280 records according to the nav bar or any other method of counting will indicate record number 8729 via the "forms.currentrecord" method.
There are no queries, no sql, not even a recordset, just the code you see, and, yes, the form and underlying code are both using the same table.
I do not want / need different code. what I have, albeit below your standards, works.

I just want an answer to the original question.

In the past I have received valuable answers to my problems on this forum, but occasionally, as now, there seems to be a fixation on "improving" my code rather than answering the question that was posed in the O.P.
Is it too much to ask that my simple request be addressed?

I have stated, many times, that I am an 83 yo novice programmer. Sometimes I ask for suggestions to improve my code and they are gratefully received, I will have a look at the "with recordset" snippet. Other times, as now, I just need answers to a problem and I do get somewhat frustrated when all I get is code "improvement' suggestions. My code might be basic but it ain't broke so it don't need fixing!
John

 

[CJ_London]

All you are doing is describing what you have, not showing it. so far as I know a form cannot show more records than the underlying recordsource or .currentrecord to be greater than the number of records, so the implication is the form recordsource is not the same as 'db'.

'db' does not seem to be declared or a value assigned, perhaps you have in some code you have not shown us. For all we know 'db' is a table called tblCustomers whilst the form is based on tblInvoices. You are setting frm to screen.activeform - how do you know that is the form you think it is?

Your code as such is clearly not working as intended, myself and others have suggested a better way. Ignore it by all means, it is is your app. But we cannot answer your question without knowing more.

Suggest you tell us what the value of db and the value of frm.recordsource returned in your msgbox or use debug.print.

If they are the same then the implication is your db is corrupt, if not - you have your answer, your code is not doing what you think it is doing, so it needs to be modified in some way.

 

[John Sh]

It turns out the problem was twofold.
1) the table in use by the form was "Collier_collection" and the table carried by "db", assigned in the definition of sub Buttons
vis Public Sub Buttons(db As String) was "Collier Collection". Missing the "_". As stated by Watson "the obvious is the hardest to see"
2) The button "btnNext" was actually named "Nextbtn" causing an error trap in the calling current event to trigger and "Resume next".
Correcting these two typo's has fixed the problem and my code is working as required.

I thank you all for your efforts.
John

 

[CJ_London]

That’s one of the reasons not to have spaces in table and field names- less confusion

but glad you got it sorted

 

[moke123]

One of the reasons we often suggest code improvements is situations like this. Your writing code for your form. Your form has many built-in properties and events available. One of those properties is the record set record count property. It is never wrong even if you filter your form or use a complicated query as a record source. It also makes your code more portable as it's available in any bound form and less prone to human error.

Then again, If you had implemented that one suggestion, you may never have gotten the answer to your original question you were so insistent on getting.

PS. Thanks for the idea. It's a great addition to my class. Think I may add a few other visual aids other than just disabling.