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

    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.


  2. #2
    Join Date
    Sep 2002
    South Wales

    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
    Vancouver, WA

    Re: Update records in Code instead of query


    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
    Dallas, TX

    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

    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
    DoCmd.OpenReport stDocName, acViewNormal
    MsgBox "Your records have been processed.", vbExclamation

    Call PostJobCharge

    Exit Sub

    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