Update multiple filtered invoices as "paid" and add payment notes without opening each invoice individually

[AHMEDRASHED]

Hello everyone,
I need help with an issue . I want to update multiple filtered invoices as "paid" and add payment notes without opening each invoice individually. Currently, I have a search form (view only) and an invoice form with main forms and subforms. (payment notes in subform)
Is there a VBA code or method to perform this bulk update efficiently?
Your suggestions are greatly appreciated.
Thanks,

Video attached

 

[arnelgp]

you can use the recordset of the subform to do the Update:

With Me.PaymentSubFormNameHere.Form.RecordSetClone
    Do Until .Eof
        .Edit
        !PaidField = -1
        .Update
        .MoveNext
    Loop
End With

 

[GPGeorge]

Will the notes for each paid invoice be identical? If so, you can modify Arnel's code to include that second field in order to update the Payment Notes.

That said, my reading of the question was that the PaidField is in the table bound to the main form.

Please clarify.

 

[ebs17]

Dim sSQL As String
sSQL = "UPDATE YourTable SET PaidField = True, PaymentNote = 'hello'" & _
      " WHERE " & Me.subFormNeme.Form.Filter
CurrentDb.Execute sSQL, dbFailOnError

Is there a VBA code or method to perform this bulk update efficiently?

The first question that comes to mind is always a query.

 

[arnelgp]

The first question that comes to mind is always a query.

not always.
if you have dirtied the subform and you execute the query, ms access will complain about update conflict.

 

[ebs17]

update conflict

- This would also be the same for recordset.edit
- Saving edits beforehand is now no longer a challenge.

 

[Pat Hartman]

How do you know they are paid?

I understand that business is conducted differently around the world. Why is the information identical? Usually, you log payment method and check number or CC number. Notes are only entered occasionally and they wouldn't be identical.

If the user has a paper record such as a check or CC receipt, they would have to search to find the customerID so the payment could be applied to the correct record. Why not just have a button that updates the record with a paid date. Seems like that would be simpler than trying to make a batch process for something that is a record by record process. If you are taking a file of deposits from your bank, then depending on what info is in the file, you might be able to automate the update with a query and not have to touch each account that was paid.

 

[AHMEDRASHED]

Thank you so much

I added a new button and used the code below. I'm not sure if it's the best approach, but it seems to work fine. I used this code because sometimes I need to make multiple invoices, for example, 50 invoices, and add a payment note, such as "Paid in cash on 24/07/2024," for all of them.

Private Sub btnFillPayNote_Click()
    Dim userNote As String

    ' Prompt the user to enter a note
    userNote = InputBox("Enter the note for PayNote:", "Add PayNote")

    ' Check if user entered a note or cancelled the input
    If userNote <> "" Then
        On Error GoTo ErrHandler
        
        Do While Not Me.Recordset.EOF
            ' Open the selected invoice
            DoCmd.OpenForm "frmInvoice", , , "InvoiceNumber='" & Me.InvoiceNumber & "'"
            DoCmd.Maximize

            ' Fill the "PayNote" in the subform "frmNotes"
            Forms!frmInvoice!frmNotes.Form!PayNote = userNote
            
            ' Set Check94 (isPaid) to True
            Forms!frmInvoice!Check94 = True

            ' Save and close the "frmInvoice" form
            DoCmd.Close acForm, "frmInvoice", acSaveYes

            ' Move to the next record
            DoCmd.GoToRecord , , acNext
        Loop
        
        Exit Sub

ErrHandler:
        ' Check if the error is due to reaching the end of the recordset
        If Err.Number = 2105 Then
            ' Reached the end of the recordset, exit the loop
            MsgBox "All selected invoices have been updated.", vbInformation, "Process Complete"
        Else
            ' Handle other potential errors
            MsgBox "An error occurred: " & Err.Description, vbExclamation, "Error"
        End If
    Else
        MsgBox "No note was entered. The PayNote was not updated.", vbExclamation, "No Note Entered"
    End If
End Sub

 

[Gasman]

Do yourself a favour.
Start giving your controls meaaningfule names and not leaving them as check94 and then having to add comments to know what on earth check94 is meant to be. Call it chkPaid or something blnPaid, something along those lines.

I would be using the filtered recordsetclone as @arnelgp proposed, not going to next record until I error.

 

[AHMEDRASHED]

Do yourself a favour.
Start giving your controls meaaningfule names and not leaving them as check94 and then having to add comments to know what on earth check94 is meant to be. Call it chkPaid or something blnPaid, something along those lines.

