1

I started a small business and have created an accounting DB with advice from my accountant.

In Part of my business I generate invoices for the same customers every month. I use a unique invoice number that helps with tracking etc.

So I haven't been able to get an auto number to work. Currently, I dump the records in monthly then manually update the invoice number, but it is taking too much time now. I have been trying to adapt some code I found online but I am new to the coding end of Access. Most of the time I have been able to get things done with queries and macros.

So this is the code that I am trying to adapt.

Private Sub InvUDB_Click()
Dim InvNo As DAO.Database
Dim rstMInvoice As DAO.Recordset
Dim rstInv1 As DAO.Recordset
   Set InvNo = CurrentDb
   Set rstMInvoice = InvNo.OpenRecordset("MInvoice")   '**** This is the line that the debugger opens up to after the error
   Set rstInv1 = InvNo.OpenRecordset("Inv1")

   rstMInvoice.MoveFirst
   Do Until rstInvoice.EOF
      If rstMInvoice!Invoice = 0 Then

         rstMInvoice.Edit
         rstMInvoice!Invoice = rstInv1!Inv
         rstMInvoice.Update

      End If
      rstMInvoice.MoveNext

      DoCmd.SetWarnings False
         DoCmd.OpenQuery "Invoice_Q000"
         DoCmd.OpenQuery "Invoice_Q002"
         DoCmd.SetWarnings True
   Loop
End Sub

I get an error message 3167:

“You referred to a record that you deleted or that another user in a multiuser environment deleted. Move to another record, and then try the operation again.”

When I Debug it highlights The following row:

Set rstMInvoice = InvNo.OpenRecordset("MInvoice")

I use the two queries below this line to generate a temp table with the new invoice number, (Max Inv No for current year and Month Plus 1).

I have tried running these queries in different places in the code with no luck.

As you can see I do not have a clue as how to write this or how to get this code to run. When I replace everything past the equal sign with a constant it works but it replaces all the qualifying records with that constant.

Can someone help me with this?

1 Answer 1

0
Private Sub InvUDB_Click()
    Dim db As DAO.Database
    Dim rstMInvoice As DAO.Recordset
    Dim rstInv1 As DAO.Recordset
    Dim strSQL As String
    Dim newInvoice As Long
    
    ' Set the current database
    Set db = CurrentDb
    
    ' Open recordsets for MInvoice and Inv1 tables
    Set rstMInvoice = db.OpenRecordset("MInvoice")
    Set rstInv1 = db.OpenRecordset("Inv1")
    
    rstMInvoice.MoveFirst
    Do Until rstMInvoice.EOF
        If IsNull(rstMInvoice!Invoice) Or rstMInvoice!Invoice = 0 Then
            ' Generate a new invoice number using the query
            strSQL = "SELECT Max(Inv) + 1 AS NewInvoice FROM Inv1"
            newInvoice = Nz(DLookup("NewInvoice", strSQL), 1)
            
            ' Update the Invoice field with the new invoice number
            rstMInvoice.Edit
            rstMInvoice!Invoice = newInvoice
            rstMInvoice.Update
        End If
        
        rstMInvoice.MoveNext
    Loop
    
    rstMInvoice.Close
    rstInv1.Close
    Set rstMInvoice = Nothing
    Set rstInv1 = Nothing
    Set db = Nothing
    
    ' Run the queries
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Invoice_Q000"
    DoCmd.OpenQuery "Invoice_Q002"
    DoCmd.SetWarnings True
End Sub

added a check to see if the Invoice field in the MInvoice table is Null or 0, and if so, it will generate a new invoice number and update the field with the new value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.