Access asking me to enter a parameter value

Question

I'm running an SQL query through VB in Microsoft Access for form to add records to a table. However, it keeps asking me to insert parameter value, when they are already present in the form.

The form

Private Sub AddPart_Click()

Dim strSQL As String

strSQL = "INSERT INTO Part VALUES (" & Me.IdPartPrimary.Value & ", " & Me.NamePartPrimary.Value & ", " & Me.BrandPartPrimary.Value & ", " & Me.ModelPartPrimary.Value & ", " & Me.FunctionPartPrimary.Value & ", -1, " & Me.FatherPartPrimary.Value & ", " & Me.ProviderPartPrimary.Value & ", " & Me.AmountPartPrimary.Value & ");"

DoCmd.RunSQL strSQL


End Sub

I already checked for spelling mistakes and there were none. Also, this happens with every field. If I don't insert a parameter value and cancel instead, the record still gets added, only after I close and reopen the table lots of times.

I didn't think it was necessary since the values are in the correct order — LaCosaCoding, Aug 24 '22 at 22:53
`I already checked for spelling mistakes and there were none` - apart from the missing quotes around the values, I assume, which makes them column names rather than values. Also please see https://stackoverflow.com/q/332365/11683. — GSerg, Aug 24 '22 at 23:02
How is the ID value generated? Is this user input or autonumber field? If autonumber then this must be a bound form and there is no need for SQL. If this is unbound form and code fails, record should not be created. Described behavior 'the record still gets added, only after I close and reopen the table lots of times' makes no sense. — June7, Aug 25 '22 at 02:49

June7 · Answer 1 · 2022-08-31T19:00:00.733

If fields are text type, need text delimiters - apostrophe will serve. I assume comboboxes have a number value from a hidden foreign key column. Value property does not need to be specified as it is the default.

With Me
strSQL = "INSERT INTO Part " & _
         " VALUES (" & .IdPartPrimary & ", '" & _
         .NamePartPrimary & "', '" & .BrandPartPrimary & "', '" & _
         .ModelPartPrimary & "', '" & .FunctionPartPrimary & "', -1, " & _
         .FatherPartPrimary & ", " & .ProviderPartPrimary & ", " & .AmountPartPrimary & ")"
End With

AFAIK, Access cannot execute multi-action SQL statements but SQL injection is still possible. If you want to explore use of Parameters in VBA, review How do I use parameters in VBA in the different contexts in Microsoft Access?

Another alternative to avoid SQL injection is to open a recordset, use its AddNew method to create a new record, and set value of each field. DAO.Recordset AddNew

Access asking me to enter a parameter value

1 Answers1