I would be using the filtered recordsetclone as @arnelgp proposed, not going to next record until I error.

I changed it to "chkPaid". @arnelgprealy, I tried working on it for about 3 hours yesterday but couldn't figure it out. You guys are so professional; when I come here I feel I'm baby in high school . still learning and have a lot to catch up on. Thank you for your comment and your help!

 

[Gasman]

You guys are so professional

Not me, I am just a dabbler, and my memory is so bad these days, that I need to do what I just advised you, so if I go back to something, even a few days later, I can understand it. I still add comments, but my variables/controls will have names that indicate what they are for.

I have learnt a lot here since I joined, everyone else really needs to do the same in my oprinion.
Some people just want everything handed to them on a plate. They learn nothing normally.

 

[AHMEDRASHED]

Not me, I am just a dabbler, and my memory is so bad these days, that I need to do what I just advised you, so if I go back to something, even a few days later, I can understand it. I still add comments, but my variables/controls will have names that indicate what they are for.

I have learnt a lot here since I joined, everyone else really needs to do the same in my oprinion.
Some people just want everything handed to them on a plate. They learn nothing normally.

Thanks for your humble words and sharing your experience, @Gasman . Your advice on using meaningful names and comments for variables and controls is greatly appreciated. I agree that learning never stops, The knowledge and insights shared by you and other members have helped me . I'm grateful for your input, and I look forward to learning more from everyone here.

 

[gemma-the-husky]

Thank you so much

I added a new button and used the code below. I'm not sure if it's the best approach, but it seems to work fine. I used this code because sometimes I need to make multiple invoices, for example, 50 invoices, and add a payment note, such as "Paid in cash on 24/07/2024," for all of them.

View attachment 115309

Private Sub btnFillPayNote_Click()
    Dim userNote As String

    ' Prompt the user to enter a note
    userNote = InputBox("Enter the note for PayNote:", "Add PayNote")

    ' Check if user entered a note or cancelled the input
    If userNote <> "" Then
        On Error GoTo ErrHandler
    
        Do While Not Me.Recordset.EOF
            ' Open the selected invoice
            DoCmd.OpenForm "frmInvoice", , , "InvoiceNumber='" & Me.InvoiceNumber & "'"
            DoCmd.Maximize

            ' Fill the "PayNote" in the subform "frmNotes"
            Forms!frmInvoice!frmNotes.Form!PayNote = userNote
        
            ' Set Check94 (isPaid) to True
            Forms!frmInvoice!Check94 = True

            ' Save and close the "frmInvoice" form
            DoCmd.Close acForm, "frmInvoice", acSaveYes

            ' Move to the next record
            DoCmd.GoToRecord , , acNext
        Loop
    
        Exit Sub

ErrHandler:
        ' Check if the error is due to reaching the end of the recordset
        If Err.Number = 2105 Then
            ' Reached the end of the recordset, exit the loop
            MsgBox "All selected invoices have been updated.", vbInformation, "Process Complete"
        Else
            ' Handle other potential errors
            MsgBox "An error occurred: " & Err.Description, vbExclamation, "Error"
        End If
    Else
        MsgBox "No note was entered. The PayNote was not updated.", vbExclamation, "No Note Entered"
    End If
End Sub

You shouldn't need to add notes like that. If you keep a record of receipts in another table, then you can store the receiptID against all the invoices that were paid by that remittance. All you then need is the date and amount and customer in the receipts table.

If you have some invoices that don't get paid in full, then the above doesn't quite work, and you find you need another table called payment matching, say, so that you can match an invoice to 2 or 3 partial payments. Given an invoice you can display all the payments that were allocated to that invoice. Given a payment you can see all the payment allocations for that payment.

What you need is a slick form that can take a "amount to match" of say $2000, and let you click each invoice that's paid, and verify that the total selected invoices agrees to $2000. So you might need to part pay some invoices. You might need to allow users to add a settlement discount. You might need to add some options to automatch payments for a given month. Whatever helps users match the payment quickly and efficiently. You store the payments and allocation matching in a way that lets you review the matching subsequently. That's what you should be aiming for.

 

[AHMEDRASHED]

A heartfelt thank you to @arnelgp and @Gasman for your guidance and support! RecordsetClone Your advice improved my code significantly,
allowing me to update all invoices in a second. I truly appreciate your expertise and willingness to share your knowledge.

Thank you all for help!

