3

Making a form in Access 2010. I'm trying to make a button that moves to the next record (or the first if it's at the end), but because I want to account for other users' updates to the data set that have occurred in the meantime, I'm requerying the form before going to the next record.

I'm using the following code, adapted from this SO post:

Private Sub NextRec_Click()

Dim currentID As Long

currentID = Me.id.Value


'Here is where the requery brings the form back to the first record
Me.Requery


With Me.RecordsetClone
    .FindFirst "id=" & currentID
    If Not .NoMatch Then
       If Me.Dirty Then
          Me.Dirty = False
       End If
       Me.Bookmark = .Bookmark
    End If
End With

If Me.CurrentRecord < Me.Recordset.RecordCount Then
    DoCmd.GoToRecord , , acNext
Else
    DoCmd.GoToRecord , , acFirst
End If


End Sub

It's working fine, except that .requery causes the form to briefly return to the first record before going back to the current record and then on to the next record. I don't want it to do this--is there any way I can just keep the current record showing in the form while .requery takes place, instead of showing the first record for the split second while .FindFirst is looking for the record at CurrentID?

Community
  • 1
  • 1
sigil
  • 9,370
  • 40
  • 119
  • 199
  • 1
    What kind of recordset are you using that you have to requery? If you are going to this much trouble, why not select one record at a time? – Fionnuala Feb 10 '12 at 21:52
  • I'm adding records to the database using INSERT statements run from an Excel Userform. It looked like they weren't being added to the recordset, e.g. if my last record was ID# 11, and then I added one from my Excel sheet, and pressed the Next button, the form just goes to a new record, it doesn't go to the one that should have been just added. I'll look again and see if it's still exhibiting this behavior. – sigil Feb 10 '12 at 23:01
  • ok, I've just checked, and while the table that the Form is based on is being updated, the Form is not. If I'm at the last record, and I add a new record from my Excel userform, pushing the go-to-last record button just keeps it at the same record and doesn't go to the new one that the Userform just inserted. – sigil Feb 10 '12 at 23:06
  • Why are you adding records using Excel? If you really must use Excel, how are you connecting to Access? – Fionnuala Feb 10 '12 at 23:19
  • Connecting through the Access Database Engine Object. Some of our users don't have Access installed or aren't comfortable with it. – sigil Feb 10 '12 at 23:23
  • Also, it isn't because of Excel. I just opened another copy of the Access front end and added a new record to the table. The form did not change. – sigil Feb 10 '12 at 23:27
  • They do not need a full version of Access, the runtime for Access 2010 is free. They need not feel they are using Access with a properly designed user-interface. If you must stick with Excel, you are probably better presenting the records one by one with all navigating done by code, after all, next is what ever you define it to be. – Fionnuala Feb 10 '12 at 23:31
  • 1
    let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7548/discussion-between-remou-and-sigil) – Fionnuala Feb 10 '12 at 23:32

3 Answers3

7

Requery the recordset, not the form itself:

Me.Requery  '<-- resets the current record to the first one in the recordset
Me.Recordset.Requery  '<-- doesn't affect the current record
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
3

Sorry but I am strongly against those programmers who reinvent a NEXT button that is already provided.
If you need to do things when the user moves to the next record, use the OnCurrent event. This way you will also trap Page Down keys that users are entitled to use.

For me that code is unnecessary and a waste of resource. The recordset IS automatically updated every n seconds by Access (as specified in the refresh interval).

Is is true that deleted records stay there and appear as #deleted# and new records, might not appear unless you requery. But this is rarely a problem, and requery is really heavy and unfriendly.

Edit, after seeing @sigil's own reply:
Just an idea to reduce the number of recordset refreshes, which seem important to you: you might compare Me.Recordset.RecordCount with dCount("*", Me.RecordSource) and only requery when those differ.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • As I wrote above, the database is not being refreshed. Right now it's set to 60 secs refresh (for ODBC as well as local, I thought that might make a difference b/c the tables are in a linked back end) and the new records are _not_ showing up unless I requery. – sigil Feb 10 '12 at 23:21
  • It is true that the ODBC refresh does not show new records http://msdn.microsoft.com/en-us/library/aa141386(v=office.10).aspx – Fionnuala Feb 11 '12 at 00:05
1

So, as Remou wrote in the comments under iDevlop's answer, ODBC refresh doesn't show new records. I suspect that is the issue here because the form is getting its data from a linked table.

I'll still need to have my own Prev/Next buttons because I want to be able to loop back to the first record when I pass the last record. But I've modified the code; instead of requerying every time, I check to see if the record is the last one, and only requery when I click Next on the last record, or Prev on the first. This solves the problem adequately.

Modified code for NextRec_Click as follows:

Private Sub NextRec_Click()

Dim currentID As Long

currentID = Me.id.Value

If Me.CurrentRecord = Me.RecordsetClone.RecordCount Then
    Me.Requery
    With Me.RecordsetClone
        .FindFirst "id=" & currentID
        If Not .NoMatch Then
           If Me.Dirty Then
              Me.Dirty = False
           End If
           Me.Bookmark = .Bookmark
        End If
    End With


    If Me.CurrentRecord < Me.Recordset.RecordCount Then
        DoCmd.GoToRecord , , acNext
    Else
        DoCmd.GoToRecord , , acFirst
    End If
Else
    DoCmd.GoToRecord , , acNext
End If

End Sub
sigil
  • 9,370
  • 40
  • 119
  • 199