Results 1 to 7 of 7
  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

    Error trapping (Access 2003)

    Hello Access Gurus,

    I have included a portion of my code which is producing different results in two copies of my database and was wondering why the error checking works in one and not the other of the databases. There doesn't appear to be anything different between the code in either database. I have also checked References and Options and they are all the same.

    The code runs an Append query and the error checking checks for incorrect data. The error message that should work is error number 3201 when I have intentionally placed incorrect data in a Key field.

    CODE SAMPLE FOLLOWS ***********
    If MsgBox(strMsg, myButtons, msgTitle) = vbYes Then
    'DoCmd.OpenQuery ("qryAppendData")
    db.Execute "qryAppendData", dbFailOnError
    MsgBox "Appended " & intNumber & " Records"
    Else
    MsgBox ("Append action cancelled by user")
    DoCmd.CancelEvent
    End If

    'MsgBox "Successful append of " & intNumber & " Records"

    Exit_cmdAppendImportedData_Click:
    Exit Sub

    Err_cmdAppendImportedData_Click:
    msgTitle = "Append Imported Data Error"
    myButtons = vbOK
    Select Case Err.Number
    Case 3021 'Import Action is cancelled
    Resume Exit_cmdAppendImportedData_Click
    Case 3022 'Key violation error
    MsgBox "Problem with append query"
    Resume Exit_cmdAppendImportedData_Click
    Case 3201 'Key field type error
    strMsg = "Problem with a key field. Please check you have the correct Fundholder Code for this service. "
    strMsg = strMsg & Chr(13) & Chr(13) & " Please re-check the Excel data and repeat the Import Process from the beginning"
    MsgBox strMsg, myButtons, msgTitle
    Resume Exit_cmdAppendImportedData_Click
    Case 3464 'Data type error
    strMsg = "Problem with data type, please check you have the correct data types for each field. "
    strMsg = strMsg & Chr(13) & Chr(13) & " Check there are no text values in numeric fields. eg Postcode does not contain N/A#."
    strMsg = strMsg & Chr(13) & Chr(13) & " Please check the Excel data and repeat the Import Process from the beginning"
    MsgBox strMsg
    Resume Exit_cmdAppendImportedData_Click

    Case Else
    strMsg = "Error No. " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "Import Error"
    End Select

    END OF CODE SAMPLE ************

    I look forward to a response.

    Regards

    Jocelyn

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

    Re: Error trapping (Access 2003)

    What happens if you temporarily uncomment the line

    'DoCmd.OpenQuery ("qryAppendData")

    (you don't need the parentheses here) and comment out the line

    db.Execute "qryAppendData", dbFailOnError

  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: Error trapping (Access 2003)

    I get the usual Access messages:
    1. You are about to run an Append query, do you want to continue Yes or No?
    2. You are about to append ## records, do you want to continue Yes or No?
    The query runs and I get no error messages!

    This code was running beautifully with the docmd.execute command as I tested several lots of data. However I took a copy of the database, removed all the records and then re-imported Excel data into Access and then appended the records as usual with test data that should have gone to the On error routine but it didn't work.

    It is a real mystery to me.

    Does anything stop those error messages from executing except for DoCmd.SetWarnings False? Although that command only works for Access messages.

    Regards,

    Jocelyn

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

    Re: Error trapping (Access 2003)

    Are you absolutely sure that have conflicting data in the database where you don't get an error message? Have you inspected the target table before and after running the code and compared the number of records?

  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: Error trapping (Access 2003)

    I have found the reason for the error trapping not working. Some how my relationships had been stuffed up so there was no way of the error checking to detect the referential integrity of the data I was appending. So sorry to have wasted your time and thanks again for your response.

    Regards

    Jocelyn

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

    Re: Error trapping (Access 2003)

    Thanks Hans and yes I had done all of that testing.
    Please see me previous response as I have since found the problem.

    Kind regards,

    Jocelyn

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

    Re: Error trapping (Access 2003)

    I'm glad you found the cause - such things can be very frustrating!

Posting Permissions

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