MS Access 2013调用来自VBA的插入查询,出现奇怪的错误 [英] MS Access 2013 calling insert queries from VBA with strange errors
问题描述
我有一个保存的插入查询,用于将记录添加到表中.查询的参数来自未绑定形式.该表中的2个字段为yes/no数据类型.这些字段的表单控件是一个复选框.
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个字符.
附录
如果我循环运行代码以查找系统,我也会得到Reserved error (-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屋!