Results 1 to 14 of 14
  1. #1
    john134 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    42

    Refresh vs. RefreshAll

    Is there a simple vba code that emulates the RefreshAll button on the menu? The Refresh option


    does not work, only the RefreshAll option. I have tried Requery in my code and that also does not
    emulate the RefreshAll button. Why is this not a straightforward code issue in vba?

    My code is:

    DoCmd.OpenForm "Main Menu"
    Me.Refresh

    When I open the form it is not updated. But, updates as soon
    as I click on the RefreshAll button on the Menu

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You are only refreshing the form. There's only 1 table/qry on the form.
    'Refresh all ' updates all the controls; cbo boxes, lists, subforms.

    What do you want updated?

  3. #3
    john134 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    42
    I want to update the "Main Menu" form in code without having to manually click
    the RefreshAll button.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might try using the form "Current" event........

    Here is a description of the differences between "Requery", "ReCalc", "Refresh" and "Repaint".
    http://www.projectperfect.com.au/blo...h-and-repaint/

  5. #5
    john134 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    42
    Thank you for your reply and advice.

    The ReCalc code works fo updating the Main Menu. I think this is because the
    control to be updated is a calculation, i.e., DCount("......).

    However, I have another issue. I am entering data into a new form and then saving it. The Record Source for the form
    is the table, "Individual Donor Table." The table updates with the new record only when I click on the
    RefreshAll button.

    Code:
     DoCmd.OpenTable "Individual Donor Table"          Me.Requery
    Replacing Requery with ReCalc, Repaint, or Refresh does not work. Any suggestions
    would be welcome.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by john134 View Post

    The table updates with the new record only when I click on the
    RefreshAll button.
    I'm not really sure you understand what Refresh (and hence RefreshAll) does; it has nothing to so with the Table reflecting new Records. Tables show New Records when the Record is Saved.

    From Help:

    The Refresh method immediately updates the records in the underlying record source for a specified form or datasheet to reflect changes made to the data by you and other users in a multiuser environment.

    In other words, if you're UserA...and UserB edits a given Record...when UserA's Form is used is Refreshed it will then show the edit that UserB made to that given Record...and Refresh only applies to Records that existed when UserA was first Loaded...it will not show New Records that have been added since UserA's Form was Loaded. To do that, it has to be Requeried, as was mentioned above.

    If hitting RefeshAll is 'causing' the New Record to appear in the Table, it's because you've exited the Record, in order to hit RefreshAll, and therefore the Record is Saved...just as it would be if you exited the Record by going to another Record or by Closing the Form.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you open the table after the record is added, it will show. The record is actually in the table as soon as it is added. Why are you working with viewing data at the table level?

  8. #8
    john134 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    42
    Thank you for the reply. Yes, it does show when I open the table manually.
    But, not when I open it in code. No reason to open it except to verify that it was updated.
    I believe from the responses that there is no straightforward code for manually clicking
    the RefreshAll button. Can anyone confirm that, or not?

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You can use vba to close the open table, then reopen it. I haven't been able to find any vba code to do the equivalent of refreshall.
    Last edited by davegri; 09-27-2016 at 02:23 PM. Reason: clarity

  10. #10
    john134 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    42
    I have tried that with the following code:

    DoCmd.OpenTable ("Individual Donor Table")
    DoCmd.Close ("Individual Donor Table")
    DoCmd.OpenTable ("Individual Donor Table")

    But, on the second line I get the following error message:

    Run type error 13
    Type mismatch

    It will work when manually opening, closing, and reopening
    but not in code. Although I can live without opening the table,
    it is somewhat puzzling to me as to why it won't work when
    coded.

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Below worked for me:

    Code:
    Public Sub atest()
        Dim MyTableName As String
        MyTableName = "tblColors"
        DoCmd.OpenTable MyTableName
        DoCmd.Close acTable, MyTableName
        DoCmd.OpenTable MyTableName
    End Sub

  12. #12
    john134 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    42
    I copied your code with my table name. The code ran with
    no errors with your version. However, the table did not show
    the new record unless I click on RefreshAll.

    I am about to give up on this issue but I am very curious
    why this isn't working. I appreciate your assistance and patience.

  13. #13
    john134 is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2014
    Posts
    42
    P.S. It worked this time around. The problem was when I tested
    it, I did not include an entry on a control on a subform for another table that is
    related to my "Individual Donor Table." When I did so, I did see
    the new record. I learned a lot about how Access works and, again,
    thanks for your help.

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Public Sub atest()
        Dim MyTableName As String
        MyTableName = "tblColors"
        DoCmd.OpenTable MyTableName
        DoCmd.Close acTable, MyTableName
        DoCmd.OpenTable MyTableName
    End Sub
    OK. I did check my code again to make sure it worked for me, and it did.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Refresh button
    By Kat-ness in forum Forms
    Replies: 2
    Last Post: 05-01-2013, 08:37 PM
  2. How to refresh a DB?
    By adacpt in forum Programming
    Replies: 1
    Last Post: 12-02-2012, 02:45 PM
  3. Refresh All
    By gg80 in forum Programming
    Replies: 3
    Last Post: 01-12-2012, 03:39 PM
  4. 2010 MS Access 'RefreshAll' Automation
    By Tomfernandez1 in forum Forms
    Replies: 1
    Last Post: 10-26-2011, 12:47 PM
  5. Access 2010 Refresh VS Refresh ALL
    By Snwboarder1982 in forum Access
    Replies: 1
    Last Post: 09-09-2011, 04:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums