Requery question

[ptpaton]

Does anyone know how to prevent a form from jumping to the first record of a dataset after requerying the underlying query? I've tried requerying specific control boxes and entire forms, only for it to either not work or get the usual "object is not open" message.

This is a general question, but in all cases I'm using a form based on a query, and I'm leaving the Control expression blank in the requery method. I'm also requerying from a macro. Is there a workaround for this?

Thanks,


-Patrick

Nine times out of ten, the simplest solution is the best one.

 

[RickSpr]

I don't think you can actually prevent it from repositioning to the first row.

The repositioning happens because it's possible that many records have been updated by other users in a way that completely rearranges the records, so that a record number or bookmark would no longer refer to the original record. In fact, it's possible that the current record has been deleted, or that every field of the current record has been changed since you first retrieved it, so that the formerly current record isn't even in the recordset any more.

Access positions to the first record, then, because there is no guarantee that the formerly current record can even be found. In practice, though, it usually will still exist.

So here's a VBA workaround that assumes the original record still exists:
1. Save the key field(s) of the current record in local variables.
2. DoCmd.Echo False to prevent the screen flickering.
3. Requery the form.
4. Use the RecordsetClone (Access 97) or Recordset (later versions) property to FindFirst the original record using the saved key field(s). If no record is found, skip to step 6.
5. If you used RecordsetClone, assign its Bookmark property to the Form's Bookmark property.
6. DoCmd.Echo True to restore screen updating.

(Note: If you're using Access 97 and aren't familiar with RecordsetClone, you need to assign it to a Recordset variable and use the variable's properties and methods. Don't access the properties and methods of RecordsetClone itself. The only thing you should normally do with RecordsetClone is assign it to a variable.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein