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

hi John,

johnb wrote:
> 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 <--

Cheers Stefan

"Stefan Hoffmann" wrote:

> hi John,
>
> johnb wrote:
> > 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 <--
>
>