Sorry could you explain a little more or add pictures? I don't see what you're trying to do.

Hi James, as I said in the other thread ( https://599cd.com/a?70919 ), your table setup needs to accommodate multiple payments to a single invoice (and/or vice versa).  You won't be able to make any forms or code to do that until you have a many-to-many table relationship as I mentioned.  Here is the table setup suggestion again:

TransactionID     TransactionDate     PaymentID     InvoiceID     PaymentApplied
1                 5/5/23              100           11223         $100
2                 5/20/23             101           11223         $200
3                 6/1/23              200           33445         $1500
4                 6/1/23              200           44556         $800
5                 8/20/23             520           11223         ($50)

This how you relate multiple payments (payment ID 100 and 101) to a single invoice (ID 11223), or one payment (ID 200) to multiple invoices (ID 33445 and 44556).

Hey I came up with a VBA Code to try get this done:

Private Sub BtnAddInvoicePayment_Click()

Dim selectedInvoice As String
Dim clientName As String
Dim newPaymentID As Long

' Get the Selected Invoice Number and Client Name from the Form Controls

selectedInvoice = Me.InvoiceIDNumber.Value
customerName = Me.ClientName.Value

' Generate a New Payment ID or Retrieve it from a Sequence
newPaymentID = GetNewPaymentID()

' Add the New Payment Record to the Payment Table
DoCmd.RunSQL "INSERT INTO Payment (PaymentID, InvoiceIDNumber, ClientName) " & _ "VALUES (" & newPaymentID & ", '" & selectedInvoice & "', '" & clientName & "')"

' Optional: Update the Form or display a Confirmation Message
Me.Requery
MsgBox "Payment added Successfully!"

' Clear the Selected Invoice and Client Name fields for Adding Another Payment
Me.InvoiceIDNumber.Value = "" Me.ClientName.Value = ""

End Sub

' Function to Generate a New Payment ID (assuming an Auto-Incrementing Primary Key)
Function GetNewPaymentID() As Long

Dim rst As DAO.Recordset
Dim newID As Long

Set rst = CurrentDb.OpenRecordset("SELECT MAX(PaymentID) AS MaxID FROM Payment")

If Not rst.EOF Then
newID = rst!MaxID + 1 Else newID = 1

End If

rst.Close
Set rst = Nothing
GetNewPaymentID = newID

End Function

Hey Richard, do you think that as VBA Code would to Generate Additional Payments for "Invoice Number" and/or "Client's Invoice(s)" correctly?

Without reading thru your code... explain to me in English exactly what you're trying to do.

Richard, I am trying to write an VBA Code that I can "Add Additional Payment(s)" for Invoice or apply the Extra Payment Amount to"Other Client Invoices".

OK, I cover this topic extensively in Access Developer 7.