When using Filter by Form on a Subform Access Crashes

[Regulator]

I have a database which contains a form which tracks Orders and then keeps a
log in a subform of orders by that same customer. The log contains data such
as amount of order, date of order etc.

Here is my problem, when using the filter by form option on the subform if I
try to filter the date column by any criteria such as Between or >, after
hitting the funnel button ("Apply Filter"), the database crashes.

I can filter all other fields of the subform just not ones that contain
dates and times.

Other information that might be useful. When entering dates for filtering I
am using format #mm/dd/yyyy#. Also this database was built in Access 2000.
However, later our office updated to 2003. The production area still uses
2000 and opens the database in 2000; therefore, the database is run by two
different version. I have always, and continue to edit all forms in Access
2000 in order to avoid compatibility problems. The database crashes equally
in 2000 and 2003.

Any help would be appreciated.

 

[Allen Browne]

Try this:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. 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"

4. Open Access, and compact again.
You may find the problem is solved at this point.

5. If not, does this form have a subform?
And is the subform based on a query?
If the answer is Yes to both, open the subform in design view, and make sure
you have a text box for the field(s) named in the LinkChildFields property
of the subform control (typically the foreign key field.) Save. Close. Open
the main form, and delete the LinkMasterFields/LinkChildFields properties
from the subform control. Save. Close. Compact. Then open the main form
again, and restore the properties. (I have never seen this issue in Access
2000; only 2002 and 2003.)

6. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

7. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
the code syntax is compilable, and the bug with the AccessField type is
averted.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

BTW, if you have different users in this database at the same time, it would
be a good idea to split it. If that's a new concept, see:
Split your MDB file into data and application
at:
http://allenbrowne.com/ser-01.html

 

[Regulator]

I would agree with splitting it; however, the database is also secured. I
read how split the database, but am not sure how the secured database will
behave. Any good information on how to split secured Access Databases.

I will try your sugesstion tomorrow and let you know if it worked.

Thank you for answering the question.

 

[Allen Browne]

If you are logged into the secure database with administrative priviliges, I
would expect the built-in database splitter to work:
Tools | Database Utilities | Database Splitter