Display record number and count 1

[rjoubert]

I want to display the current record number (index) and the record count on my form. I am not using the Navigation buttons at the bottom. I need to know what to use as the Control Source for an unbound text box so that it displays "Record [n] of [m]".

 

[rjoubert]

I think I've got it figured out now. This may not be the best way, so if someone has an easier way to do it, I'm all ears.

In the Current event of my form, I put the following code...

    Dim db As Database
    Dim tbl As TableDef

    Set db = CurrentDb
    Set tbl = db.TableDefs("Documents")
    
    Me.txtRecordNum.Value = "Record " & Me.CurrentRecord & " of " & tbl.RecordCount
    
    Set tbl = Nothing
    Set db = Nothing

The only problem I see with this code is that when a user clicks on the button to add a new record, the record index is 1 higher than the record count. For example, if there are 3 records in the table, and the add new record button is clicked, it displays "Record 4 of 3".

 

[fneily]

At the top or whereever, put an unbound Label control (create a label and type the letter A so it is created). Call it RecNum
Open your form in design view and go ALT+F11 to go to VBA. In the top section, put:
Option Compare Database
Option Explicit
Dim Records As DAO.Recordset
Dim TotalRecords

On the form's OnLoad event, put:
Set Records = Me.RecordsetClone
Records.MoveLast
TotalRecords = Records.RecordCount

On the form's OnCurrent event, put:
If Not Me.NewRecord Then
Records.Bookmark = Me.Bookmark
Me![RecNum].Caption = "Record " & Records.AbsolutePosition + 1 & " of " & TotalRecords
Else
Me![RecNum].Caption = "New Record"
End If

Add two more sub's:
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![RecNum].Caption = TotalRecords + 1 & " pending..."
End Sub

Private Sub Form_AfterInsert()
Records.MoveLast
TotalRecords = Records.RecordCount
End Sub

 

[rjoubert]

Hey, I said EASIER!!! Just kidding, fneily, this works great...thanks for the effort!

 

[missinglinq]

I use this rather simple code. I have the record numbers appear in the Title area of the form, but you could, of course, replace Me.Caption with the name of a label. This goes in Private Sub Form_Current()

If Not Me.NewRecord Then
     Me.Caption = "Record  " & CurrentRecord & "  Of  " &    
     RecordsetClone.RecordCount & "  Records"
Else
     Me.Caption = "Record  " & CurrentRecord & "  Of  " & 
     (RecordsetClone.RecordCount + 1) & "  Records"
    
End If

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[fneily]

Missingling, if someone inputs a new record and does not save it then your display wouldn't make sense.

 

[missinglinq]

Sure it makes sense! I've been using it for a number of years now. If the record is a new record, the code adds 1 to the current record count for DISPLAY PURPOSES ONLY! It doesn't actually change the record count! If the record is then discarded, the displayed count reverts to whatever is appropriate for the current record being displayed.

Me.Caption = "Record  " & CurrentRecord & "  Of  " &
     (RecordsetClone.RecordCount + 1) & "  Records"

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[fneily]

DISPLAY PURPOSES ONLY!" Oh, it's just for DISPLAY PURPOSES ONLY! I'd rather see a picture of my Porsche since, after all, it's for DISPLAY PURPOSES ONLY!
Actually, it's foretelling the future.
No, it doesn't make sense. "Here's the number of records that actually isn't the right count and this is the record number of a record that doesn't exist yet so the number that's shown is fictitious." Hmmm, after 6 rum and cokes, you may be right.

 

[missinglinq]

The only problem I see with this code is that when a user clicks on the button to add a new record, the record index is 1 higher than the record count. For example, if there are 3 records in the table, and the add new record button is clicked, it displays "Record 4 of 3".

My code addresses the problem rjoubert mentioned in his 2nd posting. It simply shows "Record 4 of 4" instead of "Record 4 of 3." It is the right count if the record is added. If the record is tossed it reverts back to the correct count.

After reading your post, I'm begining to understand how so many people who have had classes with "Instructors" and still have no idea about how to work with Access.

The Missinglinq

There's ALWAYS more than one way to skin a cat!