访问VBA:查询值和目标字段的数量不同 [英] Access VBA: Number of query values and destination fields are not the same

问题描述

我正在尝试将Access表中的数据添加到表中.当我运行sql代码时，收到一条错误消息，提示查询值和目标字段的数量不相同."

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."

这是我的代码:

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

推荐答案

您的SQL字符串将传递到SQL引擎，该引擎不知道如何解释me!ComboProduct1等.您需要将这些变量的值插入到字符串:

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

在字符串周围加上单引号，但不要在数字周围加上单引号.我不确定您的某些字段是数字还是字符串-我猜了. 您需要注意日期-检查SQL引擎是否正确解释yyyy/mm/dd格式的日期.它将自动为您将字符串#2016/06/04#转换为日期.

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.

这篇关于访问VBA:查询值和目标字段的数量不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！