Access INSERT INTO - to few parameters. expected 2

Good afternoon,

I'm having a problem with a INSERT statement in VBA. Everytime I'm trying to insert a new record to the table through input from the form I'm getting a error.

Run-time error '3061': Too few parameters. Expected 2.

And this is the VBA behind it.

Private Sub Command242_Click()
Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = CurrentDb

'Testing purpose
Me.cbPenalty1 = 0
Me.cbOwnGoal1 = 0
' Create a new record in the tblMatchPlayer table.
' Query saved the player who scored with values in a new row, linked with MatchID & PlayerID.

'Testing purpose MsgBox - DELETE WHEN WORKS!!
MsgBox " INSERT INTO tblMatchPlayer " _
        & "(MatchID, PlayerID, SubstituteID, PositionID, Surname, ScoreTime, RedCards, YellowCards, Substitude, Penalty, OwnGoal, Assist) VALUES " _
        & "(" & Me.MatchID & ", '', '', '', " & Me.cmScoreName1 & ", " & Me.tbScoreTime1 & ", '', '', '', " & Me.cbPenalty1 & ", " & Me.cbOwnGoal1 & ", " & Me.cmAssist1 & ");", vbOKOnly, "Query Show"
'Actual INSERT
dbs.Execute " INSERT INTO tblMatchPlayer " _
        & "(MatchID, PlayerID, SubstituteID, PositionID, Surname, ScoreTime, RedCards, YellowCards, Substitude, Penalty, OwnGoal, Assist) VALUES " _
        & "(" & Me.MatchID & ", '', '', '', " & Me.cmScoreName1 & ", " & Me.tbScoreTime1 & ", '', '', '', " & Me.cbPenalty1 & ", " & Me.cbOwnGoal1 & ", " & Me.cmAssist1 & ");"

dbs.Close

End Sub

When the MsgBox pop-ups up to show me the Query it's going to write in the table I'm getting these results.

INSERT INTO tblMatchPlayer (MatchID, PlayerID, substituteID, PositionID, Surname, ScoreTime, RedCards, YellowCards, Substitude, Penalty, OwnGoal, Assist) VALUES (29, '', '', '', Grozema, 34, '', '', '', 0, 0, Bruins);

I can't see anything wrong with this insert query.. however VBA does seems to think he is missing some parameters but I don't know what parameters.

The fields in my table are as this.

MatchPlayerID - Autonumber
MatchID - Number
PlayerID - Number
SubstituteID - Number
PositionID - Number
Surname - Text
ScoreTime - Text
RedCards - Text
YellowCards - Text
Substitude - Text
Penalty - Yes/No
OwnGoal - Yes/No
Assist - Text

Can you guys help me out?

With kind regards, Patrick

Solution

Instead of viewing your sql statement in a MsgBox, debug.print it. Then copy it, create a new query, switch to SQL view and paste the statement as the SQL. Switch to design view and the problem will rear it's head.