Error when closing database if multiple forms are open

[Seth Schrock]

I'm using Access Runtime 2010 for my users to use my database. frmHome is the name of the form that opens automatically. If I have frmLoans and frmHome open and click the X to close the program, I get an error from frmLoans' OnLoad event saying that "You entered an expression that has an invalid reference to the property Form/Report." (error number 2455). Why is any form's OnLoad event running when I'm closing the database? Should I just trap for error 2455 and resume next for just that error? If I close frmLoans (leaving just frmHome open) and then close the database, then I don't get the error. I also don't get the error with frmLoans open on my laptop that has the full Access 2010 on it.

[TheSmileyCoder]

This sounds very weird and I admit to not having to much experience with using just runtime.

However when something sounds very weird, as is the case here, the first thing to try is to compact&Repair, Decompile, and then recompile.


Now if that doesn't work we would have to look into any events that might run when the form is closed & unloaded, and perhaps it might be relevant to know if you use instanced forms?

[Seth Schrock]

Neither form has any events related to their closing (OnClose, OnUnload, etc.) I did the decompile, recompile, compact and repair, same error. Not sure what an instanced form is, so chances are I'm not using them.

[TheSmileyCoder]

If you are using code like:

Dim oFrm as form
Set oFrm=New Form_Client

Then you are using instanced forms. If you are only opening forms with "Docmd.Open ..." then you shouldn't have a problem. Instanced forms is used to open the same form multiple times.

Does your forms recordsource, or any objects on your form reference any fields or controls on other(or same) form?
(For example cascading comboboxes)

[Seth Schrock]

Then I'm not using instanced forms. I do open frmLoans by selecting a record on frmHome, but then frmLoans doesn't reference anything from frmHome. One thing that I did notice is that when I get the error, it has already closed frmHome, but frmLoans is still open. Not sure if this means anything.

[TheSmileyCoder]

When frmLoans is opened from frmHome how does it know which record to show?

[Seth Schrock]

There is a subform on frmLoans that shows a few fields of the query that frmLoans is based on. I double click on one of those records and use the following code.

DoCmd.OpenForm "frmLoans"
DoCmd.SearchForRecord "LoanID = " & Me!LoanID

(Me being the subform).

[TheSmileyCoder]

I must admit to being out of ideas. Can you recreate it on your own pc with full access installed using simulated runtime?

[Seth Schrock]

If by "simulated runtime" you mean actually using the database as the normal users would, then no I can't recreate the error. No error occurs on my PC while using the full Access.

What could go wrong if I just put the following in frmLoans' OnLoad event error handler?

If err.Number = 2455 Then Resume Next

[TheSmileyCoder]

By simulated runtime I mean that the full version of access has the functionality to simulate runtime. If you open access by going to Windows Start and selecting Run and then typing in:

"C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE" "C:\Test\MyAccessProject.mdb" /runtime

You should be able to simulate the runtime environment as it will look on the users pc. Of course replace the Access executable path and project path as appropriate. I have only used this once myself, so I am not all that familiar with it.


I don't know about ignoring the error. My gut instinct is that the error message you are getting is not related to the "true" cause of the issue. From a testing perspective it might of course be interesting.

If you want, and can, make a sample db and I can try to take a look at it.

[Seth Schrock]

It does happen when I simulate runtime on my laptop.

I was able to narrow down what the code was that was causing the error in frmLoans' OnLoad event.

With Me.sfrmEarlyDisclosures.Form
    .AllowAdditions = (TempVars("UserType") <> etLoanOfficer)
    .AllowDeletions = (TempVars("UserType") <> etLoanOfficer)
    .AllowEdits = (TempVars("UserType") <> etLoanOfficer)
End With

It is one of these lines, but I would assume that it is the first as all the others should work if the first one does. However, this code runs fine when I open the form. Do subforms get closed before the main form? Why is the OnLoad event even running when the form closes? I had trouble before with this code (see I get error code 2467 when trying to reference a subform), but that was because I had forgotten to merge the table it was based on to the new backend (SQL Server). Once I did that, the problem went away.

[TheSmileyCoder]

Interesting. Its nice to hear that it also happens in simulated runtime. That should make it a tad easier for you to debug.

When I have to debug code where the normal debugger is not available to me (For me it might be when something is running in compiled mode, such as a MDE/ACCDE, but I guess runtime qualifies as well.) I usually rely heavily on the msgbox function.

I still feel it is very very odd that this is happening in the onload event, while closing.

So my suggestion would be to try to temporarily change the code to:

Msgbox "About to run Test of subform reference"
Msgbox "Subform name:" & Me.sfrmEarlyDisclosures.Form.Name

Msgbox "ABout to run test of TempVars collection"
Msgbox "UserType:" & TempVars("UserType")
Msgbox "etLoanOfficer:" & etLoanOfficer

Msgbox "About to run Test of change to AllowAdditions"
Me.sfrmEarlyDisclosures.Form.AllowAdditions = (TempVars("UserType") <> etLoanOfficer)
Msgbox "Test of change to allowadditions succesfull"

With this running you should atleast be able to narrow down where the error occurs. Another method would be to use something like MZ-tools to add line numbering (or do it manually) and then incorporate the line number as part of the error message.

Hope that helps.

[Seth Schrock]

That is how I narrowed it down to those lines of code. I group the code by setting an integer to incremental values and then pass the value to my custom error message. In this case intCodeGroup got set to 5 on the line prior to my code and gets set to 6 the line after. The number that got returned was 5. TempVars("UserT ype") was already used in previous code so I know that it works. etLoanOfficer is an enum that I created and it was also used previously successfully.

Anyway, I tried your message box idea to make sure.

intCodeGroup = 5
MsgBox "SubformName" & Me.sfrmEarlyDisclosures.Form.Name
intCodeGroup = 6

With Me.sfrmEarlyDisclosures.Form
    .AllowAdditions = (TempVars("UserType") <> etLoanOfficer)
    .AllowDeletions = (TempVars("UserType") <> etLoanOfficer)
    .AllowEdits = (TempVars("UserType") <> etLoanOfficer)
End With

intCodeGroup = 7

Opening the form produced the message box just fine and there were no errors. Leaving that form open when closing the database again caused the error with the code group being 5, so clearly it isn't finding the subform when the database is closing. Is there a way to find out what called the Form_Load event to know why it is running?

[Seth Schrock]

As this database is going for "live testing" on Monday, I have just tested for error number 2455 in my error handler and removed the With/End With portion so that the error number would be the same for all three lines. This works for now, so I'm good for Monday, but I would still like to know why this is happening and fix the "real" issue. Maybe I can create a sample for you on either Monday or Tuesday.

[TheSmileyCoder]

This is one of those cases that just sounds so weird, that I get really curious as to what is causing it. If you are using your "code" group number alot I would really recommend taking a look at MZ-tools. Its free for VBA, and it can add/remove line numbers for you by the click of a button. You can get line numbers in your error function by typing ERL.