CODEWITHSUNDEEP
sqlms-accessvba
Patrick
Patrick

Reputation: 113

Access 2010 VBA Too few parameters: Expected 3

In VBA for Access 2010 and I am just trying to insert some data into a subform. I have a form, with 3 subforms on it. I have 3 unbound text boxes that the user will enter data, hit a command button, and then that data will instert into the subform I have on the layout. Here is my code:

Private Sub cmdAssign_Click()

CurrentDb.Execute " INSERT INTO Crew " _
    & "(txtCrewName,txtKitNumber,txtActionDate) VALUES " _
    & "(txtAssignCrew, txtAssignKit, txtAssignDate);"

End Sub

I know it has got to be something simple but I'm not really familiar with this. The txtCrewName, txtKitNumber, and txtActionDate are the empty values in the subform where I want data to go. And the txtAssignCrew, txtAssignKit, and txtAssignDate are the unbounds on the form, but outside of the subform 'Crew'. Any ideas? Thanks

EDIT: Figured it out. Thank you all for the help

Upvotes: 1

Views: 2788

Answers (3)

HansUp
HansUp

Reputation: 97101

So txtAssignCrew, txtAssignKit, and txtAssignDate are the names of controls on the form. The db engine doesn't know what they are, so assumes they must be parameters.

You could build those controls' values (instead of the controls' names) into your INSERT statement. But consider an actual parameter query instead, and execute it from a QueryDef object.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strInsert As String

strInsert = "PARAMETERS crew_name Text(255), kit_num Long, action_date DateTime;" & vbCrLf & _
    "INSERT INTO Crew (txtCrewName,txtKitNumber,txtActionDate)" & vbCrLf & _
    "VALUES (crew_name, kit_num, action_date);"
Debug.Print "strInsert:" & vbCrLf & strInsert
Set db = CurrentDb
Set qdf = db.CreateQueryDef("", strInsert)
qdf.Parameters("crew_name") = Me.txtAssignCrew
qdf.Parameters("kit_num") = Me.txtAssignKit
qdf.Parameters("action_date") = Me.txtAssignDate
qdf.Execute dbFailOnError
Set qdf = Nothing
Set db = Nothing

Note, in the PARAMETERS clause I assumed text as the data type for txtCrewName, long integer for txtKitNumber, and Date/Time for txtActionDate. If all are text, adjust the PARAMETERS clause.

Upvotes: 2

Tarik
Tarik

Reputation: 11209

Corrected code: CurrentDb.Execute " INSERT INTO Crew " _ & "(txtCrewName,txtKitNumber,txtActionDate) VALUES " _ & "('" & txtAssignCrew & "','" & txtAssignKit & "','" & txtAssignDate & ");"

Notes: - You might need to change the format for the AssignDate parameter - Column names in the database are usually not prefixed with txt - You would be better off using parametized queries to avoid SQL injection attacks

Upvotes: 1

Scotch
Scotch

Reputation: 3226

This should work, assuming txtAssignCrew, Kit, and Date are your unbound controls you want to insert. Realize that if there are single quotes or anything in those fields, it would cause the statement to fail, so you would need to escape them.

 Private Sub cmdAssign_Click()

 CurrentDb.Execute " INSERT INTO Crew " _
  & "(txtCrewName,txtKitNumber,txtActionDate) VALUES " _
  & "('" & me.txtAssignCrew & "', '" & me.txtAssignKit & "','" & me.txtAssignDate & ");"

 End Sub

I may have botched a quote.

Upvotes: 1

Related Questions