CODEWITHSUNDEEP
sqlms-accessvbaappend
jiangzhou He
jiangzhou He

Reputation: 57

Access VBA: Number of query values and destination fields are not the same

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

Upvotes: 1

Views: 422

Answers (2)

Gustav
Gustav

Reputation: 55806

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

Upvotes: 1

John D
John D

Reputation: 1637

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.

Upvotes: 0

Related Questions