0

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

PatrickStel
  • 45
  • 1
  • 9
  • Is it accept null values? – anas p a Dec 18 '15 at 11:49
  • Do you mean the empty field? or do you mean the Yes/No fields? – PatrickStel Dec 18 '15 at 11:53
  • You probably make a typo with this field: `OwnGoals`. In your query you have `OwnGoal` - without s. Also, I am not sure if you can give empty string `''` as a `PlayerID` field if it is of Number type (the same with other fields, i.e. `SubstituteID`) - maybe try to change it to 0 instead of empty string. – mielk Dec 18 '15 at 11:55
  • Sorry my bad, thats a typo when I filled it in here. It's both OwnGoal No joy, even if I give those fields a value of 0 it keeps saying the same error. :( – PatrickStel Dec 18 '15 at 11:57
  • Another thing is that the last value: `Bruins` should be in quotations. – mielk Dec 18 '15 at 12:01
  • Okay, so now I have both names in quotations, `surname` and `assist` now I don't get the error to few parameters anymore but it isn't writing in the table as well... – PatrickStel Dec 18 '15 at 12:11
  • [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) – Andre Dec 18 '15 at 12:14
  • Never mind my reply from not working, I forgot to refresh my table :), thanks for the help. it's working now :) – PatrickStel Dec 18 '15 at 12:17

1 Answers1

0

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.

AVG
  • 1,317
  • 8
  • 12