-----Original Message-----
Replace:
Me.Parent.[InvoiceAmount]
with:
Me.Parent![NameOfOtherSubformContol].Form! [InvoiceAmount]
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Thanks. Only problem seems to be that the invoice amount
is in the second sub form not the parent so it doesn't
reference properly. I tried to change to the name of the
subform but that didn't work either.
Do I have to move the receipt subform into the invoice
subform?
Lori
-----Original Message-----
Use the BeforeUpdate event of the Form (i.e. the
subform)
to sum the *other*
payments from the payment table. Add the current line, and compare the total
to the InvoiceAmount field in the main form.
Something like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curPaid As Currency
Dim strMsg As String
If Not IsNull(Me.AmountPaid) Or IsNull(Me.Parent. [InvoiceAmount]) Then
strWhere = "([InvoiceID] = " & Me.[InvoiceID] & _
") AND ([PaymentID] <> " & Me.PaymentID & ")"
curPaid = Nz(DSum("AmountPaid", "PaymentTable", strWhere), 0) +
Me.AmountPaid
If curPaid > Me.Parent.[InvoiceAmount]) Then
strMsg = "Total paid will be " & Format (curPaid, "Currency") & _
", more than the invoice amount. Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2)
Cancel = True
End If
End If
End If
End Sub
I have a client form with an invoice and payments received
subform in order to post new payments. I want to verify
that the new payment added to the previous payments does
not exceed the invoice amount. The invoice and previous
payment amounts are in the invoice sub form. I know to
use the before update event but how do I reference the
invoice subform for their values?
Thanks in advance for your help.
Lori
.
