SQL code generates error message

[Guest]

I'm getting an error message when I press my button that states "The changes
you requested to the table were not successful because the would create
duplicate values in the index, primary key, or relationship. Chage the data
in the field or fields that contain duplicate data, remove the index, or
redefine the index to permit duplicate entries and try again"

The purpose of the code is to update another table from the form and a
"selectable" table embedded as a subform in the form and then to reset the
selections to not selected (model_select.[select] is a Y/N check box).
Anyone know where I'm going wrong with my code on this one?

The code for my button is shown below:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim strSql As String
Dim strSql2 As String


strSql = "INSERT INTO kit_to_model ( model, kit_num ) SELECT """ &
Me.[Combo1] & """ AS Kit_Num, [model_select].[model]FROM model_select WHERE
(([model_select].[select])=True);"
DBEngine(0)(0).Execute strSql, dbFailOnError

strSql2 = "UPDATE model_select SET model_select.[select] = False WHERE
((model_select.model) Is Not Null);"
DBEngine(0)(0).Execute strSql, dbFailOnError

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

 

[Ron Weiner]

Look Hard at the variable in your second DBEngine(0)(0).Execute command

It is currently
DBEngine(0)(0).Execute strSql, dbFailOnError

Should probably be
DBEngine(0)(0).Execute strSql2, dbFailOnError

Ron W

 

[Guest]

Thanks Ron,

I actually noticed it about 7-10 minutes after posting. I wish there was a
way to recall a question after performing your fifth 'idiot check' of the
code so as not to waste you guys' time. Thank you for the prompt response,
though! I'm glad there are resources like y'all out there!

- J

Look Hard at the variable in your second DBEngine(0)(0).Execute command

It is currently
DBEngine(0)(0).Execute strSql, dbFailOnError

Should probably be
DBEngine(0)(0).Execute strSql2, dbFailOnError

Ron W

I'm getting an error message when I press my button that states "The changes
you requested to the table were not successful because the would create
duplicate values in the index, primary key, or relationship. Chage the data
in the field or fields that contain duplicate data, remove the index, or
redefine the index to permit duplicate entries and try again"

The purpose of the code is to update another table from the form and a
"selectable" table embedded as a subform in the form and then to reset the
selections to not selected (model_select.[select] is a Y/N check box).
Anyone know where I'm going wrong with my code on this one?

The code for my button is shown below:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim strSql As String
Dim strSql2 As String


strSql = "INSERT INTO kit_to_model ( model, kit_num ) SELECT """ &
Me.[Combo1] & """ AS Kit_Num, [model_select].[model]FROM model_select WHERE
(([model_select].[select])=True);"
DBEngine(0)(0).Execute strSql, dbFailOnError

strSql2 = "UPDATE model_select SET model_select.[select] = False WHERE
((model_select.model) Is Not Null);"
DBEngine(0)(0).Execute strSql, dbFailOnError

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub