Hello Ofer,
Thanks for all the help! I was able to get everything working properly.
However, I do have one more question for you. For each of my payment entries,
I have code that creates a new / running balance called "myNewBalance".
Below is the code:
Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]
If Nz([paymentdate]) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call
MyNewBalance = 0
Else
MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function
Below is the code that I am using for my DMAX per your suggestions:
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"Invoicenumber=" & Me.invoicenumber), 0) + 1
End If
End Sub
What is happening is, is this.. When I add a new invoice and add new
payments MyNewBalance is "carrying" over from my previous invioce and
payments. Do you know how I can "reset" this for each new invoice?
Thanks for all your help!
Brook
Ofer said:
If you have the invoive number on the main form the refer to it as
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![MainFormName].invoicenumber & "'"),
0) + 1
If you have it only on the subform then it should be written as
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![MainFormName]![SubFormName].Form!invoicenumber &
"'"),
0) + 1
טפסי×![Form2]![Form1].Form![Country]
Brook said:
Ofer,
Thanks for the help,
The invoice number is in text format : for example this is a sample
invoice number : SLD-0001.
I updated the code with the code you provided for text. Here is what I
have:
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenumber & "'"),
0) + 1
End If
However, when I add a payment, I am getting a debug error that goes to
this line item.
Any ideas?
Thanks,
Brook
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments",
"InvoiceNumber='" & Forms![frminvoicepayments subform].invoicenum & "'"), 0)
+ 1
End If
:
Try and Add a Where statement to the DMAX
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum=" & forms![FormName].invoicenum), 0) + 1
End If
End Sub
Assuming that the invoice number is a Number
If t is text you should use
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber",
"tblinvoicepayments","InvoiceNum='" & forms![FormName].invoicenum & "'"), 0)
+ 1
End If
End Sub
:
I have a payments subform on my Invoices form, I have a "PaymentNumber" that
I use to update as a DMAX after my Payment Date has been added, however, I
need it to "reset" for each invoice number, how would I do that?
I do have the field "invoiceNumber" in both my tblinvoicepayments and my
invoicepayments subform
Thanks,
Brook
Below is my code:
Begin Code:
Private Sub paymentdate_AfterUpdate()
If Nz(Me.paymentnumber, 0) = 0 Then
Me.paymentnumber = Nz(DMax("PaymentNumber", "tblinvoicepayments"), 0) + 1
End If
End Sub
End Code:
