Subform textbox record count

Question

Hello, I have a form [frmShiftDay] with a subform [frmShiftMachinesSubform] and I needed a record counter on the subform to display “Machine # of #” which it does with the VBA below BUT if I close the DB and open it then the textbox [txtMachineCount] displays “Machine 1 of 1” and if I hit my next button then it says the correct number of records. I tried to requery the textbox from the main forms on current event and have had no luck. How can I get it to show the correct number without selecting my next button to refresh it? Thanks!

 Private Sub Form_Current()

'-------------------------------------------------------------------------------------------------

'  Counts and displays number of records or machines you have entered for the shift

'--------------------------------------------------------------------------------------------------

If Not Me.NewRecord Then

Me.txtMachineCount = "Machine " & CurrentRecord & "  Of  " & RecordsetClone.RecordCount & ""

Else

Me.txtMachineCount = "Machine  " & CurrentRecord & "  Of  " & (RecordsetClone.RecordCount + 1) & ""

End If

End Sub

Answer 1

No it is not. Test it out and you will see it depends on records to be there. It will fail if there are 0 records: you cannot Movelast when RecordCount=0.

RecordCount+1: that seems like a really bad idea.

The other comment is more stylistic: I don't like:

if not x then

  'do this

else

  'do that

endif

I would rewrite as:

if x then

  'do that

else

  'do this

endif

Answer 2

Tom, the below code works but is it written correctly? Thanks!

Private Sub Form_Current()

'--------------------------------------------------------------------------------------------------

'  Counts and displays number of records or machines you have entered for the shift

'--------------------------------------------------------------------------------------------------

With Me.RecordsetClone

.MoveLast

If Not Me.NewRecord Then

Me.txtMachineCount = "Machine " & CurrentRecord & "  Of  " & RecordsetClone.RecordCount & ""

Else

Me.txtMachineCount = "Machine  " & CurrentRecord & "  Of  " & (RecordsetClone.RecordCount + 1) & ""

End If

End With

End Sub

Answer 3

You will need to MoveLast on your RecordsetClone. It is lazily loaded for performance reasons so the RecordCount may be nonzero, but not necessarily the right number.