vba 使用access vba计算sql查询结果中的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23193037/
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
count the number of rows in sql query result using access vba
提问by Mohammed AL Jakry
I am trying to count the number of rows in sql query result using access 2007 vba.
What I have is a text box named AGNwhen a user put value on it check for this value then it bring back MsgBoxif the the value is already inserted. What I try to do is :
我正在尝试使用 access 2007 vba 计算 sql 查询结果中的行数。我拥有的是一个文本框，AGN当用户在其上放置值时，它会检查此值，然后MsgBox在该值已插入时返回。我尝试做的是：
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT agencies.[agency no] FROM agencies WHERE agencies.[agency no]= " &Me.AGN.Text
Set rs = db.OpenRecordset(strSQL)
If rs.Fields.Count > 1 Then
MsgBox "this value is already here "
End If
Set rs = Nothing
When I insert any value on the textbox I got run time error 3061 (too few parameters)
当我在文本框中插入任何值时，出现运行时错误 3061（参数太少）
回答by HansUp
The "too few parameters"error message generally means there is something in your SQL statement which Access doesn't recognize as a field, table, function or SQL keyword. In this case, it could happen if [agency no]is text rather than numeric data type. If that is the case, enclose the value of AGNwith quotes when you build the SQL statement. (Or you could use a parameter query to avoid the need to quote the text value.)
该“太少参数”错误信息通常意味着有东西在你的SQL语句这Access不能识别为一个字段，表，函数或SQL关键字。在这种情况下，如果[agency no]是文本而不是数字数据类型，则可能会发生这种情况。如果是这种情况，请AGN在构建 SQL 语句时用引号将 的值括起来。（或者您可以使用参数查询来避免引用文本值的需要。）
strSQL = "SELECT a.[agency no] FROM agencies AS a" & vbCrLf & _
"WHERE a.[agency no]= '" & Me.AGN.Value & "'"
Debug.Print strSQL
In case of trouble, go to the Immediate window and copy the output from Debug.Print. Then you can create a new query in the Access query designer, switch to SQL View and paste in the statement text for testing.
如果出现问题，请转到“立即”窗口并从Debug.Print. 然后您可以在 Access 查询设计器中创建一个新查询，切换到 SQL 视图并粘贴语句文本进行测试。
Once your SELECTis working, you can check whether or not the recordset is empty. When it is empty both its BOFand EOFproperties are true. So to detect when it is notempty, check for Not (BOF And EOF)...
开始SELECT工作后，您可以检查记录集是否为空。当它为空时，它的BOF和EOF属性都为真。因此，要检测它何时不为空，请检查Not (BOF And EOF)...
With rs
If Not (.BOF And .EOF) Then
MsgBox "this value is already here "
End If
End With
However you don't actually need to open a recordset to determine whether a matching row exists. You can check the value returned by a DCountexpression.
但是，您实际上并不需要打开记录集来确定是否存在匹配的行。您可以检查DCount表达式返回的值。
Dim lngRows As Long
lngRows = DCount("*", "agencies", "[agency no]='" & Me.AGN.Value & "'")
If lngRows > 0 Then
MsgBox "this value is already here "
End If
Notes:
笔记：
- I used
AGN.Valueinstead ofAGN.Textbecause the.Textproperty is only accessible when the control has focus. But I don't know where you're using that checking code, so unsure which is the proper choice for you. - Notice the similarities between the
SELECTquery and theDCountoptions. It's often easy to translate between the two.
- 我使用
AGN.Value而不是AGN.Text因为该.Text属性仅在控件具有焦点时才可访问。但是我不知道您在哪里使用该检查代码，因此不确定哪个是您的正确选择。 - 请注意
SELECT查询和DCount选项之间的相似之处。在两者之间翻译通常很容易。
