Access crashes when I remove filter on a sub form

M

mikejs2000

Hi,
I have a form that contains a tabbed control, on this control is a number of
sub forms amoung other things. On one of the tabs I have two sub forms, one
shows the result of a query to find maiching records from an import, and the
other shows th main table, where the results will end up. The way I have
worked the system is for the user to filter the main table data on various
fields in order to see if he/she can find a match, if they do then they are
to delete the record from the import table. The problem occurs when I go to
remove the said filter I just get a big window stating that Access has found
a problem and needs to be restarted. Can anyone come up with any ideas why
this is happening. The need for the tabs is to guide the user through a set
of 6 steps in order to import a text file and there are a number of
selections that are needed to be made to produce the final query that imports
the data into the main table.

Mike J. Soames

Thanks
 
A

Allen Browne

Whew, hard to know where to start with something like that.

What you are trying to do sounds sensible enough (wizard type interface,
with sequential steps), but there are so many potential factors here. Here's
a few to get started.

1. Eliminate Name AutoCorrect, by unchecking the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"
Open Access (holding down the Shift key if you have any startup code), and
compact again.

3. If this is Access 2002 or 2003, is there anything in the
LinkMasterFields/LinkChildFields properties of any of your subforms? If so,
are all the fields named in LinkChildFields represented by a text box in the
subform? If not (and particularly if the subform's source is a query), clear
the LinkMasterFields/LinkChildFields, add a (hidden) text box to the subform
for the field. Save. Close. Then open the form again and restore the
LinkMasterFields/LinkChildFields.

There seems to be a problem with objects of type AccessObject (the type when
you refer directly to a field). The steps above work around that problem by
giving it an object of type Textbox to link on instead.

4. Test. Hopefully it's resolved by this stage. If not, the next step will
be to start examining the query statements to see if any of them are
triggering the crash and the point where you un-filter. There's a myriad of
bugs in the JET query engine that could cause this. A very common one is any
criteria or grouping on yes/no fields from the outer side of a join or
subquery.

If you're still stuck double-check you have the latest service pack for your
version of Access, and at least SP8 for JET 4:
http://support.microsoft.com/kb/239114
and indicate what version you are using.
 
M

mikejs2000

Firstly thanks for your help Allen
One interesting fact I missed was that initial entry, and that is that every
thing works fine is I copy and past the form and remove a number of other
object from it. To me this means that the problem is somewhere else on the
form, another control or something.
I think I have found a solution where I have access open another working
form transparently and the user does the filter on this, then when he/she
clicks continue the system goes back to the original form, to stop the user
running a filter on this form the offending sub forms are locked. Ok not
ideal but I think a usab le workround.

Mike J. Soames
 
Top