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