Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Append and Action Queries (Access 2003)

    Hello Access Gurus,

    I am running an Append Action query and am wondering how to trap an Access error message when it may happen. If there are data type errors then an Access message is displayed asking whether I want to continue or not (Yes or No). Can anyone tell me what error number this Access message is or more importantly how I can intercept it with VB code?

    Thanks,

    Jocelyn

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Append and Action Queries (Access 2003)

    The general answer, which may not apply in this case (but give it a try) is this.

    If your code has an error handler in, the error handler will display a message box with code like this. This displays a description of the error.
    MsgBox Err.Description

    If you change the code to this, it will also display the number as well.
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") "

    Once you know the number you can handle it like this

    If Err.Number = 1234 Then (or whatever the number is)
    Action you want performed
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    Resume Exit_cmdCancel_Click
    End If
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append and Action Queries (Access 2003)

    Thanks for your response John. I am familiar with this code however, if an Action query fails an Access message is displayed however it doesn't have a number. So I can't include it in my error routines.

    Regards

    Jocelyn

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append and Action Queries (Access 2003)

    If you run the action query as follows:

    CurrentDb.Execute "NameOfTheQuery", dbFailOnError

    you will get error 3022 if there is a key conflict, and you can intercept this error using an error handler as indicated by John Hutchison.

    Note: CurrentDb.Execute is a DAO instruction; it operates at a lower level than the Access interface. It won't work for queries that refer to controls on forms, and for parameter queries.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Append and Action Queries (Access 2003)

    Thanks Hans, I shall give this a go

    Regards

    Jocelyn

Posting Permissions

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