Access Crashes when no data in form 1

[TroyMcClure]

I get the MSAccess.exe - Application Error
The instruction at 0Xblah,blah, blah. The memory could not be "read" (with quotes around the word read)

I get this when I have no data in a form (due to the form's query's criteria returning nothing). It happens at the point when I close the form--ie, I could work at any time during the day, and it resulted in nothing returned, then even when later I refiltered and got records, when I finally close the form I get the error and the entire Acceess application is killed.

Anyone seen this, know of a KB on this, etc?
--Troy

 

[RickSpr]

Are you saying this happens even if there are records on the form at close time, but at some point all the records were filtered out?

Do you have code in the Close event procedure?

As I'm sure you've learned, Access doesn't display any controls if the form's recordset is empty. In fact, I think it doesn't even load the controls, meaning any reference to a control in this situation will cause an error. Perhaps you have such a reference in another event procedure, and are trapping the error and ignoring it, but that sets up the eventual failure at Close time.

A better way would be to avoid executing the code that gets the error (and sets up the eventual failure). You might try testing for an empty recordset (Me.Recordset.RecordCount = 0) wherever you try to reference a control.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[TroyMcClure]

<< Are you saying this happens even if there are records on the form at close time, but at some point all the records were filtered out? >>

Yes--I could work all day with no problems, but if at any point during the day a filter resulted in an empty form, the error happens when I close. No code is in close event--it will also happen if I go to design view and then just 'x' out of the form there.

It's definitely something to do with the form controls not showing--the form is blank in the 'detail' section, but I have a 'reset' button in the header for this very reason--so that an empty search will still allow the user to reset and show all records.

I do make references to controls in numerous places in code--and the problem probably does stem from some reference to a control--but always in the past even if I reference a totally nonexistent control, say via misnaming, then sure, I get a trappable runtime error, or even the "the expression has no value" error--but it dosn't sink the entire access.exe application. This is a much more serious, low-level error.

Now, if I set the form to 'Dyanaset', and the form's query to dynaset, and all permissions on tables, etc, etc...AND do AllowAdditions, then after all that--then on an empty recordset I get form controls showing up (for the optional new record), but empty of course, and the error doesn't happen.

I can deal with that--However, it's just that I had locked down all permissions, etc, and had set up forms & queries in Snapshot mode for both performance and security, and I kind of liked it like that, and also I liked the totally blank form detail section--it left no doubt that the user's search didn't find anything.

So I have other ways of securing the form against additions, etc, so I can solve it by doing the Dynaset/dynaset/allowadditions thing, but I'd sure prefer to be able to keep my original design setup and find the bug.
--T

 

[mustangcoupe]

do you have any on error events to try and trap this problem??

 

[TroyMcClure]

I have found the bug.

The culprit is setting the .RowSource of a listbox (but not a combobox), when the form has no records. This is the case when, ie, it was filtered, you changed the .RecordSource, etc, and there is no 'blank' record waiting for data-entry as a new record. This is the case if either the form or it's query is a Snapshot, or if the AllowAdditions is false, or if table-security denies Insert permission, or for whatever reason you didn't put the full Primary Key on the form or it's query. This combination of criteria is not uncommon. We've all seen it--the "blank" form. Only stuff in the Header--if you chose to show Header/Footer--will show.

What makes this bug insidious is that no runtime error is generated when the listbox.RowSource is set to this seemingly non-existent object. Further, you can refer to this listbox and return it's properties, even though it's container--the Detail Section, and presumabley the object itself, does not exist.

This can be easily reproduced: Create a form, base it on some query based on any dummy test table,
Put a listbox in the form. Make it based on anything--for easier testing I made it's RowSource Type = "Value List", and just put the letter "x" as RowSource. In the Load event, do the following:

Private Sub Form_Load()
Me.Filter = "RecordID = ""@^@#^#""" 'Or any dummy critera that will return no recs.
Me.FilterOn = True

debug.print "List Source before: " & Me.List1.Rowsource
Me.List1.RowSource =  "Y" 'change to anything
debug.print "List Source After: " & Me.List1.Rowsource
End Sub

Now do Ctrl-G and look at the debug window contents. It sees the source correctly before & after.
Now close the form--you should get the illegal operation error, and the entire access.exe will crash.

The solution, if you need the Snapshot or otherwise not allow additions, is to check the RecordsetClone for records prior to setting the Listbox.RowSource. If it's .eof and .bof, then don't attempt to set the source, otherwise, you're fine.

I hope this can help someone avoid the hours of frustration I've spent on this...
--T

 

[JustLJ]

Rick -
your testing of the recordset for empty has saved the day!
Thanks for sharing!
Star for you!
LJ