CODEWITHSUNDEEP
vbams-accessrecordset
Gener4tor
Gener4tor

Reputation: 388

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:

Upvotes: 0

Views: 2041

Answers (2)

Gener4tor
Gener4tor

Reputation: 388

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.

Upvotes: 0

Erik A
Erik A

Reputation: 32642

You can navigate to the current form record, assuming a record is selected.

Dim rs As DAO.Recordset
Set rs = Me.Recordset
rs.Bookmark = Me.Bookmark

If you don't navigate to a specific record, any position can be valid. If this is EOF or BOF, you get a No current record error.

Upvotes: 1

Related Questions