Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2007
    Posts
    42

    2007 'Refresh All' event?

    Does anyone know if there is an event for "Refresh All"? The 2007 ribbon includes both refresh and refresh all. When the user clicks refresh all, the form goes back to the first record in the dataset (as opposed to staying on the editing record when they click refresh). I wanted to add code to get the user back to the editing record, but can't find an event to do it.

    I'm working in Access 2007. My form source includes multiple records and I'm using the bookmark logic to navigate through them.

    I hope this isn't something obvious that I've missed. Thanks for your help ... Terri

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,925
    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.requery
    me.MyAutonumberField.setfocus
    docmd.findrecord varMyAutoIDField
    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)

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,925
    Also keep in mind that if you don't need to update the data in the table from the form, but instead just need to 'repaint' the form to fire events such as conditional formatting or show new text in caption boxes, you can utilize the me.repaint command. I can't say though if a repaint type command is located on any of the MSAccess menus in 2007.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •