vba - How to reference a primary key in Ms Access VBA behind a report

Question

I would like to reference the Primary key on the continuous long summary report on my VBA code see below so that I get the correct results per total. The report was combined using domain functions, there is no ISSUE here in terms of performance all is perfect even if there are huge queries to do the actual calculations.

The report has grouped sales data for each salesman coming from unrelated tables & queries, thanks the domain functions has helped to construct this report accurately and with quality performance.

Problems:

I want now to have the final total for all salesmen, but if I reference the subtotal per each salesman, it only picks the value for the first salesman throughout, that is why I want to reference the primary which is based from the salesmen table and visible on the report. On the report I’m using this code below on-load event: VBA Code

Me.txtsalestotal = Me.finalTotal

I'm putting the code above behind the report, but it keeps on picking the same value for one salesman to all salesmen, that's I why a primary key referencing is required here.

I think if I can be helped to know how to reference the primary key (which is Visible on the report) on the above code, then it will sort out this problem for good.

标签： vbams-access

Answer 1

这是我对这个问题的深入研究的答案：

由于用于汇总报表的域函数，因此 sum 函数现在变得无关紧要，这意味着它永远不能用于非绑定控件，除非控件直接绑定到记录源（在这种情况下为查询）。解决方案

(1) 从任何参与的表中创建一个新控件，该表具有该数字中正确的数据类型

(2) 将此控件作为主记录源查询的一部分包含在内，该查询将主要数据提供给相关报表。

(3) 在主报表中将此控件指定为未绑定的计算值作为其记录源，Ms Access 会将其视为普通的原生控件，就像任何其他查询控件一样。

(4) 然后让累积数字只需应用带内报表运行总计并将其隐藏

(5) 最后在页脚添加另一个控件，然后引用隐藏控件，问题解决！