Hi all,

I need help with the following code. It performs as it should as far as editing the Invoice Balance when a payment is posted. However, the code applies all payments posted to the first record in tblInvoices. How do I target the correct record?


Private Sub PostPayment_Click()

Dim db As Database
Dim rst As Recordset
Dim SearchID As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblInvoices")

SearchID = Forms![subfrmPaymentsNew]![InvoiceID]

    With rst
        .Edit
        ![InvoiceBalance] = Nz(rst![InvoiceBalance]) - (Me.DiscountAmount + Me.PaymentAmount)
        .Update
        .Close
    End With

  Forms!frmInvoices.Refresh

End Sub

Thanks in advance for your help.

Don


D. Bartelt

__________________
D. Bartelt

Hi Ya Don!

You are opening the whole table with no restrictions to get the correct record.

Change your code to this:


SearchID = Forms![subfrmPaymentsNew]![InvoiceID]
SearchSql = "Select * from tblInvoices Where InvoiceID = " & SearchID
Set rst = db.OpenRecordset(SearchSql)

Hope This Helps,

Kevin


dartcoach

Kevin,

Thank you for your quick response. With your advice, the following code works great!!!

Private Sub PostPayment_Click()

Dim db As Database
Dim rst As Recordset
Dim SearchID As String
Dim SearchSql As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblInvoices")

SearchID = Forms![frmInvoices]![InvoiceID]
SearchSql = "Select * from tblInvoices Where InvoiceID = " & SearchID
Set rst = db.OpenRecordset(SearchSql)

    With rst
        .Edit
        ![InvoiceBalance] = Nz(rst![InvoiceBalance]) - (Me.DiscountAmount + Me.PaymentAmount)
        .Update
        .Close
    End With

        Forms!frmInvoices.Refresh

Exit_PostPayment_Click:
    Exit Sub

Err_PostPayment_Click:
    MsgBox Err.Description
    Resume Exit_PostPayment_Click

End Sub

Thanks so much! Take care!

Don

D. Bartelt

Don,

Glad I could help!

Take care.

Kevin

dartcoach

I have a few suggestions.
Can you put future programming topics like this in VBA section?

Use DAO.Database, DAO.Recordset to avoid later confusion with ADO

You have error correcting, but you have no
On Error Goto statement.

As soon as you open the rst, make you sure you have a record to play with.
If you are a new record, you would have a Null value for Search ID, and an Error there too.




Database Agreements