customizing 'control sources' with code in Access 2007

[Guest]

I have a setup where the groups, sorting, and fields have their data sources
and visibility set in the report's On Open event based on choices made in a
form. This works fine in 2000 and 2003 but in it causes Access 2007 to crash
with an "Access has encountered a problem and needs to close." message--after
several pages.

I suspect this is related to http://support.microsoft.com/kb/927536/en-us
(Access 2007 may close unexpectedly when you try to change the Control
Source property for a control that is on a report or on a form in Layout view)

I'm not in Layout view, but rather in the On Open stage of Normal (print) or
Preview modes, so that doesn't precisely apply, but it is the closest I can
find.

Going in and specifying all the control sources by hand rather than letting
the code do it does indeed eliminate the error. But it seems awfully odd
that changes to the 'On Open' code would affect something that causes crashes
well into the printing of the report.

The point at which the error happens varies with which fields are selected,
but if the form has the same settings than the crash happens at the same
point in the report every time.

Isn't dynamically setting control sources pretty normal? I'm thinking about
trying to do something with copying the report, opening the copy in design
mode, saving the changes, then opening for preview--all via code. But just
reverting to 2003 seems easier---but annoying.

If there is a known issue I'm missing, or some alternative method that would
get around what is really looking like an Access bug here, I'd appretiate any
insights. Thanks.

 

[Guest]

"Isn't dynamically setting control sources pretty normal?" No, not IMHO.
There may be a decent alternative but we have no way of making suggestions
since we don't know why you need to do this.

 

[Guest]

"Isn't dynamically setting control sources pretty normal?" No, not IMHO.

There may be a decent alternative but we have no way of making suggestions
since we don't know why you need to do this.
--

The goal is that users can easily customize a standard analytic
report--applying a wide variety of criteria, choose 5 fields to include, and
optionally choosing up to three other fields to group and summarize on. It
is a nice plus that they don't need to know Access to do this. But even if
they could make the report themselves, it is a huge convenience to have a
standard report with all the formating done that can be easily configured
however desired.

It has worked fine since Access 97, and it almost works now except for
causing a total Access crash after some pages have been processed. The more
I think about it the more convinced I am that this is some kind of bug where
a buffer is filling up or something is recursing improperly or something like
that. Even if I could find a nice work around (and everything I can think of
is a LOT more work that shouldn't be necessary) I'm realizing that the next
bug could be even uglier. Looks like time to go back to 2003 for a bit and
let the 'public beta' of 2007 progress for a bit.

 

[Guest]

You might consider change the SQL property of a saved query that is used as
the record source of the report. This would allow you to "alias" field names
with generic names like "ColumnA", "ColumnB",...

It wouldn't be exactly the same but might work.

 

[Guest]

At first I was thinking that didn't address the report grouping---but with
the aliasing I guess I could group on 'ColumnA' and then have the report
labels use Form fields as data sources to get proper labels. So yeah that
might work.

That's a lot of kludginess to go through though, so I'll probably still
revert to 2003 for the time being.

Still, thanks for the notion.

 

[Guest]

I have since heard of other reliable sources that there is an issue with
dynamically setting the control sources of text boxes in reports during the
On Open event. I believe MS has been notified.