I have an app that has three identical tables (Clients, Prospects, and Vendors). Although the structure is the same, I've kept them separate for performance reasons. Each of these tables has a child table containing line items, much like an Invoice/Invoice line items arrangement.

Forms and reports figure out what table should be used by passing the table name as a parameter when needed, and it all works fine. Simple reports are no problem, using a line like "me.recordsource=gstrTable" in the OnOpen event for the main report.

One final report, however, has me stumped, and it's a timing issue. It's the only one with a subReport, and I need to set the recordsource of this subReport when the report is called. The main report is fine, but the OnOpen event for the subReport will not permit the recordsource to be changed once the report is opened (which I guess makes sense). I've also tried to use the OnOpen event in the main report, but at that point, the subreport isn't active yet, so that fails, too. In all respects, the three subreport tables are identical in structure.

I've faked it by using three separate reports for the time being, but it genuinely annoys me to have three reports that are identical except for the recordsource in the subReport. I don't like to have (or maintain) three almost identical objects if I don't have to. Put another way, I want to change the recordsource when the report loads based on the table I'm currently looking at.

I guess I could use a runSQL query for this, but it seems like a lot of churning just to get around what seems to be a straight-up issue.

Anyone know how to fix this?