Error with Append Query

[Guest]

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 Strin
Dim strID As Strin

strID = Me.I

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 strAdd

End Su

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

 

[Graham Mandeno]

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

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.

 

[Guest]

Thanks Graham -- that's exactly what I wanted

Jo

----- Graham Mandeno wrote: ----

Hi Jo

Instead o
DoCmd.RunSQL strAdd
use this
CurrentDb.Execute strAddQ, dbFailOnErro

Then, when an error occurs, you can trap it, check the Err.Number for th
error code you want to ignore, and do a Resume Next (or whatever else yo
require)
--
Good Luck

Graham Mandeno [Access MVP
Auckland, New Zealan

Hello All

record is being displayed to a table called "tblUser." The gist of the cod
is shown below

Private Sub cmdSaveRecord_Click(
Dim strAddQ as Strin Dim strID As Strin
strID = Me.I
strAddQ = "INSERT INTO tblUser ( ID, Question, [Choice A], [Choice B]

[Choice C], [Choice D], [Choice E] ) " &

" SELECT TOP 1 tblExam.ID, tblExam.Question, tblExam.[Choic

A], tblExam.[Choice B], tblExam.[Choice C], tblExam.[Choice D]
tblExam.[Choice E] " &

" FROM tblExam " &
" WHERE (tblExam.[ID] = " & strID & ");

DoCmd.RunSQL strAdd
End Su
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 conversio

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 rul
violations. Do you want to run the action query anywayto be able to error check for this in my code so that the user does not ge
confused when she makes the mistake of clicking on the button twice. I
there a way of checking to make sure that the current ID isn't alread
contained in the table