Record Count

[Rusty]

Hello there people,

I am trying to set up a couple of fields that will count the number of records on a form. (This together with some custom navigation buttons will help the users here see things a little clearer when they are moving between records).

I have used the code below and there are 5 records in total, but when the form is first opened the field [navRecNo] contains "1" and the field [navRecCount] contains "1". It's only when you go to the next record, or go to add a new record that the [navRecCount] actually contains the true number of total records, i.e. 5.

Am I missing something here??

Rusty

=========================================
Private Sub Form_Current()

Me.navRecNo = Form.CurrentRecord
Me.navRecCount = Form.RecordsetClone.RecordCount + IIf(Form.NewRecord, 1, 0) & " "

End Sub

 

[namliam]

getting the number of records in a recordset need only be done once (when opening the form) then add 1 when adding a record (using a public variable)

try adding: me.recordsetclone.movelast
before getting the recordcount....

Regards

 

[Rich]

Are we talking about a main or subForm?

 

[Oldsoftboss]

Hope this helps.

Dave

 

[ghudson]

This works for me using a label named "lRecordXofY" in the forms OnCurrent event...

Private Sub Form_Current()
    
    If Me.NewRecord Then
        Me.lRecordXofY.Caption = "New Record (" & Me.RecordsetClone.RecordCount & " existing records)"
    Else
        Me.lRecordXofY.Caption = "Record " & [CurrentRecord] & " of " & Me.RecordsetClone.RecordCount
    End If
    
End Sub

Add this to the forms OnOpen event...

Private Sub Form_Open(Cancel As Integer)

    RecordsetClone.MoveLast
    RecordsetClone.MoveFirst

End Sub

 

[Rusty]

Add this to the forms OnOpen event...

Private Sub Form_Open(Cancel As Integer)

    RecordsetClone.MoveLast
    RecordsetClone.MoveFirst

End Sub

Cheers ghudson, that worked a treat!

Rusty

 

[Rusty]

How does this work in a sub form?

Private Sub Form_Current()
    
    If Me.NewRecord Then
        Me.lRecordXofY.Caption = "New Record (" & Me.RecordsetClone.RecordCount & " existing records)"
    Else
        Me.lRecordXofY.Caption = "Record " & [CurrentRecord] & " of " & Me.RecordsetClone.RecordCount
    End If
    
End Sub

Add this to the forms OnOpen event...

Private Sub Form_Open(Cancel As Integer)

    RecordsetClone.MoveLast
    RecordsetClone.MoveFirst

End Sub

How do I get this code to work when the form is then used as a subform (within another form). Is there similar code to place in the 'parent' form?

Rusty

 

[Rusty]

Are we talking about a main or subForm?

It's a subform

 

[ghudson]

How do I get this code to work when the form is then used as a subform (within another form). Is there similar code to place in the 'parent' form?

Rusty

I have never tried that but I imagine that you should be able to do what you want by placing the code and label inside the subform's form.

 

[chewbm]

Just creat a text box and set the control source to
=IIf([CurrentRecord]>(Count(*)),'New Record',('Record ' & [CurrentRecord] & ' of ' & Count(*)))