重写子报表数据源时链接主报表和子报表(Linking master report and subreport when rewriting the subreport datasource)

 我有一组'Master'表（TableA，TableB，TableC），每个表可以通过连接表（jctTableAX，jctTableAY，每个可选地链接（多对多）到一组'Linked'表（TableX，TableY，TableZ）， jctTableAZ等） 
 
 表中的每个项目都有一个唯一的（长）标识符（例如Aid，Xid），用于构建链接，以及一个显示给用户的唯一（字符串）选择器（例如，ASelector，XSelector）。 
 
 每个主表或链接表都有一个关联的查询（例如qryA，qryX），它可以提取所有表数据并通过Selector对其进行排序。 
 
 我想制作一套报告，显示“主”表（A / B / C）中每个项目的完整细节; 每个报告都有一组三个子报告，显示链接选择器的列表。 
 
 （实际上有十几个主表和十几个链表，有些表可以是主表或链表，具体取决于上下文，所以我需要一个通用的可扩展解决方案 - 任何报告中都会有大多数子报表但是，因为并非每个表链接组合都受支持。） 
 
 我当前的设计包含每个主表的报告，以及每个链表的单个子报告。 这些子报表包含在每个主报表中，子报表的数据源将在子报表Open事件中重写（示例是针对X类型的链接项的子报表） 
 
     Dim strMasterType As String
     Dim strJunctionTable As String
     Static intCallCount As Long

     If intCallCount = 0 Then 'Only execute this once

         strMasterType = FormItemType(CurrentMasterForm) 'returns A B or C
         strJunctionTable = GetJunctionTable(strMasterType, "X") 'returns the relevant junction table name

         Me.RecordSource = "SELECT " & strJunctionTable & ".*,  qryX.*, " & strJunctionTable & "." & strMasterType & "id AS MasterID FROM " & strJunctionTable & " LEFT JOIN qryX ON " & strJunctionTable & ".Xid = qryX.Xid;"


     End If
     intCallCount = intCallCount + 1
 
 Link Master Field是Aid。 链接子字段是MasterID（这些是在报表设计时设置的，但我相信这应该让我重写不同主报表的子报表源，只要子报表源始终包含MasterID字段。） 
 
 
 
 这个设计是否合理还是有更好的解决方法？ 
 
 
 在链接主字段/链接子字段设置或RecordSource SQL中有一些错误，因为我在子报表中获取相同的数据，即使主项目之间的链接数据不同，但我要进行代码盲测试把它整理出来。 
 

I have a set of 'Master' tables (TableA, TableB, TableC) which can each optionally be linked (many to many) to a set of 'Linked' tables (TableX, TableY, TableZ) via junction tables (jctTableAX, jctTableAY, jctTableAZ etc.) 
 
Each item in a table has a unique (Long) identifier (e.g. Aid, Xid) which are used to construct the links and a unique (string) Selector (e.g. ASelector, XSelector) that is displayed to the user.
 
Each master or linked table has an associated query (e.g. qryA, qryX) that pulls out all the table data and sorts it by Selector.
 
I want to produce a set of reports showing the full detail for each item from a 'Master' table (A/B/C); each report will have a set of three subreports showing lists of the linked selectors.
 
(In practice there are a dozen master tables, and a dozen linked tables, and some tables can be either master tables or linked tables depending on the context, so I need a generic scaleable solution -- the most subreports there will be in any report is 4 however, as not every combination of table links is supported.)
 
My current design has a report for each Master table, and a single subreport for each linked table. These subreports are included in each of the master reports, and the data source for the subreport is rewritten in the subrreport Open event thus (example is for subreport for linked items of type X)
 
     Dim strMasterType As String
     Dim strJunctionTable As String
     Static intCallCount As Long

     If intCallCount = 0 Then 'Only execute this once

         strMasterType = FormItemType(CurrentMasterForm) 'returns A B or C
         strJunctionTable = GetJunctionTable(strMasterType, "X") 'returns the relevant junction table name

         Me.RecordSource = "SELECT " & strJunctionTable & ".*,  qryX.*, " & strJunctionTable & "." & strMasterType & "id AS MasterID FROM " & strJunctionTable & " LEFT JOIN qryX ON " & strJunctionTable & ".Xid = qryX.Xid;"


     End If
     intCallCount = intCallCount + 1
 
Link Master Field is Aid. Link Child Field is MasterID (These are set at report design time, but I believe this should let me rewrite the subreport source for different master reports as long as the subreport source always has in it a MasterID field.)
 
 
 
Is this design sensible or is there a better approach to the problem?
 
 
There's something wrong either in the Link Master Field/Link Child Field set-up or the RecordSource SQL as I'm getting the same data in the subreports even though linked data differs between the master items, but I'm going code-blind trying to sort it out.
 

原文：https://stackoverflow.com/questions/37052190