Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Wink Unanswered: Update records in Code instead of query

    Hello again,

    I am shooting for a bit of smoother automation. After my transaction is completed I run an UpdateQuery to change the CheckBox from True(-1) back to False (0). I don't want to go through the process of clicking yes to prompts about running the query. Can this be done in a VBA procedure and run the Update without any user intervention? If so please show me the way. Thanks in advance.

    Bud

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Snippet of code

    Enclose your existing code (behind a button?) with

    docmd.setwarnings false

    (your query code)

    docmd.setwarnings true


    No need for anything fancy!
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Jan 2004
    Location
    Vancouver, WA
    Posts
    41

    Re: Update records in Code instead of query

    Bud,

    I don't know how you are running your transaction, but you can just do this in code after the transaction completes:


    Private Sub UpdateStuff()
    Dim db1 as Database

    Set db1 = CurrentDb

    db1.Execute("<your update query name here>")

    Set db1 = Nothing

    End Sub

    If you have more info on the method you are using to run your transaction, it would be easier to figure out how to put the two together.

    Mark N.

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

    Re: Update records in Code instead of query

    Thanks gaerthdart and mtnorwood,
    While waiting for replies I tried something that worked. Not the best way maybe but works for now. I created the code below and Called a function(PostJobCharge) that I created by converting the Macro which ran the Update queries. What I did next was went to Tools/Options/..Edit/Find tab and cleared the Confirm box for ActionQueries. Garethdart, would that be the same as you did with the SetWarnings to False? Mtnorwood, I didn't create this database but came in after creator left the company. It had 2 Update queries running in conjunction to finish the transaction.
    thanks guys, you're great
    Bud



    Private Sub cmbProcessJobCharge_Click()
    On Error GoTo Err_cmbProcessJobCharge_Click
    'Revised by Bud 1-29-2004.
    'This automates Posting and Updating job charges to avoid the macros
    'or clicking the dialog boxes from the Update queries.

    Dim stDocName As String

    stDocName = "JobCharge"
    DoCmd.GoToRecord , , acNewRec
    ShipDate.SetFocus
    DoCmd.OpenReport stDocName, acViewNormal
    DoCmd.Close
    MsgBox "Your records have been processed.", vbExclamation

    Call PostJobCharge

    Exit_cmbProcessJobCharge_Click:
    Exit Sub

    Err_cmbProcessJobCharge_Click:
    MsgBox Err.Description
    Resume Exit_cmbProcessJobCharge_Click

    End Sub

Posting Permissions

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