MS Access – VBA – Requery a Form While Remaining on the Same Record

Have you ever wanted to requery a form after a user inserts a new record or modifies an existing record, to perhaps re-order things, but wanted to stay on the same record that you currently are on once the requery was done? Perhaps to synchronize changes made by other users, or on another form.

My Initial Idea

The fact of the matter is that it truly isn’t very complex to do.  Below is some straight forward code to do so and you’d need only add it to a Form’s After Insert event or a control’s After Update event.

    Dim rs              As DAO.Recordset
    Dim pk              As Long

    pk = Me.PrimaryKeyFieldName
    Me.Requery
    Set rs = Me.RecordsetClone
    rs.FindFirst "[PrimaryKeyFieldName]=" & pk
    Me.Bookmark = rs.Bookmark
    Set rs = Nothing

Now there is nothing wrong with the code above, but instead of putting such code inside each and every form’s After Insert event and every control’s After Update event, I thought to myself that I should be able to create a simple, re-useable function that I could call, and achieve the same desired effect. Below is that function.

'---------------------------------------------------------------------------------------
' Procedure : FrmRequery
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Requery the form to apply the chosen ordering,
'               but ensure we remain on the current record after the requery
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' frm       : The form to requery
' sPkField  : The primary key field of that form
'
' Usage:
' ~~~~~~
' Call FrmRequery(Me, "Id")
' Call FrmRequery(Me, "ContactId")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Oct-19                 Initial Release
'---------------------------------------------------------------------------------------
Sub FrmRequery(frm As Form, sPkField As String)
    On Error GoTo Error_Handler
    Dim rs              As DAO.Recordset
    Dim pk              As Long

    pk = frm(sPkField)
    frm.Requery
    Set rs = frm.RecordsetClone
    rs.FindFirst "[" & sPkField & "]=" & pk
    frm.Bookmark = rs.Bookmark

Error_Handler_Exit:
    On Error Resume Next
    Set rs = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: FrmRequery" & vbCrLf & _
            "Error Description: " & Err.Description, _
            vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

The beauty of the above function is that you can copy it into a standard module, and then call it with a single line of code in as many events as you choose. You could even build an event expression, thus not requiring any VBA events, if you so wished to.

A Much Simpler Solution!

Another option would be to simply requery the underlying form’s recordset directly, something like

Me.RecordSet.Requery

Or

Forms!YourFormName.Form.RecordSet.Requery

The beauty here is the screen data updates itself, but the form remains exact as is, the scrollbar doesn’t move, so it is completely transparent to the end-user.

14 responses on “MS Access – VBA – Requery a Form While Remaining on the Same Record”

  5. David

    Hi
    The line Set rs = frm.RecordsetClone throws an error 3420 (Object Invalid or no longer set) if you have previusly deleted a record of the form.

  13. Allen Stanbury

    Thanks Dan! I have used me.recordset.requery many times since you posted the idea. A few days ago, I applied it to a combobox. Here is the problem: the record source for the CBO is set in the Form_Open event. When the form is opens, the cbo is blank and set to look two weeks into the future and two week into the past. A click on the blank cbo drops the list of event dates down. Clicking a date displays data for that particular day. The narrow date range works fine 90% of the time. But occasionaly a user will want to search outside of the default range, perhaps two or three years in the past. Since the cbo is initially blank, the user can type in any date. The code below allows the cbo to display event dates plus or minus 28 days of the date typed in. Here is my code using your idea me.recordset.requery. What else can we do with me.recordset.requery in place of me.requery?

    Private Sub cbo_DefaultDate_NotInList(NewData As String, Response As Integer)
   Dim SQL As String
   Dim dDate1 As Date
   Dim dDate2 As Date
   Dim T As String    ' msgbox title
   Dim S As String    ' msgbox subject
   
   ' Check if the entered value is a valid date
   If Not IsDate(NewData) Then
      S = "You entered: " & NewData & vbNewLine
      S = S & "Please enter a date in one of these formats:" & vbNewLine
      S = S & "   2025-12-31   or " & vbNewLine
      S = S & "   Jan 31, 2025 or" & vbNewLine
      S = S & "   31 Dec 2025" & vbNewLine
      T = "Invalid Date Format..."
      MsgBox S, vbExclamation, T
      Me.cbo_DefaultDate.Text = ""
      Response = acDataErrContinue
      Exit Sub
   End If
   
   dDate1 = CDate(NewData) - 28  ' Display a range of dates in pick list
   dDate2 = CDate(NewData) + 28
   
   ' Dyanamically change the Row Source property of cbo_DefaultDate
   SQL = "SELECT tb1_Events.Event_ID, tb1_Events.fld_EventDate " & vbCrLf & _
         "FROM tb1_Events " & vbCrLf & _
         "WHERE tb1_Events.fld_EventDate Between " & dt(dDate1) & " And " & dt(dDate2) & " " & vbCrLf & _
         "ORDER BY tb1_Events.fld_EventDate DESC;"
         
   If SQL_Ok(SQL) Then
      '  Update cbo recordset
      Set Me.cbo_DefaultDate.Recordset = oCurrentDb.OpenRecordset(SQL, dbOpenDynaset) 
      Me.cbo_DefaultDate.Recordset.Requery                                         ' Display new range of dates
      Me.cbo_DefaultDate.Dropdown                                                     ' Show the new list
      Response = acDataErrContinue
      ' Me.cbo_DefaultDate.Requery triggers error 2118
      ' The reason "Me.cbo_DefaultDate.Recordset.Requery" works is that requerying the Recordset
      ' does not force an immediate UI update as "Me.cbo_DefaultDate.Requery" does,
      ' avoiding the field-saving issue.
   Else
     T = "Invalid Date..."
     S = "You entered: " & NewData & vbNewLine
     S = S & "We found no curling events on days near " & NewData & "." & vbNewLine
     S = S & "Sorry, we have no scores between " & dDate1 & " and " & dDate2 & "." & vbNewLine
     MsgBox S, vbExclamation, T
     Me.cbo_DefaultDate.Text = ""
     Response = acDataErrContinue
  End If
End Sub 

'--- Helper function
Private Function SQL_Ok(SQL As String) As Boolean
   Dim rs As DAO.Recordset
   Set rs = oCurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
   If rs.BOF And rs.EOF Then
      SQL_Ok = False
   Else
      SQL_Ok = True
   End If
   rs.Close
   Set rs = Nothing
End Function