Okay, could be several things going on here.
Firstly, a crosstab query gets its column headings from one of the
fields.
That means Access has to run the query before it knows what the fields
are.
And since it needs to know what the fields are for you to design the
report,
it will ask you for the parameters even in design view. You can solve
that
issue by listing the field names in the Column Headings property of the
report. The column names are now static, and so it does not have to run
the
query to discover what they are.
With a bit of luck, you might find that setting the Column Headings
solves
the crashes too. One if the things that can cause Access to crash is when
fields just disappear (as they can when the Column Headings are not
declared.)
You have declared the 2 parameters as Date/Time type fields. If the text
boxes on frmDateMenu2 are unbound, set their Format property to Short
Date
so Access understands their data type on the form too, and won't
misinterpret them or allow bad dates.
Any other complexity that could be contributing to this crash? For
example,
are there any subqueries in the crosstab? Or are there subqueries in
lower
level queries that the crosstab is based on?
Are there any yes/no fields that you are grouping on? Particularly if
these
are from the outer side of a join, they can crash Access.
How complex is the sorting'n'grouping within the report? The report
itself
does further querying of the crosstab results based on what you enter
into
the Sorting And Grouping dialog, so it is possible that the crashes
happen
at this level.
Below are some generic steps for solving crashes and their associated
corruptions. They are not specific to the issue you describe, but may be
useful anyway.
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/Repair
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 (holding down the Shift key if you have any startup code),
and compact again.
5. 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
6. 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,
and the code syntax is compilable.
Hello. I have a report (based upon a crosstab query) that when opened
causes
my db to crash about 50% of the time. In an effort to pinpoint the
problem,
I have pulled up the crosstab query several times and it works fine.
This
leads me to believe that the problem thus lies on the report side.
A little background on the ct query: There is a criteria column Where:
Between [Forms]![frmDateMenu2]![StartDate]
And [Forms]![frmDateMenu2]![EndDate] in which both of these dates have
been
declared.
One thing that does seem odd is that when I pull up the report in
design
mode, I get pop up menus asking for Parameter Values both StartDate and
EndDate. Does anyone know of some undocumented reason for this?
