Share via


Recordset.Requery Method (DAO)

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