Updates the data in a
Recordset object by re-executing the query on which the object is based.
Syntax
expression .Requery(NewQueryDef)
expression A variable that represents a Recordset object.
Parameters
Name
| Required/optional
| Data type
| Description
|
NewQueryDef
| Optional
| Variant
| Represents the Name property value of a QueryDef object |
Remarks
Use this method to make sure that a Recordset contains the most recent data. This method re-populates the current Recordset by using either the current query parameters or (in a Microsoft Access workspace) the new ones supplied by the newquerydef argument.
If you don't specify a newquerydef argument, the Recordset is re-populated based on the same query definition and parameters used to originally populate the Recordset. Any changes to the underlying data will be reflected during this re-population. If you didn't use a QueryDef to create the Recordset, the Recordset is re-created from scratch.
If you specify the original QueryDef in the newquerydef argument, then the Recordset is requeried using the parameters specified by the QueryDef. Any changes to the underlying data will be reflected during this re-population. To reflect any changes to the query parameter values in the Recordset, you must supply the newquerydef argument.
If you specify a different QueryDef than what was originally used to create the Recordset, the Recordset is re-created from scratch.
When you use Requery, the first record in the Recordset becomes the current record.
You can't use the
Requery method on dynaset- or snapshot-type
Recordset objects whose
Restartable property is set to
False. However, if you supply the optional newquerydef argument, the
Restartable property is ignored.
If both the
BOF and
EOF property settings of the
Recordset object are
True after you use the
Requery method, the query didn't return any records and the
Recordset contains no data.
Example
This example shows how the Requery method can be used to refresh a query after underlying data has been changed.
Sub RequeryX()
Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim rstView As Recordset
Dim rstChange As Recordset
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set qdfTemp = dbsNorthwind.CreateQueryDef("", _
"PARAMETERS ViewCountry Text; " & _
"SELECT FirstName, LastName, Country FROM " & _
"Employees WHERE Country = [ViewCountry] " & _
"ORDER BY LastName")
qdfTemp.Parameters!ViewCountry = "USA"
Debug.Print "Data after initial query, " & _
[ViewCountry] = USA"
Set rstView = qdfTemp.OpenRecordset
Do While Not rstView.EOF
Debug.Print " " & rstView!FirstName & " " & _
rstView!LastName & ", " & rstView!Country
rstView.MoveNext
Loop
' Change underlying data.
Set rstChange = dbsNorthwind.OpenRecordset("Employees")
rstChange.AddNew
rstChange!FirstName = "Nina"
rstChange!LastName = "Roberts"
rstChange!Country = "USA"
rstChange.Update
rstView.Requery
Debug.Print "Requery after changing underlying data"
Set rstView = qdfTemp.OpenRecordset
Do While Not rstView.EOF
Debug.Print " " & rstView!FirstName & " " & _
rstView!LastName & ", " & rstView!Country
rstView.MoveNext
Loop
' Restore original data because this is only a
' demonstration.
rstChange.Bookmark = rstChange.LastModified
rstChange.Delete
rstChange.Close
rstView.Close
dbsNorthwind.Close
End Sub
This example shows how the Requery method can be used to refresh a query after the query parameters have been changed.
Sub RequeryX2()
Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim prmCountry As Parameter
Dim rstView As Recordset
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set qdfTemp = dbsNorthwind.CreateQueryDef("", _
"PARAMETERS ViewCountry Text; " & _
"SELECT FirstName, LastName, Country FROM " & _
"Employees WHERE Country = [ViewCountry] " & _
"ORDER BY LastName")
Set prmCountry = qdfTemp.Parameters!ViewCountry
qdfTemp.Parameters!ViewCountry = "USA"
Debug.Print "Data after initial query, " & _
[ViewCountry] = USA"
Set rstView = qdfTemp.OpenRecordset
Do While Not rstView.EOF
Debug.Print " " & rstView!FirstName & " " & _
rstView!LastName & ", " & rstView!Country
rstView.MoveNext
Loop
' Change query parameter.
qdfTemp.Parameters!ViewCountry = "UK"
' QueryDef argument must be included so that the
' resulting Recordset reflects the change in the query
' parameter.
rstView.Requery qdfTemp
Debug.Print "Requery after changing parameter, " & _
"[ViewCountry] = UK"
Do While Not rstView.EOF
Debug.Print " " & rstView!FirstName & " " & _
rstView!LastName & ", " & rstView!Country
rstView.MoveNext
Loop
rstView.Close
dbsNorthwind.Close
End Sub