Update field with last payment date

[phoust]

I have an invoice table with a LastPaymentDate field. Payments are entered into InvoicePmt table from a NewPayments form. There can be multiple payments for an invoice. I am trying to update the LastPaymentDate field on the invoice with the date of the most recent payment. I thought I could do it with the OnClick event when saving a new payment, but I can't figure out how I would pass the date parameter. This is my code on the Save button:
Private Sub btnSavePmt_Click()
Dim strPmtDate As Date
strPmtDate = Me!PmtDate

On Error GoTo btnSavePmt_Click_Err

DoCmd.Close acForm, "frmNewPayment"
DoCmd.OpenForm "frmTPAGInvoice", acNormal
DoCmd.Requery
'Me!LastPaymentDate.SetFocus
'Me!LastPaymentDate.Value = strPmtDate
'End If

Obviously I have some major errors in this code. Any suggestions would be greatly appreciated.

 

[GPGeorge]

I have an invoice table with a LastPaymentDate field. Payments are entered into InvoicePmt table from a NewPayments form. There can be multiple payments for an invoice. I am trying to update the LastPaymentDate field on the invoice with the date of the most recent payment. I thought I could do it with the OnClick event when saving a new payment, but I can't figure out how I would pass the date parameter. This is my code on the Save button:
Private Sub btnSavePmt_Click()
Dim strPmtDate As Date
strPmtDate = Me!PmtDate

On Error GoTo btnSavePmt_Click_Err

DoCmd.Close acForm, "frmNewPayment"
DoCmd.OpenForm "frmTPAGInvoice", acNormal
DoCmd.Requery
'Me!LastPaymentDate.SetFocus
'Me!LastPaymentDate.Value = strPmtDate
'End If

Obviously I have some major errors in this code. Any suggestions would be greatly appreciated.

You probably want to stop and rethink this design. If you only care about one payment per invoice, regardless of the amount of the invoice or the amount of the payment, it might be justifiable to store only the most recent installment. In reality, though, I can't imagine a valid payment system that showed only the most recent payment regardless of how many previous payments have been made or the amount due on the invoice.

You should, I suggest, have a table of InvoicePayments which records: InvoiceID, PaymentAmount, PaymentDate.

The "last" or most recent payment for each invoice is the one with the latest, or most recent date. The sum of the payments on an invoice tell you how much has been paid in total, and subtracting that sum from the total due on the invoice tells you how much remains to be paid.

A main form/Subform design for invoices (main form) and invoice payments (sub form) will be the most straightforward approach to storing data.

 

[phoust]

Actually this is what I have. There is a separate payment table. We want the last payment date to show on the invoice form and on a list of paid invoices ready to ship so we can prioritize the shipping by final payment dates.

 

[plog]

Any suggestions would be greatly appreciated.

Don't do this at all.

You shouldn't store data that can be derived logically from other data. If you have a list of all payments you can logically derive the first payment, the second payment, the last payment, etc. So you wouldn't store any of them seperately, you'd just use logic on the data you have and obtain it whenever you need it.

Either you build a query to get the latest payment by customer, or use a DMax to do the same on a report or form, or you retrive it in some other manner--you don't store it seperately.

So, why is it do you think you need to store it in a table? What will that help you accomplish further down the line? Where will this data ultimately go?

 

[phoust]

I tried the DMax function but I'm having trouble with the syntax.

Private Sub Form_Load()
Me.LastPaymentDate = DMax("PmtDate", "InvoicePmt", "InvoiceNo = " & Me!Invoice)
End Sub

This date will show on the invoice form and also on a list of invoices ready to ship. If I use an unbound text box, how would I code it?

 

[GPGeorge]

Actually this is what I have. There is a separate payment table. We want the last payment date to show on the invoice form and on a list of paid invoices ready to ship so we can prioritize the shipping by final payment dates.

Create a query like this and use it to populate the required field on the invoice report:

SELECT InvoiceID, Max(InvoicePaymentDate) as LastPayment
FROM InvoicePaymentTable
GROUP BY InvoiceID

 

[Gasman]

I tried the DMax function but I'm having trouble with the syntax.

Private Sub Form_Load()
Me.LastPaymentDate = DMax("PmtDate", "InvoicePmt", "InvoiceNo = " & Me!Invoice)
End Sub

This date will show on the invoice form and also on a list of invoices ready to ship. If I use an unbound text box, how would I code it?

That should work if invoice is numeric.

 

[MarkK]

I'm having trouble

Maybe describe in greater detail the exact nature of the "trouble." : )

 

[plog]

If I use an unbound text box, how would I code it?

Set the control's source to either:

InvoiceNo Is String:
=DMax("PmtDate", "InvoicePmt", "InvoiceNo = '" & Me!Invoice & "'")

InvoiceNo Is Number:
=DMax("PmtDate", "InvoicePmt", "InvoiceNo = " & Me!Invoice)

 

[phoust]

I'm getting an #Name? error. I've checked the field names and they are correct. I put this as an expression in the control for an unbound text box and set the format of the box to Short Date. I can't figure out what I am doing wrong.

 

[plog]

Can you post a screenshot of InvoicePmt in design view? So we can see all the fields in it?

 

[MarkK]

Me is not valid in a TextBox.ControlSource. Me is an object reference in VBA. Use the name of a control, like...

=DMax("PmtDate", "InvoicePmt", "InvoiceNo = " & [Invoice])

 

[phoust]

 

[phoust]

Can you post a screenshot of InvoicePmt in design view? So we can see all the fields in it?

 

[MarkK]

In this expression...

=DMax("PmtDate", "InvoicePmt", "InvoiceNo = " & [Invoice])

..."InvoicePmt" is a table, not a form.

Can you post a screenshot of InvoicePmt in design view?

I understand this to mean what the Table looks like, not the Form.

 

[plog]

Mark is correct. Show us the table the DMax is using. Not the report using the DMax

 

[phoust]

Mark is correct. Show us the table the DMax is using. Not the report using the DMax

 

[Minty]

Your invoice number is text so you need to escape it with single quotes:

=DMax("PmtDate", "InvoicePmt", "InvoiceNo = '" & [Invoice] & "'")