How can I dsiplay the number of records on the form (same as bottom left corner)

[UDontKnowJack]

Hi!

I would like to display the number of a record and the total numbers of records on that form. Basically what Access already does in the bottom left corner, I would like to have somewhere bigger on the form.

For example in a Text Box you are on record 3, it shows [3 of 20]

Thanks a lot !

[ADezii]

Hi!

I would like to display the number of a record and the total numbers of records on that form. Basically what Access already does in the bottom left corner, I would like to have somewhere bigger on the form.

For example in a Text Box you are on record 3, it shows [3 of 20]

Thanks a lot !

Assuming you have a Label named lblRecCount on your Form, place the following code in the Form's Current() Event:

Private Sub Form_Current()
If Me.NewRecord Then
  Me![lblRecCount].Caption = "[" & Format$(Me.CurrentRecord, "#,###") & " of " & _
                              Format$(Me.Recordset.RecordCount + 1, "#,###") & "]"
Else
  Me![lblRecCount].Caption = "[" & Format$(Me.CurrentRecord, "#,###") & " of " & _
                              Format$(Me.RecordsetClone.RecordCount, "#,###") & "]"
End If
End Sub

[UDontKnowJack]

Thanks!

Works Perfectly!

[NeoPa]

FYI. For large numbers of records the total count may not always be available immediately. If this is a problem for your design you could check the value first, and if it's not yet available then go to the last record then back again. Once the whole recordset has been traversed the values will be available.

[UDontKnowJack]

Hi,

How can i check the value first... is there a way to make it work properble at the start?

THanks

[NeoPa]

In my testing it returned a value (the number of records accessed so far) even when the whole dataset had not been accessed. From that it is hard to tell how accurate the figure actually is. If this is important to you (it seems it is) then I would compare the value to 1 immediately after opening. If <=1 then do a MoveLast() followed by a MoveFirst(). If, worst case, the recordset consists of only one record, then very little time has been wasted. It should always reflect the correct value though.

[UDontKnowJack]

Sorry my VBA knowledge is very limited. Could you be so kind putting into code and then where to squeeze it into the abouve code from ADezzi ...

Thanks a lot!

[NeoPa]

This code would go into the Form_Open() event procedure, rather than the Form_Current() one as ADezii used.

For completeness I've included versions for both :

Private Sub Form_Current()
    With Me.Recordset
        If .RecordCount <= 1 Then
            Call .MoveLast()
            Call .MoveFirst()
        End If
    End with
End Sub

Private Sub Form_Current()
    With Me
        .lblRecCount.Caption = _
            "[" & Format(.CurrentRecord, "#,##0"") & _
            " of " & _
            Format(.Recordset.RecordCount + IIf(.NewRecord, 1, 0), _
                   "#,##0") & "]"
    End with
End Sub