A2013 / 365 DAO Recordset中的BUG [英] BUG in A2013/365 DAO Recordset

问题描述

我相信我在Access 2013 / office365 Pro中发现了一个可重复的错误

I believe I have found a repeatable bug in Access 2013 / office365 Pro

Access无法打开包含打开表单值的DAO记录集！

Access cannot open a DAO record set that includes a value from an open Form!

重现步骤：

1.  创建新表单添加未绑定文本域。 （txtTest），保存并打开表单在字段和标签中输入值
2.  根据任何表创建查询。从表中添加单个字段（alias =" a"）。然后使用"Build"添加一个新字段。这是指新表单txtTest字段：  [Forms]！frmTest]！[txtTest] 
3. 运行查询：错误1 ：它返回一个倒L字符。
4. 如上所述添加另一个字段（别名=" b"）但用一个像NZ这样的简单函数包围：  b：NZ（[Forms]！frmTest]！[txtTest]，0）
5. 运行查询。 NZ字段从表单返回正确的值。将查询保存为Query1
6. 在代码编辑器中创建一个新的子
7. 添加如下代码：

1.  Create a new Form add an unbound text field. (txtTest), Save and open form enter a value in the field and tab away
2.  Create a query based on any table. add a single field (alias="a" )from the table. Then add a new field using "Build" that refers to the new forms txtTest field:  [Forms]!frmTest]![txtTest] 
3. Run the query: Bug 1: it returns an inverted L character.
4. add another field (alias = "b") as above but surround with a simple function like NZ:   b:NZ([Forms]!frmTest]![txtTest] ,0)
5. Run the query. The NZ field returns the correct value from the Form. Save the Query as Query1
6. Create a new Sub in the Code editor
7. add code like:

Sub TestRS()
   Dim rs As DAO.Recordset
   Set rs = CurrentDb.OpenRecordset("Query1")
   rs.MoveFirst
   Debug.Print rs!a, rs!b
   rs.Close
   Set rs = Nothing
End Sub

运行sub：（F5）

BUG 2：错误3061 -Too少参数。预计1

BUG 2: Error 3061 -Too Few Parameters. Expected 1

请自行尝试并验证这些错误...

Please try it yourself and verify these bugs...

访问版本：15.0.4551.1506

Access Version: 15.0.4551.1506

Brian，ProcessIT- Hawke&＃Bays，New Zealand

Brian, ProcessIT- Hawke's Bay, New Zealand

推荐答案

我不得不说，我没有在2013/365进行测试。但我确实在MS Access 2010中尝试过。我可以重现您的错误。我对此的发现。

I have to say, I did not test it in 2013/365. But I did try in MS Access 2010. I could reproduce your errors. My findings on this.

BUG 1 - 这不是一个错误，它可以被归类为一个，但有一个完美的逻辑解释。由于Form控件未绑定，这意味着编译器的控件格式未知。因此，当您尝试访问Form
控件时，在Query表达式中，它会混淆并返回垃圾值。使用Nz是一种解决方法，因为它设置了一种格式。

BUG 1 - It is not a bug, well it can be categorized as one, but there is a perfectly logical explanation. Since the Form control is unbound which means the Format of the control is unknown for the Compiler. So when you try to access a Form control, in a Query expression it gets confused and returns garbage value. Using Nz is a work around as it does set a format.

"如果变种参数的值为 Null ，则
Nz 函数返回数字零或零长度字符串（在查询表达式中使用时始终返回零长度字符串），
取决于上下文是否指示值应为数字或字符串 。如果包含可选的
valueifnull 参数，那么 Nz 函数将返回该参数指定的值，如果
变量参数是 Null 。" - 来自MSDN参考。

"If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string. If the optional valueifnull argument is included, then the Nz function will return the value specified by that argument if the variant argument is Null." - From MSDN reference.

BUG 2 - 绝对不是错误。这一直存在，如果您尝试访问需要参数的预编译查询，将始终发生错误3061。双击查询1将提示您输入参数。在VBA中使用时，
通常使用连接Form控件值的实际SQL。类似的东西。

BUG 2 - Is definitely not a bug. This has always existed, if you try to Access a pre-compiled query that requires an argument, Error 3061 will always occur. Double clicking Query 1 will prompt you to enter the parameter. When using in VBA, you normally use the actual SQL concatenating the Form control values. Something like.

Public Sub simpleCheck()
    Dim sampStr As String, tmpRS As DAO.Recordset
    
    sampStr = "SELECT Agents.A_ID, '" & [Forms]![Frm_test]![testTxt] & "' AS A FROM Agents;"
    Set tmpRS = CurrentDb.OpenRecordset(sampStr)
    Debug.Print tmpRS.Fields(0) & " - " & tmpRS.Fields(1)
    Set tmpRS = Nothing
End Sub

所以这就是我要说的关于的问题。

So this is what I would have to say about the issues.

这篇关于A2013 / 365 DAO Recordset中的BUG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！