Private Sub btnfill_Click()
Dim userNote As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Dim InvoiceNumber As String
    Dim InvoiceID As Long

    ' Prompt the user to enter a note
    userNote = InputBox("Enter the note for PayNote:", "Add PayNote")

    ' Check if user entered a note or cancelled the input
    If userNote <> "" Then
        On Error GoTo ErrHandler

        ' Get the current database and the filtered recordset from the form
        Set db = CurrentDb()
        Set rs = Me.RecordsetClone

        ' Loop through each filtered record
        If Not rs.EOF Then
            rs.MoveFirst
            Do While Not rs.EOF
                ' Ensure the InvoiceNumber field exists and is valid
                If Not IsNull(rs!InvoiceNumber) Then
                    InvoiceNumber = rs!InvoiceNumber
                    
                    ' Get the InvoiceID from tblInvoice
                    InvoiceID = DLookup("InvoiceID", "tblInvoice", "InvoiceNumber = '" & InvoiceNumber & "'")
                    
                    ' Update the corresponding record in tblInvoice
                    sql = "UPDATE tblInvoice SET isPaid = True WHERE InvoiceNumber = '" & InvoiceNumber & "'"
                    db.Execute sql, dbFailOnError
                    
                    ' Update the corresponding record in tblNotes
                    sql = "UPDATE tblNotes SET PayNote = '" & userNote & "' WHERE InvoiceID = " & InvoiceID
                    db.Execute sql, dbFailOnError
                End If
                
                ' Move to the next record
                rs.MoveNext
            Loop
        End If
        
        ' Notify the user that the process is complete
        MsgBox "All filtered invoices have been updated.", vbInformation, "Process Complete"
        
        Exit Sub

ErrHandler:
        ' Handle potential errors
        MsgBox "An error occurred: " & Err.Description, vbExclamation, "Error"
    Else
        MsgBox "No note was entered. The PayNote was not updated.", vbExclamation, "No Note Entered"
    End If
End Sub

 

[Pat Hartman]

sometimes I need to make multiple invoices, for example, 50 invoices, and add a payment note, such as "Paid in cash on 24/07/2024," for all of them.

You never answered my questions. How do you know an invoice was paid today? How do you take that information to pick the records to update? If you have to touch each record you want to update anyway, why not just add a button to update the record instead of selecting it?

You don't need to use the recordset clone AND running an update query for each and every record you want to update is the most inefficient method you could have devised.

If you answer the questions we ask, we can usually offer the simplest solution.

 

[AHMEDRASHED]

You never answered my questions. How do you know an invoice was paid today? How do you take that information to pick the records to update? If you have to touch each record you want to update anyway, why not just add a button to update the record instead of selecting it?

You don't need to use the recordset clone AND running an update query for each and every record you want to update is the most inefficient method you could have devised.

If you answer the questions we ask, we can usually offer the simplest solution.

Sorry @Pat Hartman for delay replay

How do you know an invoice was paid today? Examble Paid cash direct to my Boss 25 jul.24 ( its small company as personal company )

The Payment note just for him for dubel check .

why not just add a button to update the record instead of selecting it? sorry i dont know how ! can i ask to update one by one or for all records once !

