访问 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 字符串将被传递给不知道如何解释 me!ComboProduct1 等的 SQL 引擎，你需要插入将这些变量的值放入字符串中:

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屋！