vba 从更新查询提示中获取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/343396/
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
Getting a value from the update query prompt
提问by tksy
When we run a update query we get prompt saying that 'these many records are going to be updated. do you want to continue' is it possible to capture the value in the prompt message to a variable i.e the number of records going to be updated.
当我们运行一个更新查询时，我们得到提示说'这些许多记录将被更新。您想继续吗？是否可以将提示消息中的值捕获到一个变量中，即要更新的记录数。
回答by Fionnuala
If you run the query from code, you can use the records affected property:
如果从代码运行查询，则可以使用记录受影响的属性：
Dim db As Database
Set db=CurrentDB
db.Execute "Some SQL here"
db.RecordsAffected
If you use a transaction, you can rollback.
如果使用事务，则可以回滚。
回答by David-W-Fenton
Patrick Cuff proposed this function:
Patrick Cuff 提出了这个函数：
Function RowsChanged(updateQuery As String) As Long
Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs(updateQuery)
qry.Execute
RowsChanged = qry.RecordsAffected
End Function
I don't understand why one would go to the trouble of assigning a QueryDef variable to execute a query when it can be done directly CurrentDB.Execute without initializing (or cleaning up) any object variables.
我不明白为什么人们会费心分配 QueryDef 变量来执行查询，因为它可以直接完成 CurrentDB.Execute 而无需初始化（或清理）任何对象变量。
Obviously, a parameter query is going need to use the QueryDef approach, since you have to assign the values to the parameters before executing it. But without parameters, there's no reason to make it more complicated than necessary. With a generic function like this that isn't set up to handle parameter queries, it seems wrongly designed.
显然，参数查询将需要使用 QueryDef 方法，因为您必须在执行之前将值分配给参数。但是没有参数，没有理由让它变得比必要的更复杂。像这样的通用函数没有设置来处理参数查询，它似乎设计错误。
And, of course, it ought also to use dbFailOnError, so that you don't get unexpected results (dbFailOnError works with QueryDef.Execute, just as it does with CurrentDB.Execute). In that case, there really needs to be an error handler.
而且，当然，它还应该使用 dbFailOnError，这样您就不会得到意外结果（dbFailOnError 与 QueryDef.Execute 一起使用，就像它与 CurrentDB.Execute 一样）。在这种情况下，确实需要一个错误处理程序。
Rather than write an error handler every time you execute SQL, you can do this, instead. The following function returns the RecordsAffected and will recover properly from errors:
与其在每次执行 SQL 时都编写一个错误处理程序，不如这样做。以下函数返回 RecordsAffected 并将从错误中正确恢复：
Public Function SQLRun(strSQL As String) As Long
On Error GoTo errHandler
Static db As DAO.Database
If db Is Nothing Then
Set db = CurrentDB
End If
db.Execute strSQL, dbFailOnError
SQLRun = db.RecordsAffected
exitRoutine:
Exit Function
errHandler:
MsgBox Err.Number & ": " & Err.Description, vbExclamation, "Error in SQLRun()"
Resume exitRoutine
End Function
It can also be used to replace DoCmd.RunSQL (you just call it and ignore the return value). In fact, this function was entirely designed for use as a global replacement for DoCmd.RunSQL.
它也可以用来替换 DoCmd.RunSQL（你只需调用它并忽略返回值）。事实上，这个函数完全是为了作为 DoCmd.RunSQL 的全局替代品而设计的。
回答by Patrick Cuff
Yes, you can get the number of records updated via the RecordsAffectedproperty:
是的，您可以通过该RecordsAffected属性获取更新的记录数：
Function RowsChanged(updateQuery As String) As Long
Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs(updateQuery)
qry.Execute
RowsChanged = qry.RecordsAffected
End Function
You can call this function with the name of your update query to get the number of rows updated:
您可以使用更新查询的名称调用此函数以获取更新的行数：
Dim numRows as long
numRows = RowsChanged("UpdateQuery")
