As far as I know, Refresh works like the old docmd.saverecord code where the data on the form is written to the table but doesn't 'requery' the recordset and take you to the first record (but I'm pretty sure both commands write the data from the form to the table). I never used the docmd.saverecord code though since I always had an autonumber field in my data table which prevented the need to issue such a command.
I will often use Refresh in my code to prevent the 'Data has been changed' problem if another form is open based on the same recordset and data was updated.
But since you can't write code such as: Forms!MyFormName.refresh (ie. referencing and refreshing data on a different form), you must use Forms!MyFormName.requery in code which I'm 'guessing' is what the 'Refresh All' accomplishes.
One trick you can do is to utilize your autonumber field (which you SHOULD have in the table) to utilize the requery type command (ie. Refresh All) and stay on the same record. You could write code as such:
Dim varMyAutoIDField as variant
varMyAutoIDField = me!MyAutonumberField
me.someothercontrol.setfocus <- so the focus doesn't stay on the autonumber field.
Note: The autonumber field must be enabled (but can and should be locked) on the form.
Another trick which I like to do (which prevents having to use the mousewheel.dll and accidently scrolling to a new record which can be problematic) is to add this code in the OnCurrent event of the form: Docmd.gotorecord,,acfirst (Note though: you must return only 1 record to the form - it doesn't work for returning multiple records to the form). What this trick does is prevent the user from accidently using the scrollwheel and they end up on a blank record when they were editing the data on the current record. Although this doesn't answer your original question, I had to add this as a suggestion since I prefer and recommend to only return 1 record to the form (using criteria) versus multiple records (which will cause a slower form opening response.) If you're dealing with thousands of records and returning all of them to the form (along with a bunch of subforms), you can really make a form perform sluggish and slow returning all the records.
Last edited by pkstormy; 04-07-10 at 22:57.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)