基于当前记录计算子表单的控制

I have the following: main form "customer" from a "customer" table. subform "invoices" with fields "invoice date", "invoice amount" "customer id" etc. from a table "invoices"

我有以下内容:来自“客户”表的主要表单“客户”。从表格“发票”中填写“发票日期”,“发票金额”,“客户ID”等字段的“发票”

whenever user clicks or goes to a record in the "invoices" sub form. I would like a "total so far" control to calculate the sum of the "invoices amount" up until the date of the current record being "clicked" or selected.

每当用户点击或转到“发票”子表单中的记录时。我想要一个“总到目前为止”的控制来计算“发票金额”的总和,直到当前记录被“点击”或选中的日期为止。

i.e. for customer microsoft with invoices: 1) may 2 09, $150 2) may 3 09, $200 3) may 4 09, $500

即对于客户微软发票:1)可能2 09,$ 150 2)可能3 09,$ 200 3)可能4 09,$ 500

If user clicks on record 2), "total so far" should show $350 If user clicks on record 1), "total so far" should show $150 If user clicks on record 3), "total so far" should show $850

如果用户点击记录2),“总到目前为止”应该显示350美元如果用户点击记录1),“总到目前为止”应该显示150美元如果用户点击记录3),“总到目前为止”应显示850美元

Currently, I am using DSum function on an event "OnCurrent" in the subform "invoices" to set the "total so far" value. Is this method slow, inefficient?

目前,我正在子窗体“发票”中的事件“OnCurrent”上使用DSum函数来设置“总到目前为止”的值。这种方法速度慢,效率低吗?

Any other simpler,cleaner,more elegant,faster, efficient method using ms access features?

使用ms访问功能的任何其他更简单,更简洁,更优雅,更快速,更有效的方法?

I want the "invoices" subform to show ALL the invoices for this customer no matter which record is clicked.

无论点击哪条记录,我都希望“发票”子表单显示该客户的所有发票。

2 个解决方案

#1

If the DSum method works for you then use it.

如果DSum方法适合您,则使用它。

If it's too slow then another way is to use a recordsetclone and loop through the records. This is more code but it's more efficient since it doesn't have to hit the database. You do need a unique key.

如果它太慢,那么另一种方法是使用记录集克隆并循环记录。这是更多的代码,但它更有效,因为它不必命中数据库。您需要一个唯一的密钥。

Private Sub Form_Current()

  Dim rst As DAO.Recordset
  Dim subTotal As Currency
  Dim rec_id As Long

  'get clone of current records in subform'
  Set rst = Me.RecordsetClone

  'save current record id'
  rec_id = Me.rec_id

  rst.MoveFirst

  'loop and total until current is reached'
  Do Until rst![rec_id] = rec_id
    subTotal = subTotal + rst![InvoiceAmt]
    rst.MoveNext
  Loop

  'add last amount on current record' 
  subTotal = subTotal + rst![InvoiceAmt]

  Set rst = Nothing

  'set text box with subtotal'
  Me.Text2 = subTotal

End Sub

The other way is to build a sql query with a sum() but that takes even more code and hits the database again.

另一种方法是使用sum()构建一个sql查询,但需要更多代码并再次访问数据库。

#2

You could put a hidden control with a Dsum in the footer of the subform, and then refer to that one from the main form. The Dsum would have its 3rd argument like "InvoiceId <= " & InvoiceId

您可以在子窗体的页脚中放置一个带有Dsum的隐藏控件,然后从主窗体中引用该控件。 Dsum将有第三个参数,如“InvoiceId <=”和InvoiceId

No need for any VBA/event in that case.

在这种情况下不需要任何VBA /事件。

#1