Form Filters causing Access Error and closes

[BillA]

I have built a database at work that is used to manage projects from start to
finish. I have a main form with 5 tabbed sub forms (named: Investigator;
Application; Renewal; Amendment and Status. Each tab's visibility is
controlled by a combo box that indicates the status of each project. On my
main form I also have a button that opens a form (from Carda Consultants)
that builds a hyperlink to an external document folder.

We experience an odd problem when entering data and moving from one tab to
another, or closing the Carda form, Access experiences an error and I get
“…Access has encountered a problem and needs to close.†Prior to closing, I
get a lengthy hourglass (about 3 seconds).

I’ve been able to identify VB code that when removed, the problem
disappears. The code is found in the Renewal and Amendment sub forms. The
code is:
Private Sub Form_Load()
Me.Filter = "[RenewORAmend] = 'Amend'"
Me.FilterOn = True
End Sub
Both sub form's record source is a single table named “tbl_RenewORAmend.â€
The code filters the field [RenewORAmend], in this case looking for ‘Amend’
and populates the Amendment sub form with amendment data for a particular
project. This code is also found in the Renewal form (swapping out ‘Renewal’
for ‘Amend’) and populates the Renewal sub form.

I now realize this code is causing the unexpected closure of the database.
I’m at a loss for why this is happening.

To add to my problem is that this database works flawlessly on my home
computer. Both computers are Running Access 2003 on XP w/SP3.

I would appreciate any advice or possibly an alternative to the code I am
using.

Thanks,
Bill

 

[Ken Snell]

My past experience with such types of errors has been that the error occurs
when ACCESS tries to connect to a table or resource that is "outside" the
database file (a backend database file on another server, for example, or an
image file on another directory, etc.).

You note that the tab's visibility is controlled by a combobox. Are these
subforms "invisible" when the main form first loads? If yes, ACCESS tries to
minimize data loading problems by not reading the Source Object's data until
the subform becomes visible. Perhaps you're getting a timing problem with
the subform trying to read its data while also doing the filter application?
Just a guess ...

To help us help you debug what might be going on, give us detailed steps
that lead to this problem, including when the subforms are
visible/invisible, whether the tab is visible/invisible, how you navigate
around the form, etc.