I am trying to add data from an Access form to a table. When I ran sql code, I got an error message saying "number of query values and destination fields are not the same."
this is my code:
Private Sub CmdAddtoProductionDetails_Click()
Dim StrSql As String
StrSql = "Insert Into test1 (ProductName, [Lot Number], ProductionDate, Quantity, Unit, Client) Values(me! ComboProduct1, me! LotNoProduct1, me! txtDate, me! Product1Quantity! me!ComboProduct1Unit, me! ComboProduct1Client)"
CurrentDb.Execute (StrSql)
End Sub
Your SQL string will be passed to the SQL engine which does not know how to interpret me!ComboProduct1 etc. You need to insert the values of those variables into the string:
Private Sub CmdAddtoProductionDetails_Click()
Dim StrSql As String StrSql = "Insert Into test1 (ProductName, [Lot Number], ProductionDate, Quantity, Unit, Client)" & _
" Values( '" & me! ComboProduct1 & "', '" & me! LotNoProduct1 & "', #" & Format(me! txtDate, "yyyy/mm/dd") & "#, " & CStr(me! Product1Quantity) & ", '" & me!ComboProduct1Unit & "', '" & me! ComboProduct1Client & "' )"
CurrentDb.Execute (StrSql)
End Sub
Put single quotes around strings but not around numbers. Some of your fields I wasn't sure if they were numbers or strings - I made a guess.
You need to be careful with dates - check that the SQL engine is interpreting dates in yyyy/mm/dd format correctly. It will convert the string #2016/06/04# to a date automatically for you.
A simpler and more direct method is to use a recordset:
Private Sub CmdAddtoProductionDetails_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select Top 1 * From test1")
rs.AddNew
rs!ProductName.Value = Me!ComboProduct1.Value
rs![Lot Number].Value = Me!LotNoProduct1.Value
rs!ProductionDate.Value = Me!txtDate.Value
rs!Quantity.Value = Me!Product1Quantity.Value
rs!Unit.Value = Me!ComboProduct1Unit.Value
rs!Client.Value = Me!ComboProduct1Client.Value
rs.Update
rs.Close
Set rs = Nothing
End Sub
