Custom Record Counter of a form

[GSevensM]

Hi all,

I have a custom record counter on a form using the below code:

Private Sub Form_Current()
If Me.NewRecord Then
Me.lblRecordCounter.Caption = _
"Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount + 1
Else
Me.lblRecordCounter.Caption = _
"Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
End If
End Sub

I think at some stage the form was saved with a filter on and this may be causing the issue. The problem I have is:

There are 1749 records. Everytime I open the form the custom record counter displays "Record 1 of 501". The built in record counter shows 1 of 1749. The moment I hit the next record arrow the custom record counter displays "2 of 1749" and if I go back again it displays "1 of 1749.".

I know it's a filter causing the problem because I have a macro that does a clear search. As soon as I hit the clear search the custom counter goes back to "1 of 501" again (even though the built in one stays at 1 of 1749).

I have Filter on Load set to No.

Anyone got any ideas as to what I am doing wrong?

 

[CJ_London]

and is the form filter property empty?

 

[MarkK]

Nothing. This behaviour is "by design." A recordset doesn't necessarily load all it's records. You can force it to do so by moving to the last record.

What you can try is use a clone of the recordset in your form, and move to the last of that, like . . .

Private Sub Form_Current()
   dim count as long
   With Me.RecordsetClone
[COLOR="Green"]      'here you force the clone to load all it's records[/COLOR]
      .movelast
[COLOR="Green"]      'and then call .RecordCount[/COLOR]
      count = .recordcount
   End With

   If Me.NewRecord Then count = count + 1

   Me.lblRecordCounter.Caption = _
      "Record " & Me.CurrentRecord & " of " & count
End Sub

Hope that helps,

 

[GSevensM]

and is the form filter property empty?

It is. It works fine on all other forms except this one so I think it is a filter that has saved somehow but I certainly can't find any reference to it anywhere!

 

[GSevensM]

Nothing. This behaviour is "by design." A recordset doesn't necessarily load all it's records. You can force it to do so by moving to the last record.

What you can try is use a clone of the recordset in your form, and move to the last of that, like . . .

Private Sub Form_Current()
   dim count as long
   With Me.RecordsetClone
[COLOR=green]     'here you force the clone to load all it's records[/COLOR]
      .movelast
[COLOR=green]     'and then call .RecordCount[/COLOR]
      count = .recordcount
   End With
 
   If Me.NewRecord Then count = count + 1
 
   Me.lblRecordCounter.Caption = _
      "Record " & Me.CurrentRecord & " of " & count
End Sub

Hope that helps,

I don't know a great deal about VBA so am trying to work out exactly what your code above did but it definitely solved my issue so thank you very much!

 

[MarkK]

You're welcome.

The code opens a clone of the Form's recordset, and moves to the last record in the clone, which is how you guarantee the recordset has loaded ALL its records. Then we save the .RecordCount, and do what your code did before, but with the accurate "count."

 

[GSevensM]

OK, so I may need to post this in a new thread as the problem is solved but since adding the code it's created a new problem! Now if I use the search box I have to find a record I get the "No Current Record: Run-time Error: 3021" pop up. If I revert back to the old code it doesn't.

Is that because it's created a clone and not actually referencing the data in the tables?

 

[MarkK]

What is "the search box?" On what line does this error occur?

 

[GSevensM]

Hi Mark,

So I realise now I wasn't clear in my previous post. The search box only returns that error message if the search criteria can't match anything in the database.

The search box is just an apply filter macro where the condition is "[FullName] Like "*" & [Forms]![frmEmployeeInformation]![txtSearchEmployeeName] & "*"

 

[MarkK]

And the error occurs at a line in the code? In the code I posted? Maybe we need to check if the cloned recordset it empty first???

 

[GSevensM]

The error occurs at .MoveLast within the code.

Private Sub Form_Current()
Dim count As Long
With Me.RecordsetClone
'here you force the clone to load all it's records
.MoveLast
'and then call .RecordCount
count = .RecordCount
End With
If Me.NewRecord Then count = count + 1
Me.lblRecordCounter.Caption = _
"Record " & Me.CurrentRecord & " of " & count
End Sub

 

[MarkK]

So in this case the recordset is empty. Check if .RecordCount is zero before running .MoveLast.

Private Sub Form_Current()
   dim count as long
   With Me.RecordsetClone
      [B][COLOR="DarkRed"]if .recordcount > 0 then [/COLOR][/B].movelast
      count = .recordcount
   End With
 
   If Me.NewRecord Then count = count + 1
 
   Me.lblRecordCounter.Caption = "Record " & Me.CurrentRecord & " of " & count
End Sub

 

[GSevensM]

Perfect Mark. Thanks very much! You've actually inspired me to start learning VBA in depth because it really does open up what you can do with an Access database.

 

[MarkK]

You're welcome, yeah, being able to code stuff gives you a very powerful tool when working with data, no question.