Search code examples
vbams-accessrecordset

How to fix "no current record"-Error from Form.Recordset?


In my ms-access vba project I am using me.Recordset in a sub form to pass the current recordset to a function (where I read the fields).

This works the first time but after a certain point I get the error:

3021 - no current record ("Kein aktueller Datensatz")

This is strange because I can see the record, all the fields are accessable with the !-operator (like Me!EM_KEY_PE) and Me.Recordset.RecordCount is 1. me.Recordset.EOF is false.

Debug.Print Me.Recordset!EM_KEY_PE

also raises the same error while Me!EM_KEY_PE works.

The recordset gets invalid after doing things like making the parent form invisible and visible again, saving the dataset and setting a new position for the parent form.

So how to fix this?

Things I tried so far:

  • Use me.recordsetClone instead of me.Recordset => no effect
  • not calling the function => Debug.Print Me.Recordset!EM_KEY_PE still raises an error

Solution

  • It seems like this line:

    Me.RecordSource = Me.RecordSource
    

    restores the recordset and fixes my problem. I found this in the microsoft documentation for Form.Recordset https://learn.microsoft.com/en-us/office/vba/api/Access.Form.Recordset

    But as this line changes the position of the selection I created this function to repair my Recordset ("LFD" is my primary key):

    Public Sub fn_repairFormRecordset(ByVal par_form As Form)
      
      Dim rs As DAO.Recordset
      Dim currentLfd As Long
      
      On Error GoTo fn_repairFormRecordset_error
      
      currentLfd = par_form("LFD")
    
      par_form.RecordSource = par_form.RecordSource
    
      Set rs = par_form.RecordsetClone
      rs.FindFirst "[LFD]=" & currentLfd
      par_form.Bookmark = rs.Bookmark
      
    fn_repairFormRecordset_exit:
      Exit Sub
      
    fn_repairFormRecordset_error:
      Call msg_error_norm(ModuleName, "fn_repairFormRecordset")
      GoTo fn_repairFormRecordset_exit
      
    End Sub
    

    Parts of this code I got from here: https://www.devhut.net/2012/10/19/ms-access-vba-requery-a-form-while-remaining-on-the-same-record/

    But I still did not foind out what exactly causes the Recordset to become invalid.