Simplifying expressions by assigning parts to variables error

[Huddle]

The actual name is "UnReq". It's the first one in the Unassigned column in the Command section

[June7]

I did that same edit and ran report for FY11 and did NOT get the error message.

[Huddle]

I'll work on it some more tomorrow. Did you get the same results when you ran the sub report alone as you did when you ran it in the full report?

[June7]

No. Possibly because the subform opened independently is not filtered by the Master/Child synchronization of the subform container control.

[Huddle]

I thought it could be a corrupt data issue. I deleted and loaded new data twice. I added FYs (06 - 14) to give more options. Each time different FYs gave errors. I've attached an example of what I am getting. If you click on FY 6, 8, 9, 11 or 12 you get an error. If you click on FY 7, 10, 13 or 14 the report works. I can not find any common demoninator in the data that would cause this. I've done Compact & Repair on the database. Please help me find the answer.

[June7]

This is one of the more aggravating and mysterious issues I have encountered. This might take several days of analysis, finding time when I can, before I either give up or solve.

[Huddle]

I appreciate all the hard work you've put into it. I really hope we find a solution. I'd hate to have to start from scratch. I'll keep working on it as well.

[June7]

Issues I am having that interfere with debugging effort:

When I try to open the form nothing happens. I have to open in design view then switch to form view. I commented the code in the combobox AfterUpdate event and form opens. Now to tackle the report.

When I try to open the report directly, Access crashes. Report opens only from the code.

[Huddle]

The form is a pop up form. Mine open on my second screen. I don't know if that makes a difference.

[June7]

Apparently does. I have only one screen at home. I seldom use the Popup and Modal properties of forms because they interfere with debugging. I use the acDialog parameter in the WindowMode argument of DoCmd.OpenForm.

[Huddle]

I took off the pop up and reattached it.

[June7]

Here are some baby steps.

On main report, the expression for Text596 has reference to [D1TotalReq3] - should it be [D1TotalReq4]?

The subreport did not show data until I modified its RecordSource:
SELECT Tbl_Schedule.Schedule_Status, Tbl_Schedule.PK_Schedule_FY, Lookup_Tbl_Commander.Commander, Lookup_Tbl_Commander.Command, Q_Bldg_Commander.FSL, Q_Bldg_Commander.District, Tbl_Schedule.PK_Bldg_Num
FROM Tbl_Schedule INNER JOIN (Lookup_Tbl_Commander RIGHT JOIN Q_Bldg_Commander ON Lookup_Tbl_Commander.Commander = Q_Bldg_Commander.Commander) ON Tbl_Schedule.PK_Bldg_Num = Q_Bldg_Commander.Bldg_Number;

The main report will run fine for any year without the subreport. What is happening between the main and sub reports that will cause this issue????

Don't know if this has any bearing but I do find it interesting that each report's unfiltered RecordSource will return 164 records but there are 168 records in Tbl_Schedule.

The subreport shows different results when run independently I am guessing because it is not synchronized by the building number with the main report. The FY Totals on the independent run appear to be the correct ones.

On main report Label484 and Label610 and on subreport Label471 caption is "FLS 4s Totals", should it be "FSL 4s Totals"?

[Huddle]

You are amazingly observant. I have to be out of the office this afternoon but intend on working on this tomorrow. Thanks again for your diligence.

[June7]

Here is my latest.

Correction about the main report running without the subreport. Something messed up and that quit working or I was mistaken to start with.

The report/subreport runs without the District2 calcs. How I fixed:

1. Copied the main report.
2. Deleted the District2 controls.
3. Changed every D1 reference in names and formulas of the District1 controls to D2. An easy task with Rick Fisher's Find/Replace add-in - costs about $50.
4. Deleted District2 controls from the original report.
5. Copy/paste new District2 controls from the copy to the original.

Report now runs regardless of FY.

Will this report be run filtered to a specific PK_Bldg_Num? If not, don't set the Master/Child links to PK_Bldg_Num fields and the FY totals in subreport will be correct. You could move the FY totals controls to the main report. I did test that and it works. The FY totals still output on second page. Apparently the only reason for the subreport is because too many controls for one report. That means only the Command controls would be on subreport. Synchronization still an issue if report is/is not filtered. This might be managed by modifying Q_Bldg_Command to:

SELECT Tbl_Building_Information.*, Tbl_Bldg_Insp_Connector.*, Lookup_Tbl_Commander_Inspector.Commander, Lookup_Tbl_Commander.Commander_Type, Lookup_Tbl_Commander.Command, Tbl_Schedule.*
FROM Tbl_Schedule RIGHT JOIN ((Tbl_Building_Information LEFT JOIN (Lookup_Tbl_Commander_Inspector RIGHT JOIN Tbl_Bldg_Insp_Connector ON Lookup_Tbl_Commander_Inspector.Inspector = Tbl_Bldg_Insp_Connector.Connecting_Inspector) ON Tbl_Building_Information.Bldg_Number = Tbl_Bldg_Insp_Connector.Connecting_Bldg) LEFT JOIN Lookup_Tbl_Commander ON Lookup_Tbl_Commander_Inspector.Commander = Lookup_Tbl_Commander.Commander) ON Tbl_Schedule.PK_Bldg_Num = Tbl_Building_Information.Bldg_Number;

Both reports can use this query as RecordSource and the FY and any other input parameters could be in this query. That way both reports are fed the same records. My test of this looks good. I do find one curiosity. Tbl_Building_Information has 823 records but the unfiltered query has 824. Indicates a duplication in one of the dependent tables.

[Huddle]

Made the changes and it looks like everything is working fine. There is a duplicate. One of the buildings was on the schedule in FY12 and FY13. I appreciate all your hard work on this. I never would have been able to do it without you.