473,320 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Custom Record Indicator not updating after requery

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
4 3453
Stewart Ross
2,545 Expert Mod 2GB
...
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
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
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
Stewart Ross
2,545 Expert Mod 2GB
... 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

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

Similar topics

6
by: DebbieG | last post by:
I have created a database for a client and was told that it was to be a one-user database. Well, you know the next statement ... now they want 3 people to be able to use the database. (FYI, I...
20
by: MS | last post by:
Access 97 I want to requery the data being displayed on a form, then I want to return to the record I was in. Why doesn't this code work? Private Sub CmdRefsh_Click()
22
by: Br | last post by:
First issue: When using ADPs you no longer have the ability to issue a me.refresh to save the current record on a form (the me.refresh does a requery in an ADP). We usually do this before...
22
by: RayPower | last post by:
I'm having problem with using DAO recordset to append record into a table and subsequent code to update other tables in a transaction. The MDB is Access 2000 with the latest service pack of JET 4....
6
by: MarkoBBC | last post by:
Hi everyone, First a brief description of my form: I have a subform within a main form. In my subform, I have a listbox displaying address information by firm name. A user first has to select a...
4
by: Bad Horsey | last post by:
Windows XP MS Access 2000 Hello folks. First timer here. Have a problem with a form. I am trying to open a report using a button on a form. The report will use information from a table...
10
by: sara | last post by:
Hi - I have been struggling with solution ideas for this now for almost 2 weeks, and have not been able to figure this out. I have a user who creates a Purchase Order (tblPOData). In some...
2
by: Arnold | last post by:
Hi Gurus, I am getting the error 3420 "object invalid or no longer set" at in the line -- rst.FindFirst "=" & NextPK --in the code below. I simply have a continuous main form that is, by...
23
by: csolomon | last post by:
Hello: I have a form that allows me to add new records. In the onCurrent event, I have: Private Sub Form_Current() 'Requery Job Number Me.cbojobNumber.Requery 'Requery DM_Mix Combo...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.