I have QuickBooks also work side by side with this database .
I make this form just for organize data entry from other user and make print for spicfic invoices and Statements for customers with full description (1st QuickBooks has limit on (on description words i think 240 letter only & 2nd isssue and dosnt print full paid or full unpaid invoices once ) thats whay i make this database

 

[Pat Hartman]

why not just add a button to update the record instead of selecting it? sorry i dont know how ! can i ask to update one by one or for all records once

But you don't update the records as a batch using a query or a code loop. You update each record as you touch it.

You also should not have to update both Quickbooks and Access. You update one application and let it feed the other. THAT is batch job. You extract the data from Access and using the batch import capability of Quickbooks, import the transactions entered into Access. If you update Quickbooks, you need to export the data and using that file, run an update query to update the Access database.

Quickbooks also has an ODBC driver. That allows Access to link directly to Quickbooks and as you update the record in Access, the code can also update the corresponding record in Quickbooks. I don't have any clients currently using Quickbooks or the ODBC driver so I cannot provide specific instructions but someone else may be able to.

The point is, that since you have to go through the data to pick out the records you want to update one at a time, you should update the record AT THAT TIME. Instead, you seem to be flagging the records - which is an update. Then going back and finding all the records you updated and updating them again. That's pretty silly.

You're not updating enough records at any one time to worry about efficiency per se but you are making the job much harder than it needs to be.

 

[AHMEDRASHED]

But you don't update the records as a batch using a query or a code loop. You update each record as you touch it.

The point is, that since you have to go through the data to pick out the records you want to update one at a time, you should update the record AT THAT TIME. Instead, you seem to be flagging the records - which is an update. Then going back and finding all the records you updated and updating them again. That's pretty silly.

You're not updating enough records at any one time to worry about efficiency per se but you are making the job much harder than it needs to be.

Thank you @Pat Hartman
did i got your point or still not and code still silly !

Private Sub btnfill_Click()
    Dim userNote As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Dim InvoiceNumbers As String
    Dim InvoiceIDList As String
    Dim InvoiceID As Variant
    Dim noteExists As Variant

    ' Prompt the user to enter a note
    userNote = InputBox("Enter the note for PayNote:", "Add PayNote")

    ' Check if user entered a note or cancelled the input
    If userNote <> "" Then
        On Error GoTo ErrHandler

        ' Get the current database and the filtered recordset from the form
        Set db = CurrentDb()
        Set rs = Me.RecordsetClone

        ' Initialize the InvoiceNumbers and InvoiceIDList strings
        InvoiceNumbers = ""
        InvoiceIDList = ""

        ' Loop through each filtered record
        If Not rs.EOF Then
            rs.MoveFirst
            Do While Not rs.EOF
                ' Ensure the InvoiceNumber field exists and is valid
                If Not IsNull(rs!InvoiceNumber) Then
                    InvoiceNumbers = InvoiceNumbers & "'" & rs!InvoiceNumber & "', "
                End If
                ' Move to the next record
                rs.MoveNext
            Loop
        End If

        ' Remove the trailing comma and space
        If Len(InvoiceNumbers) > 0 Then
            InvoiceNumbers = Left(InvoiceNumbers, Len(InvoiceNumbers) - 2)
        End If

        ' Get the list of InvoiceIDs based on the InvoiceNumbers
        sql = "SELECT InvoiceID FROM tblInvoice WHERE InvoiceNumber IN (" & InvoiceNumbers & ")"
        Set rs = db.OpenRecordset(sql)
        If Not rs.EOF Then
            rs.MoveFirst
            Do While Not rs.EOF
                InvoiceIDList = InvoiceIDList & rs!InvoiceID & ", "
                rs.MoveNext
            Loop
        End If

        ' Remove the trailing comma and space
        If Len(InvoiceIDList) > 0 Then
            InvoiceIDList = Left(InvoiceIDList, Len(InvoiceIDList) - 2)
        End If

        ' Update the corresponding records in tblInvoice
        sql = "UPDATE tblInvoice SET isPaid = True WHERE InvoiceNumber IN (" & InvoiceNumbers & ")"
        db.Execute sql, dbFailOnError

        ' Loop through each InvoiceID to update or insert PayNote
        For Each InvoiceID In Split(InvoiceIDList, ", ")
            noteExists = DLookup("PayNote", "tblNotes", "InvoiceID = " & InvoiceID)
           
            If IsNull(noteExists) Or noteExists = "" Then
                ' Insert new note
                sql = "INSERT INTO tblNotes (InvoiceID, PayNote) VALUES (" & InvoiceID & ", '" & userNote & "')"
            Else
                ' Update existing note
                sql = "UPDATE tblNotes SET PayNote = '" & userNote & "' WHERE InvoiceID = " & InvoiceID
            End If
            db.Execute sql, dbFailOnError
        Next InvoiceID

        ' Notify the user that the process is complete
        MsgBox "All filtered invoices have been updated.", vbInformation, "Process Complete"

        Exit Sub

ErrHandler:
        ' Handle potential errors
        MsgBox "An error occurred: " & Err.Description, vbExclamation, "Error"
    Else
        MsgBox "No note was entered. The PayNote was not updated.", vbExclamation, "No Note Entered"
    End If
End Sub

 

[Pat Hartman]

You are still inserting a row for every row in the recordset. So, if you click on 10 records, you are inserting 10 records 10 times or 100 records. Do you see the duplicates?

Your error handler is not triggered in the correct place.

On Error GoTo ErrHandler ------------------ Is inside an If.

That means that you only have error trapping enabled when the If tests true rather than all the time. Please move this statement to be the first line of the procedure.

Without the database, I don't think I can help you.

 

[Gasman]

BTW Pat is a She, not a He.