Requery Method 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