Custom Record Indicator not updating after requery

[aaronyoung]

I have created custom navigation buttons and Record Number indicators on several forms that are used to review and update records based on a query.

My On Current event to update the "Record X of Y" is

Private Sub Form_Current()

   Dim frm As Form, LastRec As Long
   
   Set frm = Forms!ReviewSearchRecordsForm
           
   If Trim(frm!txtRecordNo & "") = "" Then
      Me.RecordsetClone.MoveLast
      DoEvents
   End If
   
   LastRec = Me.RecordsetClone.RecordCount
   If Me.NewRecord Then LastRec = LastRec + 1
   
   frm!txtRecordNo = "Record " & CStr(Me.CurrentRecord) & " of " & CStr(LastRec)
   
   Set frm = Nothing
        
End Sub

This works as it should when using the custom navigation buttons, which I use to force the user to confirm the desire to save the record if changes have been made, and then to re-query the table, thereby no longer showing the records that have been 'finalized' based on certain fields being pouplated, and then return to the next record that would have been displayed or, if at the last record, go to the first record. To accomplish this, I have the following as part of my On Click event:

With Recordset

If .AbsolutePosition = .RecordCount - 1 Then
DoCmd.GoToRecord , , acFirst
Else

DoCmd.GoToRecord , , acNext
End If
End With


Dim CurrentKey As Integer

CurrentKey = [ID]

Me.Requery

Me.Recordset.FindFirst "[Id] = " & CurrentKey

As i mentioned, I have similar functions on several forms, but for some reason only 1 of them is not working properly after the code above is executed. On the forms working correctly, if you were originally on record 4 of 10, and the changes you made now 'finalized' the record, after the requery the Record indicated will display Record 4 of 9 (or 3 of 9 if you used the Previous Record button, which has similar code).

On the form that isn't working, if you were on Record 4 of 10 and updated the record, after the requery it would state Record 4 of 4 (or 3 of 3 w/ previous). Navigating to another record will then cause the box to update correctly, i.e. Record 5 of 9 when you go to next.

The code is the same on all of the forms. The Enabled and Locked properties are set to Yes on all forms.

Does anyone have any idea what else I should check to determine why one of the forms in not updating properly when the others are?

Thanks in advance for your help!

[Stewart Ross]

...

Private Sub Form_Current()
Dim frm As Form, LastRec As Long
Set frm = Forms!ReviewSearchRecordsForm
 
If Trim(frm!txtRecordNo & "") = "" Then
Me.RecordsetClone.MoveLast
DoEvents
End If
 
LastRec = Me.RecordsetClone.RecordCount <<<
If Me.NewRecord Then LastRec = LastRec + 1

...

Hi Aaron. I think you've been lucky with the other forms so far. From the line I've flagged <<< above you are referring to the RecordCount property of the cloned recordset without forcing Access to count the records by doing a movelast. The previous movelast in the If won't help you, because if it is executed at all it is on that instance of the recordsetclone, which is not the same as the next instance in the line flagged <<<. Each time you use RecordsetClone you get a new copy of the current recordset - not an updated copy of the same recordset. With that in mind I suggest that you explicitly declare a recordset variable and set that to the recordset clone rather than referring to recordsetclone in multiple places.

[CODE=vb]Dim RS as DAO.Recordset
...
Set RS = Me.RecordsetClo ne
RS.Movelast
LastRec = RS.Recordcount
...
[/CODE]
-Stewart

[aaronyoung]

Thanks for the explaination. You got me on the right track, and explained why my method wasn't working. The code I ended up with is

Private Sub Form_Current()

    Dim rst As DAO.Recordset
    Dim lngCount As Long

    Set rst = Me.RecordsetClone

    With rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
    End With
  

    Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount
        

End Sub

I did have some other issues with errors, one I tried to use this code, but it was because I did not have the Microsoft DAO 3.6 Object Library referenced in my VBA. The custom record indicator now works as it should, and I appreciate your helping me get on the right track.

[KerryAnders14]

Hello,

I really appreciate what you do here.

Your solution worked (mostly) for me, also.
I am using a subform with your code, however. I seem to have a problem when the subform gets to the end of the records in it's table. I get a 3021 run time error at that point, and the debugger highlights the ".movefirst " line.

Is there a problem using your code with a subform?

Thank you in advance.

[Stewart Ross]

... I seem to have a problem when the subform gets to the end of the records in it's table. I get a 3021 run time error at that point, and the debugger highlights the ".movefirst " line. ...

Hi Kerry. Problem arises because the subform recordset is empty, and the code is not explicitly checking for this end of file condition. An updated version which does act correctly for EOF is shown below, and implementing this should resolve the error you are getting.
[Code=vb]Private Sub Form_Current()
Dim rst As DAO.Recordset
Dim lngCount As Long
Set rst = Me.RecordsetClo ne
If Not rst.EOF then
With rst
.MoveFirst
.MoveLast
lngCount = .RecordCount
End With
Else
lngCount = 0
Endif
Me.txtRecordNo = "Record " & Me.CurrentRecor d & " of " & lngCount
End Sub[/Code]
-Stewart