vba 运行时错误 3464：条件表达式中的数据类型不匹配

Run Time Error 3464: Data Type Mismatch in criteria expression

提问by Prithvi Raj

I have a form in Access 2010 with Two text boxes(AIPIDTxt to enter the search criteria and AIPResultTxt to display results) and a Button(Search button). I also have a Table Table1 in Access. When I click the Search Button, I need to execute a query whose criteria is in AIPIDTxt Textbox in the form, store the result in a recordset and display the results in the textbox AIPResultTxt. So I typed in the following VBA Code in the Button Event handler.

我在 Access 2010 中有一个表单，其中有两个文本框（输入搜索条件的 AIPIDTxt 和显示结果的 AIPResultTxt）和一个按钮（搜索按钮）。我在 Access 中也有一个表 Table1。当我单击搜索按钮时，我需要执行一个查询，其条件在表单中的 AIPIDTxt 文本框中，将结果存储在记录集中并在文本框 AIPResultTxt 中显示结果。所以我在按钮事件处理程序中输入了以下 VBA 代码。

Private Sub SearchB_Click()

Dim localConnection As ADODB.Connection
Dim query As String
Dim aipid_rs As ADODB.Recordset
Dim db As Database

Set db = CurrentDb
Set localConnection = CurrentProject.AccessConnection
MsgBox "Local Connection successful"
query = "SELECT [AIP Name] FROM [Table1] WHERE [AIP ID]= 
" & [Forms]![AIPIDSearchF]![AIPIDTxt] & ""
Set aipid_rs = db.OpenRecordset(query)
Me.AIPResultTxt.Text = aipid_rs![AIP Name]
End Sub

But when I click the button I get Local Connection Successful Message Box and then a Run Time Error 3464 in the line:

但是当我单击按钮时，我收到本地连接成功消息框，然后在行中出现运行时错误 3464：

Set aipid_rs= db.OpenRecordset(query)

I have searched for similar errors and made corrections. But the error keeps coming. Is there something wrong with my query? Couldn't figure out the error. The table is a local table. So I can directly give [Table1] and field names in the query in vba. Tried adding delimiters because the fields are text fields. But that didn't work as well. I could not give the following query as well:

我已经搜索了类似的错误并进行了更正。但错误不断出现。我的查询有问题吗？无法弄清楚错误。该表是本地表。所以我可以在vba中直接给出查询中的[Table1]和字段名。尝试添加分隔符，因为这些字段是文本字段。但这并不奏效。我也无法给出以下查询：

query = "SELECT [AIP Name] FROM [Table1] WHERE [AIP ID]= " & [Forms]![AIPIDSearchF]!
[AIPIDTxt].Text & ""

This gave me a run time error stating text cannot be referenced from controls that have lost focus. My criteria is text in the text box. The text box loses focus when i click the button. But when I googled for the error, solutions were to remove ".Text". So, I ended up with the above query. Do not know what is wrong with the line:

这给了我一个运行时错误，指出无法从失去焦点的控件中引用文本。我的标准是文本框中的文本。当我单击按钮时，文本框失去焦点。但是当我搜索错误时，解决方案是删除“.Text”。所以，我最终得到了上面的查询。不知道这行有什么问题：

Set aipid_rs= db.OpenRecordset(query)

回答by HansUp

I suspect you have more than one problem with that code. But Access complains about only the first problem it finds. Look again at these 2 lines ...

我怀疑您对该代码有不止一个问题。但是 Access 只抱怨它发现的第一个问题。再看看这两行...

Dim aipid_rs As ADODB.Recordset
Set aipid_rs = db.OpenRecordset(query)

OpenRecordsetis a DAO method which returns a DAO recordset. But the code attempts to assign it to aipid_rswhich was declared As ADODB.Recordset. Those recordset types are not compatible.

OpenRecordset是一个返回 DAO 记录集的 DAO 方法。但是代码试图将它分配给aipid_rs已声明的As ADODB.Recordset。这些记录集类型不兼容。

There is an ADO connection object variable, localConnection, which is not used for anything later. Although it doesn't trigger an error, it's just not useful. And actually I don't see any reason to use anything from ADO for this task.

