error: 3061 - Too few parameters

[Stapes]

Hi I am trying to run this line of SQL: -

STRSQL = "INSERT INTO TM_CompContact ( FK_CompID, CTitle, FName, LName,
Job, [Group], SubGroup, Cat, " & _
"CreationDate, Source, Tog_import, Offer ) " & _
"SELECT TM_Customers.PK_Comp, TTemp_TextImport.Title,
TTemp_TextImport.FirstName, " & _
"TTemp_TextImport.Surname , TTemp_TextImport.[Job Title],
[Forms]![FM_ImportX]![Combo163] " & _
"AS [Group], [Forms]![FM_ImportX]![Combo165] AS SubGroup,
'TEACH' AS Cat, Date() " & _
"AS ExpDate, [Forms]![FM_ImportX]![Combo169] AS Source, -1 AS
TogImport, 1 AS Offer " & _
"FROM TTemp_TextImport INNER JOIN TM_Customers ON
TTemp_TextImport.[School URN] = TM_Customers.PK_COMP_ID;"

I get a error: 3061 Too few parameters. Expected 3.

And again with this: -

STRSQL = "INSERT INTO TM_CompContact ( FK_CompID, CTitle, FName, LName,
Job, [Group], SubGroup, Cat, CreationDate, Source, Tog_import, Offer )
" & _
"SELECT TTemp_AllMatchingPostCodes.URN1,
TTemp_AllMatchingPostCodes.Title, TTemp_AllMatchingPostCodes.FirstName,
" & _
"TTemp_AllMatchingPostCodes.Surname,
TTemp_AllMatchingPostCodes.[Job Title], [Forms]![FM_ImportX]![Combo163]
AS [Group], " & _
"[Forms]![FM_ImportX]![Combo165] AS SubGroup, 'TEACH' AS Cat,
Date() AS ExpDate, [Forms]![FM_ImportX]![Combo169] " & _
"AS Source, -1 AS TogImport, 1 AS Offer " & _
"FROM TTemp_AllMatchingPostCodes;"

Same error message.

What am I missing?

Stapes

 

[Douglas J. Steele]

Try putting the references to the form controls outside of the quotes:

STRSQL = "INSERT INTO TM_CompContact ( FK_CompID, CTitle, FName, LName, Job,
[Group], SubGroup, Cat, " & _
"CreationDate, Source, Tog_import, Offer ) " & _
"SELECT TM_Customers.PK_Comp, TTemp_TextImport.Title,
TTemp_TextImport.FirstName, " & _
"TTemp_TextImport.Surname , TTemp_TextImport.[Job Title], '" &
[Forms]![FM_ImportX]![Combo163] & _
"' AS [Group], '" & [Forms]![FM_ImportX]![Combo165] & "' AS
SubGroup, 'TEACH' AS Cat, Date() " & _
"AS ExpDate, '" & [Forms]![FM_ImportX]![Combo169] & "' AS
Source, -1 AS TogImport, 1 AS Offer " & _
"FROM TTemp_TextImport INNER JOIN TM_Customers ON
TTemp_TextImport.[School URN] = TM_Customers.PK_COMP_ID;"

Note the single quotes I've included to put around the values returned from
the controls (I'm assuming that Group, SubGroup and Source are all text
fields) Exagerated for clarity (and retyped in an attempt to avoid
word-wrap), that's

STRSQL = "INSERT INTO TM_CompContact ( FK_CompID, CTitle, " & _
"FName, LName, Job, [Group], SubGroup, Cat, " & _
"CreationDate, Source, Tog_import, Offer ) " & _
"SELECT TM_Customers.PK_Comp, TTemp_TextImport.Title, " & _
"TTemp_TextImport.FirstName, " & _
"TTemp_TextImport.Surname , TTemp_TextImport.[Job Title], ' " & _
[Forms]![FM_ImportX]![Combo163] & " ' AS [Group], ' " & _
[Forms]![FM_ImportX]![Combo165] & " ' AS SubGroup, & _
'TEACH' AS Cat, Date() & "AS ExpDate, ' " & _
[Forms]![FM_ImportX]![Combo169] & " ' AS Source, " & _
"-1 AS TogImport, 1 AS Offer " & _
"FROM TTemp_TextImport INNER JOIN TM_Customers " & _
"ON TTemp_TextImport.[School URN] = TM_Customers.PK_COMP_ID;"

(Note, too, that the aliases, provided by the AS keywords, aren't really
necessary. The INSERT INTO SQL doesn't care what names the subquery uses: it
works strictly by position)
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi I am trying to run this line of SQL: -

STRSQL = "INSERT INTO TM_CompContact ( FK_CompID, CTitle, FName, LName,
Job, [Group], SubGroup, Cat, " & _
"CreationDate, Source, Tog_import, Offer ) " & _
"SELECT TM_Customers.PK_Comp, TTemp_TextImport.Title,
TTemp_TextImport.FirstName, " & _
"TTemp_TextImport.Surname , TTemp_TextImport.[Job Title],
[Forms]![FM_ImportX]![Combo163] " & _
"AS [Group], [Forms]![FM_ImportX]![Combo165] AS SubGroup,
'TEACH' AS Cat, Date() " & _
"AS ExpDate, [Forms]![FM_ImportX]![Combo169] AS Source, -1 AS
TogImport, 1 AS Offer " & _
"FROM TTemp_TextImport INNER JOIN TM_Customers ON
TTemp_TextImport.[School URN] = TM_Customers.PK_COMP_ID;"

I get a error: 3061 Too few parameters. Expected 3.

And again with this: -

STRSQL = "INSERT INTO TM_CompContact ( FK_CompID, CTitle, FName, LName,
Job, [Group], SubGroup, Cat, CreationDate, Source, Tog_import, Offer )
" & _
"SELECT TTemp_AllMatchingPostCodes.URN1,
TTemp_AllMatchingPostCodes.Title, TTemp_AllMatchingPostCodes.FirstName,
" & _
"TTemp_AllMatchingPostCodes.Surname,
TTemp_AllMatchingPostCodes.[Job Title], [Forms]![FM_ImportX]![Combo163]
AS [Group], " & _
"[Forms]![FM_ImportX]![Combo165] AS SubGroup, 'TEACH' AS Cat,
Date() AS ExpDate, [Forms]![FM_ImportX]![Combo169] " & _
"AS Source, -1 AS TogImport, 1 AS Offer " & _
"FROM TTemp_AllMatchingPostCodes;"

Same error message.

What am I missing?

Stapes