No Current Record on Access Close (1 Viewer)

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