有一个 ADO 连接对象变量localConnection，以后不再使用它。虽然它不会触发错误，但它只是没有用。实际上，我看不出有任何理由在此任务中使用 ADO 中的任何内容。

I suggest you try this version of your code ...

我建议你试试这个版本的代码......

'Dim localConnection As ADODB.Connection
'Dim query As String ' query is a reserved word
Dim strQuery As String
'Dim aipid_rs As ADODB.Recordset
Dim aipid_rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb
'Set localConnection = CurrentProject.AccessConnection
'MsgBox "Local Connection successful"
' you said [AIP ID] is text type, so include quotes around 
' the text box value
strQuery = "SELECT [AIP Name] FROM [Table1] WHERE [AIP ID]= '" & _
    [Forms]![AIPIDSearchF]![AIPIDTxt] & "'"
Debug.Print strQuery
DoCmd.RunCommand acCmdDebugWindow
Set aipid_rs = db.OpenRecordset(strQuery)
'Me.AIPResultTxt.Text = aipid_rs![AIP Name] ' .Text property is only
    ' available when control has focus; it will trigger 
    ' an error at any other time                           
Me.AIPResultTxt.Value = aipid_rs![AIP Name]

Note Debug.Print strQuerywill display the text of the SELECTstatement in the Immediate window, and DoCmd.RunCommand acCmdDebugWindowopens the Immediate window. If you still have a problem with the query, copy the statement text and paste it into SQL View of a new query for testing.

NoteDebug.Print strQuery将SELECT在立即窗口中显示语句的文本，并DoCmd.RunCommand acCmdDebugWindow打开立即窗口。如果查询仍有问题，请复制语句文本并将其粘贴到新查询的 SQL 视图中进行测试。

Finally I'm curious whether this might give you what you need with much less code ...

最后，我很好奇这是否可以用更少的代码为您提供所需的东西......

Private Sub SearchB_Click()
    Me.AIPResultTxt.Value = DLookup("[AIP Name]", "Table1", _
        "[AIP ID]='" & Me.AIPIDTxt & "'")
End Sub

回答by Team Renegade Prodcutions

OK, So I have been searching for a simple search element for quite awhile... I have tried using subforms and I have tried recordsets. All of these have given me the information. But not the compactness I was looking for, thanks!!!

好的，所以我一直在寻找一个简单的搜索元素很长一段时间......我尝试过使用子表单并尝试过记录集。所有这些都给了我信息。但不是我正在寻找的紧凑性，谢谢！！！

using the DOA stuf is great but not for my application. using the above:

使用 DOA stuf 很棒，但不适用于我的应用程序。使用上述：

Me.AIPResultTxt.Value = DLookup("[AIP Name]", "Table1", _ "[AIP ID]='" & Me.AIPIDTxt & "'")

Me.AIPResultTxt.Value = DLookup("[AIP Name]", "Table1", _ "[AIP ID]='" & Me.AIPIDTxt & "'")

I have the compactness of code I was looking for... THanks!!! T

我有我正在寻找的代码的紧凑性......谢谢！！！吨

回答by Wayne G. Dunn

You are using an ADO recordset, therefore your open syntax is incorrect. The following should work for you (except you may get an error setting the text if the control doesn't have focus...)

您使用的是 ADO 记录集，因此您的打开语法不正确。以下应该对您有用（除非您可能会在控件没有焦点的情况下设置文本时出现错误...）

Dim localConnection As ADODB.Connection
Dim query           As String
Dim aipid_rs        As ADODB.Recordset
Dim db              As Database

Set db = CurrentDb
Set localConnection = CurrentProject.AccessConnection
MsgBox "Local Connection successful"
query = "SELECT [AIP Name] FROM [Table1] WHERE [AIP ID]= '" & [Forms]![AIPIDSearchF]![AIPIDTxt] & "'"
'Set aipid_rs = db.OpenRecordset(query)
Set aipid_rs = New ADODB.Recordset
aipid_rs.Open query, localConnection, adOpenStatic, adLockReadOnly
If Not aipid_rs.EOF Then
    Me.AIPResultTxt.Text = aipid_rs![AIP Name]
Else
    MsgBox "No records!!"
End If
aipid_rs.Close
Set aipid_rs = Nothing
localConnection.Close
Set localConnection = Nothing
db.Close
Set db = Nothing