SQL question on "Insert into "

[johnb]

I'm trying to loop thru all non PK or FK controls on a Form/Subform using the
sql statement below, but I get an error message "Number of query values and
destination field do not match".

I'd be grateful if some kind person point out the error of my ways!

TIA johnb

If Me.[SubForm1].Form.RecordsetClone.RecordCount > 0 Then
For Each ctl In Me.Controls

If ctl.Name = "Parts_ID" Or ctl.Name = "CPD_ID" Or ctl.ControlType =
acLabel Then
GoTo Flag1
End If

strSql = "INSERT INTO [tbl_Parts]( " & ctl.Name & ") " & _
"SELECT " & lngID & " As CPS_ID, " & ctl.Name & _
" FROM [tbl_parts] WHERE CPS_ID = " & Me.CPS_ID & ";"
Debug.Print strSql
DBEngine(0)(0).Execute strSql, dbFailOnError
Flag1:
Next ctl
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

 

[Stefan Hoffmann]

hi John,

GoTo Flag1

Shame upon you for using GoTo.

If Me.[SubForm1].Form.RecordsetClone.RecordCount > 0 Then

For Each ctl In Me.Controls
If Not (ctl.Name = "Parts_ID" Or _
ctl.Name = "CPD_ID" Or _
ctl.ControlType = acLabel) Then
strSql = "INSERT INTO [tbl_Parts]( " & ctl.Name & ") " & _
"SELECT " & lngID & " As CPS_ID, " & ctl.Name & _
" FROM [tbl_parts] WHERE CPS_ID = " & Me.CPS_ID & ";"
Debug.Print strSql
DBEngine(0)(0).Execute strSql, dbFailOnError
End If
Next ctl

Else
MsgBox "Main record duplicated, " & _
"but there were no related records."
End If


The number of fields to insert

INSERT INTO Table(fields)

must match the number of fields in your select

SELECT fields

..

Your insert field list consists of one field, but your select has two
fields.

mfG
--> stefan <--

 

[johnb]

Cheers Stefan

hi John,

GoTo Flag1

Shame upon you for using GoTo.

If Me.[SubForm1].Form.RecordsetClone.RecordCount > 0 Then

For Each ctl In Me.Controls
If Not (ctl.Name = "Parts_ID" Or _
ctl.Name = "CPD_ID" Or _
ctl.ControlType = acLabel) Then
strSql = "INSERT INTO [tbl_Parts]( " & ctl.Name & ") " & _
"SELECT " & lngID & " As CPS_ID, " & ctl.Name & _
" FROM [tbl_parts] WHERE CPS_ID = " & Me.CPS_ID & ";"
Debug.Print strSql
DBEngine(0)(0).Execute strSql, dbFailOnError
End If
Next ctl

Else
MsgBox "Main record duplicated, " & _
"but there were no related records."
End If


The number of fields to insert

INSERT INTO Table(fields)

must match the number of fields in your select

SELECT fields

..

Your insert field list consists of one field, but your select has two
fields.

mfG
--> stefan <--