Re: Error with Append Query

From: Joe (anonymous_at_discussions.microsoft.com)
Date: 05/21/04 

Date: Fri, 21 May 2004 08:51:02 -0700

Thanks Graham -- that's exactly what I wanted!

Joe
     
     ----- Graham Mandeno wrote: -----
     
     Hi Joe
     
     Instead of
         DoCmd.RunSQL strAddQ
     use this:
         CurrentDb.Execute strAddQ, dbFailOnError
     
     Then, when an error occurs, you can trap it, check the Err.Number for the
     error code you want to ignore, and do a Resume Next (or whatever else you
     require).
     --
     Good Luck!
     
     Graham Mandeno [Access MVP]
     Auckland, New Zealand
     
     
     "Joe" <anonymous@discussions.microsoft.com> wrote in message
     news:A949CF14-0275-4DA1-8436-1065312422DC@microsoft.com...
> Hello All:
>> I have created a command button on one of my forms that appends whatever
     record is being displayed to a table called "tblUser." The gist of the code
     is shown below:
>> Private Sub cmdSaveRecord_Click()
>> Dim strAddQ as String
> Dim strID As String
>> strID = Me.ID
>> strAddQ = "INSERT INTO tblUser ( ID, Question, [Choice A], [Choice B],
     [Choice C], [Choice D], [Choice E] ) " & _
> " SELECT TOP 1 tblExam.ID, tblExam.Question, tblExam.[Choice
     A], tblExam.[Choice B], tblExam.[Choice C], tblExam.[Choice D],
     tblExam.[Choice E] " & _
> " FROM tblExam " & _
> " WHERE (tblExam.[ID] = " & strID & ");"
>> DoCmd.RunSQL strAddQ
>> End Sub
>> The problem occurrs when the user tries to enter the same record twice.
     The following message appears:
>> Microsoft Office Access can't append all the records in the append query.
>> Microsoft Office Acccess set 0 field(s) to Null due to a type conversion
     failure, and it didn't add 1 recoord(s) to the table due to key violations,
     0 record(s) due to lock violations, and 0 record(s) due to validation rule
     violations. Do you want to run the action query anyway?
>> I don't want to add copies of the same record to the table. I just want
     to be able to error check for this in my code so that the user does not get
     confused when she makes the mistake of clicking on the button twice. Is
     there a way of checking to make sure that the current ID isn't already
     contained in the table.
>> Thanks very much in advance for your help,
>> Joe