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.

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

  1. 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.