MS Access 2013 从 VBA 调用插入查询时出现奇怪的错误 [英] MS Access 2013 calling insert queries from VBA with strange errors

问题描述

我有一个已保存的插入查询，用于向表中添加记录.查询的参数来自未绑定的表单.表的 2 个字段是是/否数据类型.这些字段的表单控件是一个复选框.

I have a saved insert query to add a record to a table. The parameters for the query come from an unbound form. 2 of the fields for the table are of the yes/no data type. The form control for these fields is a checkbox.

这是保存的查询qryInsertLog"

Here is the saved query "qryInsertLog"

PARAMETERS UserPar Text ( 255 ), ApprovedByPar Text ( 255 ), CCedByPar Text ( 255 ), 
           UnitIdPar Short, NotePar LongText, Z3Par Bit, Z5Par Bit, FollowupNotesPar LongText;

INSERT INTO tblLogBook ( [User], ApprovedBy, CCedBy, UnitID, Notes, Z3, Z5, FollowupNotes )

SELECT [UserPAR] AS Expr1, [ApprovedByPar] AS Expr2, [CCedByPar] AS Expr3, 
       [UnitIDPar] AS Expr4, [NotePar] AS Expr5, [Z3Par] AS Expr6, [Z5Par] AS Expr7, 
       [FollowupNotesPar] AS Expr10;

这是我的 VBA 代码与表单上的保存按钮相关联:

Here is my VBA code tied to a save button on my form:

Private Sub cmdSaveandNew_Click()

Dim db As DAO.Database
Dim qdf1 As DAO.QueryDef

Set db = CurrentDb
Set qdf1 = db.QueryDefs("qryInsertLog")

'put form parameters into insert query
qdf1.Parameters(0).Value = Me.cboUser.Value
qdf1.Parameters(1).Value = Me.cboApprover.Value
qdf1.Parameters(2).Value = Me.cboCCer.Value
qdf1.Parameters(3).Value = Me.cboUnit.Value
qdf1.Parameters(4).Value = Me.txtNotes.Value
qdf1.Parameters(5).Value = Me.chkZ3.Value
qdf1.Parameters(6).Value = Me.chkz5.Value
qdf1.Parameters(7).Value = Me.txtFollowup.Value

qdf1.Execute

Set qdf1 = Nothing
Call resetForm

End Sub

resetForm"是一个简单的将表单控件返回到它们的默认值的例程.

"resetForm" is a routine that simply returns the form controls to their default values.

当我点击保存按钮时，有时会添加记录，但没有获得 Me.chkZ3.Value 和 Me.chkZ5.Value 的正确值(意味着如果我检查了它们，记录将使用这些值输入错误的).点击几次保存按钮后，我开始收到以下错误:

When I hit my save button, sometimes the record gets added, but without getting the correct values for Me.chkZ3.Value and Me.chkZ5.Value (meaning if I had them checked, the record gets entered with those values as false). After hitting the save button a few times, I start getting the following error:

Run-time error '3000':

Reserved error (-3033); there is no message for this error.

然而，最有趣的部分是，当我将查询修改为仅包含 1 个是/否字段时，我没有收到任何错误并且记录已正确更新.

The most interesting part about this, however, is that when I modify my query to only include 1 yes/no field, I get no errors and records are updated correctly.

这到底是怎么回事?

我忘了提到，当我从访问对象窗口(而不是 VBA)运行查询时，它完全按预期工作.

edit: I forgot to mention that when I run the query from the Access Objects window (as opposed to VBA), it works exactly as it should.

推荐答案

问题在于 LongText 参数.我尝试了类似的查询:

The problem are the LongText parameters. I tried a similar query:

PARAMETERS pText1 Text ( 255 ), pInt1 Short, pMemo1 LongText, pYesno1 Bit, pYesno2 Bit;
INSERT INTO ForInsert ( text1, int1, Memo1, yesno1, yesno2 )
SELECT [pText1] AS A1, [pInt1] AS A2, [pMemo1] AS A3, [pYesno1] AS A4, [pYesno2] AS A5;

使用此代码

Set db = CurrentDb
Set qdf1 = db.QueryDefs("qAppForInsert")

qdf1.Parameters(0).Value = "asdf"
qdf1.Parameters(1).Value = 77
qdf1.Parameters(2).Value = String(3, "a")
qdf1.Parameters(3).Value = True
qdf1.Parameters(4).Value = False

qdf1.Execute

以及 LongText 参数的各种长度 (2, 10, 3) .

and various length for the LongText parameter (2, 10, 3) .

导致这个疯狂的数据(是/否字段总是 yesno1 = True 和 yesno2 = False ！):

Resulting in this crazy data (the Yes/No fields were always yesno1 = True and yesno2 = False !) :

+----+-------+------+------------+--------+--------+
| ID | text1 | int1 |   Memo1    | yesno1 | yesno2 |
+----+-------+------+------------+--------+--------+
|  8 | asdf  |   77 | aa         | True   | False  |
|  9 | asdf  |   77 | aaaaaaaaaa | False  | False  |
| 10 | asdf  |   77 | aaa        | False  | True   |
+----+-------+------+------------+--------+--------+

显然对于 LongText 参数，最好使用 RecordSet.AddNew 而不是参数化查询.

So apparently with LongText parameters, you are better off using RecordSet.AddNew instead of a parameterized query.

无论如何，参数限制为 255 个字符.

附录

如果我在循环中运行代码来查找系统，我也会得到保留错误 (-3033).

If I run the code in a loop to find a system, I also get the Reserved error (-3033).

这篇关于MS Access 2013 从 VBA 调用插入查询时出现奇怪的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！