Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Running Update Queries Seamlessly

Discussion in 'Information Technology' started by Jurrasicway, Jul 28, 2006.

  1. Jurrasicway

    Jurrasicway
    Expand Collapse
    Guest

    I have created a command button that I want to use to activate 3 update
    queries avoiding the messages that tell me how many records I am going to
    update and the warning that it can not be undone. How do I turn this function
    off?
    My code is

    Private Sub Command1_Click()
    DoCmd.OpenQuery "qryzFloatA"
    DoCmd.OpenQuery "qryzFloatR"
    DoCmd.OpenQuery "qryzFloatG"
    End Sub

    Thanks,

    Graeme.
     
  2. Loading...


  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Try this:

    Private Sub Command1_Click()
    Currentdb.execute "qryzFloatA", dbFailOnError
    Currentdb.execute "qryzFloatR", dbFailOnError
    Currentdb.execute "qryzFloatG", dbFailOnError
    End Sub

    Make sure to have a reference set to the DAO 3.6 Object Library if you
    include the optional dbFailOnError parameter.


    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "Jurrasicway" wrote:

    > I have created a command button that I want to use to activate 3 update
    > queries avoiding the messages that tell me how many records I am going to
    > update and the warning that it can not be undone. How do I turn this function
    > off?
    > My code is
    >
    > Private Sub Command1_Click()
    > DoCmd.OpenQuery "qryzFloatA"
    > DoCmd.OpenQuery "qryzFloatR"
    > DoCmd.OpenQuery "qryzFloatG"
    > End Sub
    >
    > Thanks,
    >
    > Graeme.
     
  4. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    Graeme,

    Add two lines of code like this:

    Private Sub Command1_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryzFloatA"
    DoCmd.OpenQuery "qryzFloatR"
    DoCmd.OpenQuery "qryzFloatG"
    DoCmd.SetWarnings True
    End Sub

    HTH,
    Nikos

    Jurrasicway wrote:
    > I have created a command button that I want to use to activate 3 update
    > queries avoiding the messages that tell me how many records I am going to
    > update and the warning that it can not be undone. How do I turn this function
    > off?
    > My code is
    >
    > Private Sub Command1_Click()
    > DoCmd.OpenQuery "qryzFloatA"
    > DoCmd.OpenQuery "qryzFloatR"
    > DoCmd.OpenQuery "qryzFloatG"
    > End Sub
    >
    > Thanks,
    >
    > Graeme.
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Before the first DoCmd, add the line:
    DoCmd.SetWarnings False
    Don't forget to turn SetWarnings back on again afterwards.

    The problem with that approach is that, once you turn SetWarnings off, you
    get no message if something goes wrong and the action query does not
    complete as expected. If you need to know whether the first one finished
    successfully before you start the second, this approach is flawed.

    The alternative is to Execute the queries instead. There is no need to turn
    SetWarnings off, and if you use the dbFailOnError switch, you do get a
    trappable error letting you know not to proceed:
    Dim db As DAO.Database
    Set db = CurrentDb()
    db.Execute "qryzFloatA", dbFailOnError
    db.Execute "qryzFloatR", dbFailOnError
    db.Execute "qryzFloatG", dbFailOnError

    The code does need adjusting if the queries contain parameters or refer to
    controls on a form.

    You also have the option to place all 3 inside a transaction so you can roll
    the whole thing back if any one fails. More info and example in:
    Archive: Move records to another table
    at:
    http://allenbrowne.com/ser-37.html


    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Jurrasicway" <Jurrasicway@discussions.microsoft.com> wrote in message
    news:A1CD3D3C-B578-4578-BF7B-0A238097E219@microsoft.com...
    >I have created a command button that I want to use to activate 3 update
    > queries avoiding the messages that tell me how many records I am going to
    > update and the warning that it can not be undone. How do I turn this
    > function
    > off?
    > My code is
    >
    > Private Sub Command1_Click()
    > DoCmd.OpenQuery "qryzFloatA"
    > DoCmd.OpenQuery "qryzFloatR"
    > DoCmd.OpenQuery "qryzFloatG"
    > End Sub
    >
    > Thanks,
    >
    > Graeme.
     
  6. Rob Parker

    Rob Parker
    Expand Collapse
    Guest

    Application.SetOption "Confirm Action Queries", False
    will turn off the confirmation, and
    Application.SetOption "Confirm Action Queries", True
    will turn it on again. So place those lines before and after your
    DoCmd.OpenQuery statements.

    There are also .SetOption methods for other options:
    Application.SetOption "Confirm Record Changes", False
    Application.SetOption "Confirm Document Deletions", False

    Using the .SetOption methods may change a user's defaults. I use a couple
    of functions called in the Load and Unload events of my startup form to save
    the user's defaults to a table, set them to what I want while the
    application is running, and restore the defaults when my application closes.
    Here's the code, if you want to be fancier:

    Public Sub SetOptions()
    Dim strSQL As String
    Dim blnConfirmChange As Boolean
    Dim blnConfirmDelete As Boolean
    Dim blnConfirmAction As Boolean
    Dim strPrinter As String
    On Error GoTo SetOptions_Error
    blnConfirmChange = Application.GetOption("Confirm Record Changes")
    blnConfirmDelete = Application.GetOption("Confirm Document Deletions")
    blnConfirmAction = Application.GetOption("Confirm Action Queries")
    strPrinter = Application.Printer.DeviceName
    strSQL = "UPDATE tblSettings SET ConfirmChange = " _
    & blnConfirmChange & ", ConfirmDelete = " & blnConfirmDelete _
    & ", ConfirmAction = " & blnConfirmAction _
    & ", Printer = '" & strPrinter & "';"
    DoCmd.RunSQL strSQL
    Application.SetOption "Confirm Record Changes", False
    Application.SetOption "Confirm Document Deletions", False
    Application.SetOption "Confirm Action Queries", False
    Exit Sub
    SetOptions_Error:
    MsgBox "Error " & Err.Number & " - " & Err.Description & vbCrLf & _
    "in procedure SetOptions" & vbCrLf & "of Module GeneralFunctions"
    Resume ExitPoint
    End Sub

    Public Sub RestoreOptions()
    On Error GoTo RestoreOptions_Error
    Application.SetOption "Confirm Record Changes", DLookup("ConfirmChange",
    "tblSettings")
    Application.SetOption "Confirm Document Deletions",
    DLookup("ConfirmDelete", "tblSettings")
    Application.SetOption "Confirm Action Queries", DLookup("ConfirmAction",
    "tblSettings")
    Set Application.Printer = Application.Printers(DLookup("Printer",
    "tblSettings"))
    Exit Sub
    RestoreOptions_Error:
    MsgBox "Error " & Err.Number & " - " & Err.Description & vbCrLf & _
    "in procedure RestoreOptions" & vbCrLf & "of Module GeneralFunctions"
    End Sub

    HTH,

    Rob

    "Jurrasicway" <Jurrasicway@discussions.microsoft.com> wrote in message
    news:A1CD3D3C-B578-4578-BF7B-0A238097E219@microsoft.com...
    >I have created a command button that I want to use to activate 3 update
    > queries avoiding the messages that tell me how many records I am going to
    > update and the warning that it can not be undone. How do I turn this
    > function
    > off?
    > My code is
    >
    > Private Sub Command1_Click()
    > DoCmd.OpenQuery "qryzFloatA"
    > DoCmd.OpenQuery "qryzFloatR"
    > DoCmd.OpenQuery "qryzFloatG"
    > End Sub
    >
    > Thanks,
    >
    > Graeme.
     
  7. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Nikos,

    If you are going to use the Set Warnings method, then you should definately
    implement an error-handler, and make sure to turn warnings back on as a part
    of exiting the procedure. Otherwise, if one of the update queries fails for
    any reason, the warnings will not be turned back on. Something like this:

    Private Sub Command1_Click()
    On Error GoTo ProcError

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryzFloatA"
    DoCmd.OpenQuery "qryzFloatR"
    DoCmd.OpenQuery "qryzFloatG"

    ExitProc:
    DoCmd.SetWarnings True
    Exit Sub
    ProcError:
    MsgBox "Your custom error message here" <---Optional
    Resume ExitProc
    End Sub



    Tom Wickerath
    Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________


    "Nikos Yannacopoulos" wrote:

    > Graeme,
    >
    > Add two lines of code like this:
    >
    > Private Sub Command1_Click()
    > DoCmd.SetWarnings False
    > DoCmd.OpenQuery "qryzFloatA"
    > DoCmd.OpenQuery "qryzFloatR"
    > DoCmd.OpenQuery "qryzFloatG"
    > DoCmd.SetWarnings True
    > End Sub
    >
    > HTH,
    > Nikos
    >
    > Jurrasicway wrote:
    > > I have created a command button that I want to use to activate 3 update
    > > queries avoiding the messages that tell me how many records I am going to
    > > update and the warning that it can not be undone. How do I turn this function
    > > off?
    > > My code is
    > >
    > > Private Sub Command1_Click()
    > > DoCmd.OpenQuery "qryzFloatA"
    > > DoCmd.OpenQuery "qryzFloatR"
    > > DoCmd.OpenQuery "qryzFloatG"
    > > End Sub
    > >
    > > Thanks,
    > >
    > > Graeme.

    >
     

Share This Page