Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2008
    Posts
    27

    Unanswered: Update Table Macro

    I have a macro setup that updates 4 tables. I was wondering if there was a way to set it up so that instead of having to click "Yes" 3 times per update to setup a Macro that automatically selects Yes for evertime it asks if you want run the update query, then ____query will be deleted, continue, then you are about to paste 122 into table. It just seems time consuming.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Convert he macros into editable code by right clicking them and chosing to save as a module.
    Then you can customise the code into a single call with 1 prompt.
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2008
    Posts
    27
    Can you help me out on the code end of it, it is not my strong point. Here is what I have:

    Function Performance_Table_Makers()

    DoCmd.OpenQuery "Greenpoint Table Maker", acViewNormal, acEdit
    DoCmd.OpenQuery "FTB Table Maker", acViewNormal, acEdit
    DoCmd.OpenQuery "PMSI Table Maker", acViewNormal, acEdit
    DoCmd.OpenQuery "All Clients Atty Performance Table Maker - Active", acViewNormal, acEdit
    DoCmd.OpenQuery "All Clients Atty Performance Table Maker - Sales", acViewNormal, acEdit

    End Function

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Look up 'setwarnings' in help.

  5. #5
    Join Date
    Dec 2007
    Posts
    70
    Quote Originally Posted by rogue
    Look up 'setwarnings' in help.
    Yes I agree with Rogue. you can 'setwarnings' so that it doesn't ask you if you want to run queries such as delete queries. Should do the trick.

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    try this

    Code:
    Function Performance_Table_Makers()
    DoCmd.SetWarnings False  'turn them off
    DoCmd.OpenQuery "Greenpoint Table Maker", acViewNormal, acEdit
    DoCmd.OpenQuery "FTB Table Maker", acViewNormal, acEdit
    DoCmd.OpenQuery "PMSI Table Maker", acViewNormal, acEdit
    DoCmd.OpenQuery "All Clients Atty Performance Table Maker - Active", acViewNormal, acEdit
    DoCmd.OpenQuery "All Clients Atty Performance Table Maker - Sales", acViewNormal, acEdit
    DoCmd.SetWarnings True  'turn back on
    End Function
    Please trun it back on other wise if you delete some NO WARNING yes i have done that and lost a data
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Feb 2008
    Posts
    27
    Got it working perfectly, thank you all for the help!

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Cool

    Quote Originally Posted by OB1
    Yes I agree with Rogue. you can 'setwarnings' so that it doesn't ask you if you want to run queries such as delete queries. Should do the trick.
    Good morning all. The best way IMHO is to use

    CurrentDb.Execute

    That way you don't have to worry about forgetting the SetWarnings altogether. Works smooth as silk.

    have a nice one
    BUD

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    you knew this was coming... My 2c

    ERROR HANDLE IF YOU ARE USING SETWARNINGS!!!
    Code:
    On Error GoTo ErrorHandler
    
        DoCmd.SetWarnings False
        ....
        ....
    
    ExitHandler:
        DoCmd.SetWarnings True
        Exit Function
    
    ErrorHandler:
        MsgBox("There has been an error")
        Resume ExitHandler
    Exit Function
    George
    Home | Blog

Posting Permissions

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