Solved No Current Record on Access Close

[CompSMART]

I have a unbound form that functions as the Main Menu for my application. It has one tab control with a few tabs (mostly list boxes displaying data) - nothing fancy going on - very simple. I added a new tab to the tab control and placed a subform on it. The subform has a query as its record source and the form itself is very simple with 9 fields of data displayed in continuous form view. I have both Generate and Delete command buttons located directly above the subform (on the subforms tab on the tab control on the parent form). The Generate command button runs an append query that adds records to the local table the subform record source query is based on and then refreshes the subform (works fine). The Delete command button runs a delete query (to delete ALL records) in the underlying table that the subform record source query is based on and and then refreshes the subform (works but 1 row of fields remain displayed on the subform with #name? listed for all fields).

The problem... after clicking the Delete command button and then closing Access - a "No Current Record" error is triggered.

The solution... I changed the record source of the subform FROM the underlying query TO a "select" statement using a where condition that specifies a criteria that I know will never exist in the underlying table. When a user clicks on the Generate command button, I run the same append query as before and then change the record source of the subform to the (original) query and refresh the subform (works fine). When the user clicks on the Delete command button, I change the record source of the subform back to the fake/phantom "select" statement and requery the subform and THEN run the delete query on the underlying data (works fine).

The solution outlined above has solved the "No Current Record" issue when exiting Access - but I would really like to know WHY it solved the issue (or more importantly, WHY I experienced the issue in the first place).

I'm eager to hear some of your thoughts! Thanks all!

 

[The_Doc_Man]

When a form or sub-form is closing, what is ALSO closing is a child task (a.k.a. child process). Every form, sub-form, report, or sub-report - in order to have CPU time - must be part of a task/process. The task in which MSACCESS.EXE is opened is PROBABLY the parent process unless you opened Access via File Explorer - in which case THAT might be the parent. (No easy way to tell.)

When any process closes, all files it has open are also closed. This closure is part of something called "process rundown" and it is necessary to happen in order for Windows to be able to reclaim memory from the closing process. Closure occurs one file connection at a time if the code in the form or sub-form had a file open that wasn't the .RecordSource of the form. When you open a query or recordset, it counts as a file channel. When the file channels close, if there was something that would be aware of any recordset opened earlier and not yet closed, then you would see a "No Current Record" - particularly if there were more files left to be closed after the thing that triggered the message.

 

[arnelgp]

can you show us what are the codes in your "delete" button.
are there any code you are executing when the db closes?

 

[CompSMART]

When a form or sub-form is closing, what is ALSO closing is a child task (a.k.a. child process). Every form, sub-form, report, or sub-report - in order to have CPU time - must be part of a task/process. The task in which MSACCESS.EXE is opened is PROBABLY the parent process unless you opened Access via File Explorer - in which case THAT might be the parent. (No easy way to tell.)

When any process closes, all files it has open are also closed. This closure is part of something called "process rundown" and it is necessary to happen in order for Windows to be able to reclaim memory from the closing process. Closure occurs one file connection at a time if the code in the form or sub-form had a file open that wasn't the .RecordSource of the form. When you open a query or recordset, it counts as a file channel. When the file channels close, if there was something that would be aware of any recordset opened earlier and not yet closed, then you would see a "No Current Record" - particularly if there were more files left to be closed after the thing that triggered the message.

Thank you Doc! While your understanding of the exact processes that take place is deeper than mine, I think I follow your explanation. But I'm still struggling to find out (on a more tangible level ie: what did I do in my FE design to cause it) why after developing apps in Access since 1995 (it somewhat depresses me to even admit that - I'm really getting old) that this is the first time I've bumped into this issue.

 

[Mike Krailo]

When you open a query or recordset, it counts as a file channel. When the file channels close, if there was something that would be aware of any recordset opened earlier and not yet closed, then you would see a "No Current Record" - particularly if there were more files left to be closed after the thing that triggered the message.

Do you have a source for this statement anywhere that I can read about? Link? And I'm not talking about the OS's process rundown as I know that, I'm talking about the association to the "No Current Record" message in Access. This doesn't make any sense to me to relate open files to the "No Current Record" message. I have always found that Access is very good about it's error messages and they are almost always pointing to the actual problem at hand. In this case, no current record could actually mean just that. A programmer cannot assume anything and must handle everything that isn't being handled or errors will occur. There is really no way for us to know without looking at the actual database and code being used. That has not been provided.

 

[CompSMART]

Arnelgp / Doc - thank you both for your taking time to reply.

As I was getting ready to reply to arnelgp, I started gutting a copy of my app to simplify it enough to post a somewhat redacted copy here. After simplifying the code behind that form in question - there it was - looking at me straight in the face - and I realized what I had done to cause the error.

