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

0
votes

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
2
vbams-accessrecordset
Please edit your post to include the code (both the calling procedure and the procedure that is being called), and also explain exactly what you are trying to do by passing the recordset. It is quite likely that there is no need to pass the recordset. - Applecore
@Applecore: As described in the question the the recordset get invalid even if the dont call the function (So the function is not the reason the error is thrown). I want to pass the recordset to access the fields from a central function. Our vba system is quite big. So we have +100 different apps which are using the same framework-modules. While the form is different in every app the function is in a framework module + All of this has historic reasons which it meakes no sense to explain it here. - Gener4tor
Without your code, no one can tell what is going on. - Gustav

2 Answers

1
votes

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.

0
votes

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://docs.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.