"#Error" causes Access to stop working

[Trystyn]

Getting very frustrated that I have a database working fine on my home computer but crashing at work.

I have a Mainform, and within it, Subform1 and Subform2.

I have linked Subform1 and Subform2 by way of a textbox called Link, linked to a Field in Subform1.

I have used this method many times and never had a problem, but occasionally, Subform1 does not contain any records. If I reach this point, Link returns a value of #Error and at home, all that happens is that Subform1 and Subform2 are both appear blank, the desired result.

At work however, when I get to one of these empty subforms, I just get a message "Access has stopped working"

To make matters worse, I use exactly the same method on another form IN THE SAME DATABASE, and have no problems at home or at work.

What's going on?

Thanks as always

 

[The_Doc_Man]

First question: Same versions of Access at home and at work?

Second question: Split or non-split database?

Third question: Do you have any event code that would be activated in the OnCurrent of any of the three forms (main, sub1, sub2)? (And for the form that does the right thing in both places, does it have event code that matches the miscreant form?)

Fourth question: Does your work computer have any host-based security code that isn't running at home?

Fifth question: Are the relationships EXACTLY the same at home and at work for cases: (a) the form's underlying table/query and the two subs' underlying table/query? and (b) for the form that does work, look for differences in the relationships for the corresponding tables? (Or are they the same tables for both cases and the forms just do different things?)

 

[CJ_London]

In addition to Docs comments

try compact and repair
try decompiling
try deleting objects - compact and repair - recreate objects

 

[Trystyn]

First question: Same versions of Access at home and at work?

Second question: Split or non-split database?

Third question: Do you have any event code that would be activated in the OnCurrent of any of the three forms (main, sub1, sub2)? (And for the form that does the right thing in both places, does it have event code that matches the miscreant form?)

Fourth question: Does your work computer have any host-based security code that isn't running at home?

Fifth question: Are the relationships EXACTLY the same at home and at work for cases: (a) the form's underlying table/query and the two subs' underlying table/query? and (b) for the form that does work, look for differences in the relationships for the corresponding tables? (Or are they the same tables for both cases and the forms just do different things?)

Thanks for the prompt reply. To answer your questions:

1) It is a different version now, although back when the problem first occured, it wasn't. I had hoped that work updating their version of Access may fix the problem.

2) Non-split

3) Nothing in OnCurrent

4) Yes but why would this affect one part of a database and not the other?

5) The same forms and tables are involved. In the first, I see a complete list of data, this is the one where I get the crash. In the second where I have no problems, the Mainform is actually a subform in itself, linked to a main form by year.

 

[Trystyn]

In addition to Docs comments

try compact and repair
try decompiling
try deleting objects - compact and repair - recreate objects

Have tried the first 2 with no joy, hoping not to have to resort to the last if theres an obvious solution.

 

[missinglinq]

If one copy of the db works and the other doesn't, you really have to consider some form of corruption, don't you, in the misbehaving copy? The first line of defense, when corruption of a Form, or entire Database, is suspected, is to create a new, blank file, and import everything from the errant file into it! Only takes a minute or two and it's amazing how many times it works!

Linq ;0)>

 

[The_Doc_Man]

Trystyn, you asked "why would this affect one part of a database and not the other?"

Your answer to #2 was "Non-split" so your question is appropriate. However, if it had been a SPLIT database and the back end was on another system, then a host-based security system (particularly a "stateful" system) might have taken exception to something on the network wire that occurred for one form but not the other. Since it is NOT a split DB case, that "shot in the dark" totally missed the target. But when you first posted the question, we didn't have those details, which is why I asked that question.

I'm more interested in something else, though. Re-reading the original description and your supplement, it is clear that your behavior occurs when a JOIN returns an incomplete record because there is no child data corresponding to the main data on the form exhibiting this problem. However, in the other form, you have a filter to the records shown in the sub-form that corresponds to your failing main form. Are you using hand-coded SQL queries, wizard-built queries, or some other source? That link between forms implies a JOIN - but is it INNER or OUTER? Did you build the forms with a query wizard or hand build and hand link them?

For this discussion and considering your descriptions, you said that a form fails under certain cases. For the moment, call that Form B (because in the non-failing case it is called from another form that for this discussion should be called Form A). The sub-forms would be C and D, and either C or D is the one that ends up with no records.

When you activate form B directly and you hit a case where C has no records, ... Does that case ever show up when you activated form A and navigated form B to the same point? Or does that case never show up because of filters in form A?

To go along with that, do you have ANY code ANYWHERE that turns off warnings for queries? Because that would ALSO turn off warnings for IMPLIED queries such as having a failing query for a recordsource or rowsource. I'm wondering (perhaps just rambling out loud) if you are missing the error message that would have told you the real reason for this error?

I'm also going to jump out on another limb, shooting in the dark all the way...

When you open the work and home versions of the database, switch to Home >> Options, and bring up the Current Datatbase options page, compare all the settings. Are they the same?

 

[JHB]

I would create a new form and copy all from the problem form into it, it takes only a few minutes.