After the "Generate" AND "Delete" command buttons are clicked, the last line of code that runs behind each refreshes the caption for a label above the subform that displays a record count to the user. Because I generally avoid using domain aggregate functions unless absolutely necessary, I obtained the number of records in the subform by using Me.frmSubFormName.Form.Recordset.Recordcount. Changing that to simply using DCount on the subforms underlying query to obtain the record count solved the issue.

 

[Mike Krailo]

Arnelgp / Doc - thank you both for your taking time to reply.

As I was getting ready to reply to arnelgp, I started gutting a copy of my app to simplify it enough to post a somewhat redacted copy here. After simplifying the code behind that form in question - there it was - looking at me straight in the face - and I realized what I had done to cause the error.

After the "Generate" AND "Delete" command buttons are clicked, the last line of code that runs behind each refreshes the caption for a label above the subform that displays a record count to the user. Because I generally avoid using domain aggregate functions unless absolutely necessary, I obtained the number of records in the subform by using Me.frmSubFormName.Form.Recordset.Recordcount. Changing that to simply using DCount on the subforms underlying query to obtain the record count solved the issue.

What if you put an On Error Resume Next in before that command. Glad you went further to troubleshoot what was going on.

 

[CompSMART]

What if you put an On Error Resume Next in before that command. Glad you went further to troubleshoot what was going on.

Yes, I had tried that earlier - it had no effect.

 

[Mike Krailo]

There are quite a few threads on "No Current Record" so it's one of those errors that could drive you mad. I remember having one that was not directly generated by the form code itself and the only way to trap the error was using special code in the On Error event of the Form. That was eye opening to me at the time. There are also many threads warning about the use of that very command you used "Form.Recordset.Recordcount" and that it could potentially generate a "No Current Record" situation. RecordCount is part of the recordset objects. The classic is not checking BOF or EOF in the code, but you probably already know about that one. There is also possible corruption issue in which case, compact/repair or importing all objects into a new database is in order. Glad you got it sorted.

 

[CompSMART]

There are quite a few threads on "No Current Record" so it's one of those errors that could drive you mad. I remember having one that was not directly generated by the form code itself and the only way to trap the error was using special code in the On Error event of the Form. That was eye opening to me at the time. There are also many threads warning about the use of that very command you used "Form.Recordset.Recordcount" and that it could potentially generate a "No Current Record" situation. RecordCount is part of the recordset objects. The classic is not checking BOF or EOF in the code, but you probably already know about that one. There is also possible corruption issue in which case, compact/repair or importing all objects into a new database is in order. Glad you got it sorted.

The FE I'm working on is newly created as part of a migration of this application to a SQL backend and I have been the only user in it so far, so sort of ruled out corruption. I am reviewing any use of the Form.Recordset.Recordcount in the rest of the application as well as a precaution. Lesson learned!

 

[The_Doc_Man]

Do you have a source for this statement anywhere that I can read about? Link?

At the moment, no. However, it would be simple enough to test - by checking whether Me.subformcontrol.Form.Recordset is still available at the time this error occurs. It will either be NOTHING (if the recordset is now closed) or it will have a value. It is also possible that I went a little TOO deep in O/S theory of operation. The form_Close event is sort of a "last chance" thing that occurs before the form actually closes - at which time you don't get another event for it. The file handle closures would occur AFTER you get the _Close event. At that time, the form has already done the form_UNLOAD event, so you no longer have any controls. It may simply be because the sub-form control is now "floating free" and the sub-form inside it can't link to the PARENT record through the parent/child link in the sub-form. THAT could be the "no current record" situation as well. Since Access is not OpenSource, knowing the specifics of events can be tricky.

Other possible causes (after further research) would be if you were on a record and it got deleted, OR if you were on a record in the parent, the form has parent/child links, and the parent record got deleted. (Which is consistent with my 1st paragraph, really.)

A post from 2009 seems to relate to this problem. Our member missinglinq says this happens during a form Close operation.

no current record on close

Hi, I have a very large and complicated form with several sub forms which does what I want mostly. If I close it with a button I have put on there which runs the docmd.close action it's fine. However if I close the form using the cross top right I get the message "no current record". Once you...

www.access-programmers.co.uk

Further searches suggest another possibility, again related to the main/sub form having a parent/child link in the subform control. Apparently, during the main form's _Close event, there is a repositioning (perhaps a .Requery?) such that the parent form does an implied .MoveFirst, which would cause the child form - if still active - to try to move somewhere as well.

Finally, because of that link between parent and child, consider what happens during mainform_Close. In the linked article, it clearly says that the main form closes FIRST after which the sub-form shuts down.

Order of events for database objects - Microsoft Support

support.microsoft.com

Everything points to the idea that the sub-form persists for a (very) brief time after the parent closes, and there theoretically can be a situation in the sub-form that can't be resolved without the parent being there.

 

[Pat Hartman]

Since the subforms load before the main form, they unload in the reverse order so the main form unloads and then the subform,

The sample database I uploaded in this thread will help you to understand form and control events.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

You should watch at least one of the videos before trying to use the sample database so you understand what you are looking at.