DoCmd.OpenQuery and error trapping

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • jj
    #1

    DoCmd.OpenQuery and error trapping

    I've got a form button that fires off 3 queries but if the first query
    returns an error, I don't want the other two queries to happen.

    Example: first query runs an insert from a linked table but if for some
    reason a field is missing a value that's required, Access throws up an
    error. At that point I just want to end the process and notify them of the
    error so they can correct the data and try the process again.


    Here's my code:
    DoCmd.OpenQuery "download_recor ds_clear", acNormal, acEdit (clears
    a temp table)
    DoCmd.OpenQuery "download_recor ds", acNormal, acEdit (downloads
    linked records)
    DoCmd.OpenQuery "download_recor ds_set", acNormal, acEdit (update
    linked records d/l value)

    Well if Query "download_recor ds" doesn't go well, I don't want to do query
    "download_recor ds_set".


    Is there some sort of error checking I can put in between those two lines?



    Here's the complete code for that button:

    ------------------------------------------------------
    Private Sub ImportWebFiles_ Click()
    Dim qryOption As Boolean
    On Error GoTo Err_ImportWebFi les_Click


    qryOption = Application.Get Option("Confirm Action Queries")
    Application.Set Option "Confirm Action Queries", False

    DoCmd.OpenQuery "download_recor ds_clear", acNormal, acEdit
    DoCmd.OpenQuery "download_recor ds", acNormal, acEdit
    DoCmd.OpenQuery "download_recor ds_set", acNormal, acEdit

    MsgBox "Records downloaded."

    Application.Set Option "Confirm Action Queries", qryOption


    Exit_ImportWebF iles_Click:
    Exit Sub

    Err_ImportWebFi les_Click:
    MsgBox Err.Description
    Application.Set Option "Confirm Action Queries", qryOption
    DoCmd.SetWarnin gs True
    Resume Exit_ImportWebF iles_Click

    End Sub
    ------------------------------------------------------------


    Tags: acedit, docmd.openquery
    • Rick Brandt
      #2
      Re: DoCmd.OpenQuery and error trapping

      "jj" <jj@test.net> wrote in message
      news:X1Xlb.6952 4$832.65728@twi ster.rdc-kc.rr.com...[color=blue]
      > I've got a form button that fires off 3 queries but if the first query
      > returns an error, I don't want the other two queries to happen.
      >
      > Example: first query runs an insert from a linked table but if for some
      > reason a field is missing a value that's required, Access throws up an
      > error. At that point I just want to end the process and notify them of[/color]
      the[color=blue]
      > error so they can correct the data and try the process again.
      >
      >
      > Here's my code:
      > DoCmd.OpenQuery "download_recor ds_clear", acNormal, acEdit[/color]
      (clears[color=blue]
      > a temp table)
      > DoCmd.OpenQuery "download_recor ds", acNormal, acEdit (downloads
      > linked records)
      > DoCmd.OpenQuery "download_recor ds_set", acNormal, acEdit[/color]
      (update[color=blue]
      > linked records d/l value)
      >
      > Well if Query "download_recor ds" doesn't go well, I don't want to do[/color]
      query[color=blue]
      > "download_recor ds_set".
      >
      >
      > Is there some sort of error checking I can put in between those two[/color]
      lines?

      Use the Execute method instead with the appropriate option.

      Dim MyDB as Database
      Set MyDB = CurrentDB

      MyDB.Execute "download_recor ds_clear", dbFailOnError
      MyDB.Execute "download_recor ds", dbFailOnError
      MyDB.Execute "download_recor ds_set", dbFailOnError

      The dbFailOnError causes an Error event to be raised if the query fails.
      Code execution should then revert to your error handler.


      --
      I don't check the Email account attached
      to this message. Send instead to...
      RBrandt at Hunter dot com


        Comment

        • jj
          #3
          Re: DoCmd.OpenQuery and error trapping

          Hmmm.. it's giving me a User-defined Data Type Not Found error with:

          Dim MyDB As Database
          Set MyDB = CurrentDb

          I didn't have to define a database connection before or anything, so what
          would it be looking for here?

          Thanks!




          "Rick Brandt" <rickbrandt2@ho tmail.com> wrote in message
          news:bn9f6d$v1m nr$1@ID-98015.news.uni-berlin.de...[color=blue]
          > "jj" <jj@test.net> wrote in message
          > news:X1Xlb.6952 4$832.65728@twi ster.rdc-kc.rr.com...[color=green]
          > > I've got a form button that fires off 3 queries but if the first query
          > > returns an error, I don't want the other two queries to happen.
          > >
          > > Example: first query runs an insert from a linked table but if for some
          > > reason a field is missing a value that's required, Access throws up an
          > > error. At that point I just want to end the process and notify them of[/color]
          > the[color=green]
          > > error so they can correct the data and try the process again.
          > >
          > >
          > > Here's my code:
          > > DoCmd.OpenQuery "download_recor ds_clear", acNormal, acEdit[/color]
          > (clears[color=green]
          > > a temp table)
          > > DoCmd.OpenQuery "download_recor ds", acNormal, acEdit[/color][/color]
          (downloads[color=blue][color=green]
          > > linked records)
          > > DoCmd.OpenQuery "download_recor ds_set", acNormal, acEdit[/color]
          > (update[color=green]
          > > linked records d/l value)
          > >
          > > Well if Query "download_recor ds" doesn't go well, I don't want to do[/color]
          > query[color=green]
          > > "download_recor ds_set".
          > >
          > >
          > > Is there some sort of error checking I can put in between those two[/color]
          > lines?
          >
          > Use the Execute method instead with the appropriate option.
          >
          > Dim MyDB as Database
          > Set MyDB = CurrentDB
          >
          > MyDB.Execute "download_recor ds_clear", dbFailOnError
          > MyDB.Execute "download_recor ds", dbFailOnError
          > MyDB.Execute "download_recor ds_set", dbFailOnError
          >
          > The dbFailOnError causes an Error event to be raised if the query fails.
          > Code execution should then revert to your error handler.
          >
          >
          > --
          > I don't check the Email account attached
          > to this message. Send instead to...
          > RBrandt at Hunter dot com
          >
          >[/color]


            Comment

            • jj
              #4
              Re: DoCmd.OpenQuery and error trapping

              Nevermind I found that I had to select DAO 3.6 in Tools>Reference s.

              However, is there another way to work this so that setting doesn't have to
              be made on other files, etc? Can .Execute only be used with DAO 3.6?



              "jj" <jj@test.net> wrote in message
              news:OZdmb.7016 4$832.59363@twi ster.rdc-kc.rr.com...[color=blue]
              > Hmmm.. it's giving me a User-defined Data Type Not Found error with:
              >
              > Dim MyDB As Database
              > Set MyDB = CurrentDb
              >
              > I didn't have to define a database connection before or anything, so what
              > would it be looking for here?
              >
              > Thanks!
              >
              >
              >
              >
              > "Rick Brandt" <rickbrandt2@ho tmail.com> wrote in message
              > news:bn9f6d$v1m nr$1@ID-98015.news.uni-berlin.de...[color=green]
              > > "jj" <jj@test.net> wrote in message
              > > news:X1Xlb.6952 4$832.65728@twi ster.rdc-kc.rr.com...[color=darkred]
              > > > I've got a form button that fires off 3 queries but if the first query
              > > > returns an error, I don't want the other two queries to happen.
              > > >
              > > > Example: first query runs an insert from a linked table but if for[/color][/color][/color]
              some[color=blue][color=green][color=darkred]
              > > > reason a field is missing a value that's required, Access throws up an
              > > > error. At that point I just want to end the process and notify them[/color][/color][/color]
              of[color=blue][color=green]
              > > the[color=darkred]
              > > > error so they can correct the data and try the process again.
              > > >
              > > >
              > > > Here's my code:
              > > > DoCmd.OpenQuery "download_recor ds_clear", acNormal, acEdit[/color]
              > > (clears[color=darkred]
              > > > a temp table)
              > > > DoCmd.OpenQuery "download_recor ds", acNormal, acEdit[/color][/color]
              > (downloads[color=green][color=darkred]
              > > > linked records)
              > > > DoCmd.OpenQuery "download_recor ds_set", acNormal, acEdit[/color]
              > > (update[color=darkred]
              > > > linked records d/l value)
              > > >
              > > > Well if Query "download_recor ds" doesn't go well, I don't want to do[/color]
              > > query[color=darkred]
              > > > "download_recor ds_set".
              > > >
              > > >
              > > > Is there some sort of error checking I can put in between those two[/color]
              > > lines?
              > >
              > > Use the Execute method instead with the appropriate option.
              > >
              > > Dim MyDB as Database
              > > Set MyDB = CurrentDB
              > >
              > > MyDB.Execute "download_recor ds_clear", dbFailOnError
              > > MyDB.Execute "download_recor ds", dbFailOnError
              > > MyDB.Execute "download_recor ds_set", dbFailOnError
              > >
              > > The dbFailOnError causes an Error event to be raised if the query fails.
              > > Code execution should then revert to your error handler.
              > >
              > >
              > > --
              > > I don't check the Email account attached
              > > to this message. Send instead to...
              > > RBrandt at Hunter dot com
              > >
              > >[/color]
              >
              >[/color]


                Comment

                Previous template Next
                Working...