Reposition - requery with positioning to the recent record
Published:
Browse All Articles > Reposition - requery with positioning to the recent record
Hi,
one of the surely most often used methods of a form is the method "Requery". As commonly known Access always positions back to the first record when using this method. An alternative up to a certain limit is the method "Refresh" which stays on the record where you were because it only refreshes the already available records but doesn't show the otherwise deleted or inserted records.
"Refresh" also fails in ADPs if you assign a recordset manually - "Refresh" works then in the same way as "Requery".
Commonly known is also that you can of course return to the recent record using "Find"/"FindFirst" (ADO/DAO). The problem here is that the record is then always the first record on the form. So if you were at the middle of the form with your recent record and you use Requery/Find to go back to the recent record the record is now at the top of the form which is very annoying.
When you search for solutions for this problem you will find solutions which tries to use the scrollbar position using Windows APIs to find the scrollbar of the form and return the current position and then, after requery, tries to set it back to it's former position. When the number of records is not the same as before it is unsure if you get the right position and also you have the problem that the scrollbar must be found with different systen names in different Access versions.
Then I stumbled upon the following page which has a very simple and genius solution:
Access: Form Return to scroll position after update
The idea: A form has a (commonly unknown) property named "CurrentSectionTop" (and the pendant "CurrentSectionLeft"). This is the distance between the current record in a continous form and the section's top (in Twips, as usual). With some simple calculations this can be used to reposition the record selector exactly to where it was before.
The solution on the mentioned page was a little bit too special for me and also the way to use DoCmd commands to position the record. DoCmd commands works only with the active form so you always need to set the focus to the right form and a control inside which begins to make problems if you have some subforms.
Moreover the solution there doesn't work if the current record would be deleted by another user or what happens if the current record wasn't saved and so on.
So I rewrote the solution by using a class module which handles all these cases and repositions back to the right record.
The class has two conditional compiler constants at the top which can be used to configure the usage of the class. The first one can be used to compile the class for use with ADO or DAO (because DAO uses "FindFirst" and "NoMatch and ADO uses "Find" and "EOF") but keep in mind that you can use the class only for one library: DAO OR ADO, not both. So you must decide if you want to use DAO or ADO recordsets in your forms in your complete application, not some with DAO and some with ADO. If you really need this you must change the code of the class to allow both libraries by for example using different "Reposition" subs for either library. Normally you would only use one library in your application so that should be not necessary.
The other conditional compiler constant is "UseRequeryInterface" which you can set to "True" (without double quotes) if you want to use the interface class "ICCReposition" (if not you only need to import the class "clsCCReposition" into your database). If you use the interface class your form must implement the interface by adding the row "Implements ICCReposition" as first line in your code (see the demo form). Next you must choose the interface name in the left dropdown field in the VBA editor at the top of the editor and choose the sub "SetRecordSource" in the right one. This will insert the needed Sub into your form.
Now you must fill the form with any kind of code which is able to reload all records into the form. This could be a simple "Requery" but could also be the assignment of a recordset which you have filled in this sub before like shown in the demo forms. Here it is a simple "SELECT" to the local table but it could also be a complex stored procedure on a SQL Server (or any other external database server) where "Requery" maybe doesn't work (especially if the stored procedure contains parameters). Access tries to automatically set an own SQL command as RecordSource if you assign a filled recordset by your own but this command doesn't work in some cases (which also applies to the F5 requery so you need to disable it also).
The class "clsCCReposition" now tries to call this interface sub in your form if you use the interface, otherwise it automatically performs a simple "Requery". After requerying the form on the one or other method it tries to reposition the form to the recent record including the position on the form. If the record could not be found at the old position it tries to find it anywhere in the recordset (if successful the record is of course the first one in the form). If it could not be found then it tries to go back to the old position and to the next record or a new record if it was the last one and was deleted. If the form was dirty then the requery method will not be performed. If you scroll the form with the mouse wheel or scrollbars without setting the record selector to a viewable record then the Reposition method goes back to the current record with the old screen position so the current record will always be visible after requerying in all cases.
Another possible idea could be to insert a field "rownumber" into the table and sort the table by this column in the query of the form. Then you could write an INSERT sub which inserts a new record at the current position and renumbers all following records and the reposition/requery it. This would give the optical effect like inserting a row in a spreadsheet which could be interesting for some special solutions.
The same method of positioning could also be used with "CurrentSectionLeft" to position to a recent field in a row - but that I didn't try.
The attachment is in the format A2003 and was written and tested with A2007.
Have fun with the new "Requery"
Christian
CCReposition.zip
one of the surely most often used methods of a form is the method "Requery". As commonly known Access always positions back to the first record when using this method. An alternative up to a certain limit is the method "Refresh" which stays on the record where you were because it only refreshes the already available records but doesn't show the otherwise deleted or inserted records.
"Refresh" also fails in ADPs if you assign a recordset manually - "Refresh" works then in the same way as "Requery".
Commonly known is also that you can of course return to the recent record using "Find"/"FindFirst" (ADO/DAO). The problem here is that the record is then always the first record on the form. So if you were at the middle of the form with your recent record and you use Requery/Find to go back to the recent record the record is now at the top of the form which is very annoying.
When you search for solutions for this problem you will find solutions which tries to use the scrollbar position using Windows APIs to find the scrollbar of the form and return the current position and then, after requery, tries to set it back to it's former position. When the number of records is not the same as before it is unsure if you get the right position and also you have the problem that the scrollbar must be found with different systen names in different Access versions.
Then I stumbled upon the following page which has a very simple and genius solution:
Access: Form Return to scroll position after update
The idea: A form has a (commonly unknown) property named "CurrentSectionTop" (and the pendant "CurrentSectionLeft"). This is the distance between the current record in a continous form and the section's top (in Twips, as usual). With some simple calculations this can be used to reposition the record selector exactly to where it was before.
The solution on the mentioned page was a little bit too special for me and also the way to use DoCmd commands to position the record. DoCmd commands works only with the active form so you always need to set the focus to the right form and a control inside which begins to make problems if you have some subforms.
Moreover the solution there doesn't work if the current record would be deleted by another user or what happens if the current record wasn't saved and so on.
So I rewrote the solution by using a class module which handles all these cases and repositions back to the right record.
The class has two conditional compiler constants at the top which can be used to configure the usage of the class. The first one can be used to compile the class for use with ADO or DAO (because DAO uses "FindFirst" and "NoMatch and ADO uses "Find" and "EOF") but keep in mind that you can use the class only for one library: DAO OR ADO, not both. So you must decide if you want to use DAO or ADO recordsets in your forms in your complete application, not some with DAO and some with ADO. If you really need this you must change the code of the class to allow both libraries by for example using different "Reposition" subs for either library. Normally you would only use one library in your application so that should be not necessary.
The other conditional compiler constant is "UseRequeryInterface" which you can set to "True" (without double quotes) if you want to use the interface class "ICCReposition" (if not you only need to import the class "clsCCReposition" into your database). If you use the interface class your form must implement the interface by adding the row "Implements ICCReposition" as first line in your code (see the demo form). Next you must choose the interface name in the left dropdown field in the VBA editor at the top of the editor and choose the sub "SetRecordSource" in the right one. This will insert the needed Sub into your form.
Now you must fill the form with any kind of code which is able to reload all records into the form. This could be a simple "Requery" but could also be the assignment of a recordset which you have filled in this sub before like shown in the demo forms. Here it is a simple "SELECT" to the local table but it could also be a complex stored procedure on a SQL Server (or any other external database server) where "Requery" maybe doesn't work (especially if the stored procedure contains parameters). Access tries to automatically set an own SQL command as RecordSource if you assign a filled recordset by your own but this command doesn't work in some cases (which also applies to the F5 requery so you need to disable it also).
The class "clsCCReposition" now tries to call this interface sub in your form if you use the interface, otherwise it automatically performs a simple "Requery". After requerying the form on the one or other method it tries to reposition the form to the recent record including the position on the form. If the record could not be found at the old position it tries to find it anywhere in the recordset (if successful the record is of course the first one in the form). If it could not be found then it tries to go back to the old position and to the next record or a new record if it was the last one and was deleted. If the form was dirty then the requery method will not be performed. If you scroll the form with the mouse wheel or scrollbars without setting the record selector to a viewable record then the Reposition method goes back to the current record with the old screen position so the current record will always be visible after requerying in all cases.
Another possible idea could be to insert a field "rownumber" into the table and sort the table by this column in the query of the form. Then you could write an INSERT sub which inserts a new record at the current position and renumbers all following records and the reposition/requery it. This would give the optical effect like inserting a row in a spreadsheet which could be interesting for some special solutions.
The same method of positioning could also be used with "CurrentSectionLeft" to position to a recent field in a row - but that I didn't try.
The attachment is in the format A2003 and was written and tested with A2007.
Have fun with the new "Requery"
Christian
CCReposition.zip
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)