By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,062 Members | 1,374 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,062 IT Pros & Developers. It's quick & easy.

Custom Record Indicator not updating after requery

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.    Dim frm As Form, LastRec As Long
  4.  
  5.    Set frm = Forms!ReviewSearchRecordsForm
  6.  
  7.    If Trim(frm!txtRecordNo & "") = "" Then
  8.       Me.RecordsetClone.MoveLast
  9.       DoEvents
  10.    End If
  11.  
  12.    LastRec = Me.RecordsetClone.RecordCount
  13.    If Me.NewRecord Then LastRec = LastRec + 1
  14.  
  15.    frm!txtRecordNo = "Record " & CStr(Me.CurrentRecord) & " of " & CStr(LastRec)
  16.  
  17.    Set frm = Nothing
  18.  
  19. End Sub
  20.  
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:

Expand|Select|Wrap|Line Numbers
  1. With Recordset
  2.  
  3. If .AbsolutePosition = .RecordCount - 1 Then
  4. DoCmd.GoToRecord , , acFirst
  5. Else
  6.  
  7. DoCmd.GoToRecord , , acNext
  8. End If
  9. End With
  10.  
  11.  
  12. Dim CurrentKey As Integer
  13.  
  14. CurrentKey = [ID]
  15.  
  16. Me.Requery
  17.  
  18. Me.Recordset.FindFirst "[Id] = " & CurrentKey
  19.  
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!
Feb 28 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim frm As Form, LastRec As Long
  3. Set frm = Forms!ReviewSearchRecordsForm
  4.  
  5. If Trim(frm!txtRecordNo & "") = "" Then
  6. Me.RecordsetClone.MoveLast
  7. DoEvents
  8. End If
  9.  
  10. LastRec = Me.RecordsetClone.RecordCount <<<
  11. 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.

Expand|Select|Wrap|Line Numbers
  1. Dim RS as DAO.Recordset 
  2. ...
  3. Set RS = Me.RecordsetClone
  4. RS.Movelast
  5. LastRec = RS.Recordcount
  6. ...
  7.  
-Stewart
Feb 28 '08 #2

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     Dim rst As DAO.Recordset
  4.     Dim lngCount As Long
  5.  
  6.     Set rst = Me.RecordsetClone
  7.  
  8.     With rst
  9.         .MoveFirst
  10.         .MoveLast
  11.         lngCount = .RecordCount
  12.     End With
  13.  
  14.  
  15.     Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount
  16.  
  17.  
  18. End Sub
  19.  
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.
Feb 29 '08 #3

P: 1
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.
Mar 2 '08 #4

Expert Mod 2.5K+
P: 2,545
... 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Dim rst As DAO.Recordset
  3.     Dim lngCount As Long
  4.     Set rst = Me.RecordsetClone
  5.     If Not rst.EOF then
  6.         With rst
  7.             .MoveFirst
  8.             .MoveLast
  9.             lngCount = .RecordCount
  10.         End With
  11.     Else
  12.          lngCount = 0
  13.     Endif
  14.     Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount
  15. End Sub
-Stewart
Mar 2 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.