Calculate an amount for a group of records based on number of reco

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

I have an invoice form and the amount of the invoice is to be shared between
a number of employees. THe employees to receive a portion of the invoice are
listed on a subform on the invoice form. I am getting a count of the number
of employees on a particular invoice. When I calculate the invoice I can
calculate the amount each employee gets (and it is always equal between those
employees) but I need a way to update each record with the amount that the
employee will recieve.

For example there are 3 employees on the invoice subform and the invoice is
for 600.00, therefor for each record on the employee subform (3 in total) the
amount field should be updated to 200.00 each

invoice form is called CustInvoice and subform is called empshare and the
field to be updated is called amount

Thanks in advance
 
First off, I would question whether you actually need to store this
information in the recordsource underlying the subform - you already have the
data stored in the form of the invoice amount / number of related records.
This is a simple calculation that can be made whenever you need that
particular data, without duplicating data already stored elsewhere. Not
saying you shouldn't, just saying I'd question the need.

You have a couple of problems facing whatever method you use to update this
field: one, if for some reason the invoice amount changes, you need to
recalculate the values stored in the amount field, and two, if employee
records are added or subtracted from the invoice, you need to update these
values. So I think the way I would approach that would be to run the same
piece of code on both the After Update event of the invoice amount field on
the main form, and the After Insert event of the subform. Note that this
approach fails if the invoice amount changes or employees are added outside
of the forms (if, for example, you input data into the table directly). You
can avoid this problem by not storing the data, and instead just displaying
the results of a calculation.

Function gmGetEmployeeAmount(lngInvoiceID as Long)

'function retrieves the total invoice amount for invoice lngInvoiceID,
'divides that amount equally among related employee records, and finally
'stores the resulting amount in the 'amount' field in the employee records
'This function needs to be accessable to two forms, so save in modules

Dim strSQL as String

'requires a reference to Microsoft ActiveX Data Objects Library
Dim rs as New ADODB.Recordset

strSQL = "SELECT Table1.Invoice, Table2.Amount " _
& "FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.InvoiceID " _
& "WHERE (((Table1.ID)=" & lngInvoiceID & "));"
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
With rs
Do While Not .EOF
![Amount] = Nz(![Invoice],0)/.RecordCount
.Update
.MoveNext
Loop
End With
rs.Close

End Function

To simply display the results of a calculation, set the subform's amount
field control source to a function stored in the subform's form module:

=lmGetAmount([InvoiceID])

Private Function lmGetAmount(lngInvoiceID as Long) As Currency

Dim strSQL As String

strSQL = "SELECT Table1.Invoice " _
& "FROM Table1 LEFT JOIN Table2 ON Table1.ID = Table2.InvoiceID " _
& "WHERE (((Table1.ID)=" & lngInvoiceID & "));"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If Not rs.EOF Then
lmGetAmount = Nz(rs![Amount],0)/rs.RecordCount
End If
rs.Close
 
Back
Top