Changing controlsource in report (A2007)

Topic

New Reply

I just found out about a problem in Access2007, in which changing the controlsource of an object in an Access report when report is opened causes Access to crash and burn! Apparently a work-around is to open report in design mode and make changes. This is a major problem for me, on the order of being a deal-breaker.

There is an entry in the Knowledge Base from Dec. 1, 2006 in which the STATUS is “Microsoft has confirmed this is a problem”. Does anyone know if this means “We are working on a solution” or “Tough break for you”?

Reply | Quote

Replies

Unless you have a source inside Microsoft, there is no way of telling what will happen. The problem might be solved in a future service pack for Access 2007, or in the next version of Access, or never…

Reply | Quote

I was hoping (against hope) that one of the Access MVPs here might have access to info the rest of us don’t.

Reply | Quote

I don’t think we have Access MVPs aboard. You might ask on UtterAccess, they have more than 10 Access MVPs, and also some people from Microsoft’s Access team as active members.

Reply | Quote

If anyone is interested, I’ve done some more work on this problem, and have been able to isolate the situation that seems to cause the problem, and developed a workaround. As near as I can tell, it only happens if you change the controlsource for objects on the form that appear in either headers or footers. The problem doesn’t seem to occur if the object is in the detail section, nor does it seem to bother it if you are changing the controlsource for grouping items.

In the situation i have, the user can select one of several fields to group on, so in the header I had textbox for which I had to change the controlsource in the report’s open event based on which selection user made. My code referenced the form from which selection was made, something like this:
txtHeader.controlsource = Forms!frmPrepReport!cboGroupField

I deleted this code from thereport’s Open event, and put in this code in the header’s Format event::
txtHeader = me(Forms!frmPrepReport!cboGroupField)

This solved the problem for one report. In another report, however, it didn’t work as good. It solved the Access closing problem, but in this 2nd report (which was based on same query as 1st report that worked perfectly), it didn’t like the use of “Me(Forms!frmPrepReport!cboGroupField)”! I had to create several hidden textboxes bound to each possible sort field, such that the control name was same as the controlsource. Then the “Me(Forms!frmPrepReport!cboGroupField)” worked. I still haven’t figured out why.

Reply | Quote

I have a slightly different experience of the same problem. The ControlSource I need to change in the Report’s OnOpen event is for GroupLevel and then for several textbox controls in the Group Header. In my original design (undertaken in Access 2003 SP2) the report opened in PrintPreview (my usual default) and it worked fine. In Access 2007 this crashed. So I created some code to test for Access version (If Application.Version = “12.0”) Then … and set the value of a variable, used to select the View parameter in the DoCmd.OpenReport statement, to acViewReport instead of acViewPreview. This works o.k. and you can then print the resulting report. Interestingly, if you set the View parameter to acViewNormal (which for a report normally sends direct to printer) it will crash in the same way as for acViewPreview. Also, this work-around will only work in Access2007, because acViewReport is not available in earlier versions.

I don’t know the difference between acViewPreview and acViewReport, but it seems that this bug has something to do with the introduction of the latter.

Reply | Quote

I haven’t had problems changing the ControlSource of a Grouping Field, only that of objects on the report (like a textbox). Right now, I’m using the technique of opening the report hidden in design mode, making the changes, closing it, then opening it like I want (Print or Preview) to actually run the report. (Just another reason why you have separate frontends for everyone!) But this is only useful if it is a .mdb database and won’t work for .mde databases!

Reply | Quote