vba 访问查询需要绕过“输入参数值”错误，消息框提示未找到字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11141338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access query need to bypass "enter parameter value" error with a msg box saying field not found
提问by JenPhyllisB
I have a simple query tied to a command button that shows a summary of the values in a particular field. It's running on a table that changes with each use of the database, so sometimes the table will contain this field and sometimes it won't. When the field (called Language) is not in the file, the user clicks the command button and gets the "Enter Parameter Value" message box. If they hit cancel they then get my message box explaining the field is not present in the file. I would like to bypass the "Enter Parameter Value" and go straight to the message if the field is not found. Here is my code:
我有一个与命令按钮相关的简单查询，该按钮显示特定字段中的值的摘要。它运行在一个随着数据库的每次使用而改变的表上，因此有时该表将包含该字段，有时则不会。当该字段（称为语言）不在文件中时，用户单击命令按钮并获取“输入参数值”消息框。如果他们点击取消，他们就会收到我的消息框，说明文件中不存在该字段。如果找不到该字段，我想绕过“输入参数值”并直接转到消息。这是我的代码：
Private Sub LangCount_Click()
DoCmd.SetWarnings False
On Error GoTo Err_LangCount_Click
Dim stDocName As String
stDocName = "LanguageCount"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Err_LangCount_Click:
MsgBox "No Language field found in Scrubbed file"
Exit_LangCount_Click:
Exit Sub
DoCmd.SetWarnings True
End Sub
回答by Fionnuala
You can attempt to open a recordset based on the query before you run the query:
在运行查询之前，您可以尝试根据查询打开记录集：
Set rs = CurrentDb.QueryDefs("query1").OpenRecordset
This will go straight to the error coding if anything is wrong with the query.
如果查询有任何问题，这将直接进入错误编码。
Alternatively, if it is always the language field and always in the same table, you can:
或者，如果它始终是语言字段并且始终在同一个表中，您可以：
sSQL = "select language from table1 where 1=2"
CurrentDb.OpenRecordset sSQL
This will also fail and go to your error coding, but if it does not fail, you will have a much smaller recordset, one with zero records.
这也将失败并转到您的错误编码，但如果它没有失败，您将拥有一个小得多的记录集，一个记录集为零。
You can easily enough get a list of fields in a table with ADO Schemas:
您可以很容易地使用 ADO 模式获取表中的字段列表：
Dim cn As Object ''ADODB.Connection
Dim i As Integer, msg As String
Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaColumns, Array(Null, Null, "Scrubbed"))
While Not rs.EOF
i = i + 1
msg = msg & rs!COLUMN_NAME & vbCrLf
rs.MoveNext
Wend
msg = "Fields: " & i & vbCrLf & msg
MsgBox msg
More info: http://support.microsoft.com/kb/186246
回答by HansUp
You have a command button named LangCount. It's click event has to deal with the possibility that a field named Languageis not present in your Scrubbedtable.
您有一个名为 的命令按钮LangCount。它的点击事件必须处理表中Language不存在命名字段的可能性Scrubbed。
So then consider why a user should be able to click that command button when the Languagefield is not present. When the field is not present, you know the OpenQuerywon't work (right?) ... so just disable the command button.
因此，请考虑为什么当该Language字段不存在时用户应该能够单击该命令按钮。当该字段不存在时，您知道这OpenQuery将不起作用（对吗？）...所以只需禁用命令按钮。
See if the following approach points you to something useful.
看看以下方法是否为您指明了一些有用的东西。
Private Sub Form_Load()
Me.LangCount.Enabled = FieldExists("Language", "Scrubbed")
End Sub
That could work if the structure of Scrubbed doesn't change after your form is opened. If the form also includes an option to revise Scrubbedstructure, update LangCount.Enabledfrom that operation.
如果打开表单后 Scrubbed 的结构没有改变，这可能会起作用。如果表单还包含修改Scrubbed结构的选项，LangCount.Enabled则从该操作进行更新。
Here is a quick & dirty (minimally tested, no error handling) FieldExists()function to get you started.
这是一个快速而肮脏的（最少测试，无错误处理）FieldExists()功能，可帮助您入门。
Public Function FieldExists(ByVal pField As String, _
ByVal pTable As String) As Boolean
Dim blnReturn As Boolean
Dim db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Set db = CurrentDb
' next line will throw error #3265 (Item not found in this collection) '
' if table named by pTable does not exist in current database '
Set tdf = db.TableDefs(pTable)
'next line is not actually needed '
blnReturn = False
For Each fld In tdf.Fields
If fld.Name = pField Then
blnReturn = True
Exit For
End If
Next fld
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
FieldExists = blnReturn
End Function
