Compare current and previous records with loop and update

[primagic]

I have the following code which is meant to loop through the records in a recordset and tell me if the previous record is the same as the current record.

I need to amend it so if the current accountnumber is the same as the previous account number then update the invoiceid column of the recordset to display the same invoice number. If it is different I need to add a new invoice number to the tbl invoices and use that number for the different account. Hope that makes sense.

Dim rs As DAO.Recordset
   Dim db As DAO.Database
   Dim qd As DAO.QueryDef
   Dim AccountNumber As String
   
   
   Set db = CurrentDb
   Set qd = db.QueryDefs!Query1
   qd.Parameters![Forms!frmDashboard!InvoiceFromDate] = [Forms]![frmDashboard]![InvoiceFromDate]
   qd.Parameters![Forms!frmDashboard!InvoiceToDate] = [Forms]![frmDashboard]![InvoiceToDate]

Set rs = qd.OpenRecordset
    
AccountNumber = rs!AccountNumber
Do While Not rs.EOF
If rs!AccountNumber = AccountNumber Then
'same Account number
MsgBox "Same account"
Else
'different Account Number
MsgBox "different account"
End If
rs.MoveNext
Loop
'Catch the last owner here
rs.Close

I have created a function that adds the new invoice. Can I call this from the above code anywhere?

Public Function AddInvoice()
   Dim rs As DAO.Recordset
   Dim db As DAO.Database
   

Set db = CurrentDb
Set rs = db.OpenRecordset("tblInvoices")
rs.AddNew
rs("InvoiceNumber") = DMax("InvoiceNumber", "tblInvoices") + 1
rs.Update
rs.Close
End Function

 

[jedraw]

Allen Browne shows how to get data from a previous record by using a subquery here

http://allenbrowne.com/subquery-01.html

look at the section

Get the value in another record