Too few parameters. Expected 1 2

[lars7]

Hi There,
I'm getting a "Too few parameters. Expected 1." message with this code, can anyone see why.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
Dim strSQL As String


strSQL = "INSERT INTO tblTeams ( TGameNo, TTeam, TFor, TAgainst )" & _
"SELECT tblGameScore.GameNo, tblfixtures.FTeam1, tblGameScore.For, tblGameScore.Against " & _
"FROM tblGameScore INNER JOIN tblfixtures ON tblGameScore.GameNo = tblfixtures.GameNo " & _
"WHERE (((tblGameScore.GameNo)=[forms]![frmDataEntry]![GameNo])) "

CurrentDb.Execute strSQL, dbFailOnError


Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

It works without the where clause.

 

[Ed2020]

Try:

"WHERE (((tblGameScore.GameNo)="""" & [forms]![frmDataEntry]![GameNo] & """" & ")) "

Ed Metcalfe.


Please do not feed the trolls.....

 

[lars7]

Hi Ed,
Sorry I had to change the code slightly but i tried this and it does not like the ")) saying "Expected end of Statement"

strSQL = "INSERT INTO tblTeams ( TTeam, TFor, TAgainst )" & _
"SELECT tblfixtures.FTeam1, tblGameScore.For, tblGameScore.Against " & _
"FROM tblGameScore INNER JOIN tblfixtures ON tblGameScore.GameNo = tblfixtures.GameNo " & _
"WHERE (((tblfixtures.GameNo)="""" &[forms]![frmDataEntry]![GameNo]))& """" & ")) "

 

[Ed2020]

Too many close brackets I think. I've never been sure why MS Access puts them all in anyway.

Give this a go:

strSQL = "INSERT INTO tblTeams ( TTeam, TFor, TAgainst )" & _
"SELECT tblfixtures.FTeam1, tblGameScore.For, tblGameScore.Against " & _
"FROM tblGameScore INNER JOIN tblfixtures ON tblGameScore.GameNo = tblfixtures.GameNo " & _
"WHERE tblfixtures.GameNo="""" &[forms]![frmDataEntry]![GameNo]& """" & ";"

Please do not feed the trolls.....

 

[PHV]

And if tblGameScore.GameNo is defined as numeric:
"WHERE tblGameScore.GameNo=" & Forms!frmDataEntry!GameNo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Thanks guys for the help, I got a syntax error with ed's code ";" but PHV your's worked great.

Tom

 

[Ed2020]

PHV - Well spotted.

Ed.

Please do not feed the trolls.....