Loop to update invoice numbers (1 Viewer)

[Eljefegeneo]

I have a form that I use to update the invoice number. The last number is selected and then all the invoices are supposed to be updated one by one with a number in a loop.
I have a two text boxes on the form, Text4 and InvoiceNumber. The following code sets the last highest invoice number. This is done on the OnCurrent event for the form.

  Me.Text4 = DMax("InvoiceNumber", "tblRegularInvoices2")
  InvoiceNumber = Text4

Then I have the following code that will loop through the records, updating the invoice numbers:

  '--this will update all invoice numbers and Set Einvioice sent to False.
   
  MsgBox "The Last Invoice Number Used Was" & " " & Me.InvoiceNumber
  '---Invoice Numbers will be updated
  UpdateCounterInTable
  MsgBox "All Invoice Numbers Have Been Updated"
  '---EInvoice will be set to False for all records if True
  DoCmd.SetWarnings False
     DoCmd.OpenQuery "qryUpdateEinvoiceSentToFalse"
         DoCmd.SetWarnings True
           MsgBox "EInvoice Sent Set To False."
  DoCmd.Close acForm, "frmInvoiceNumbers"

The module UpdateCounterInTable is:

  Public Sub UpdateCounterInTable()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim sqlW As String
  Dim lngCounter As Integer
  lngCounter = Forms!frmInvoiceNumber!InvoiceNumber
  Set db = CurrentDb
  '---sqlW needs to be amended to exclude Special Billing = True or something else to exclude
  '---those that will not be billed this month
  sqlW = "SELECT ClientID, InvoiceNumber FROM tblRegularInvoices2 ORDER BY ClientID"
  Set rs = db.OpenRecordset(sqlW, dbOpenDynaset)
  '--set initial counter value
  Do Until rs.EOF
  rs.Edit
  lngCounter = lngCounter + 1
  rs.Fields("InvoiceNumber") = lngCounter
   
  rs.Update
  rs.MoveNext
  Loop
   
  rs.Close
  Set rs = Nothing
  Set db = Nothing

When I run this I am getting the following error: Run time error 6, Overflow.

When I click on Debug, it stops at the line

  lngCounter = Forms!frmInvoiceNumber!InvoiceNumber

When I designed this DB some time ago, it seemed to work OK. Now that it is ready to be used (finally) this error occurs. The table definition for the field [InvoiceNumber] is set for Number, long integer.

Any suggestions?

 

[jdraw]

Is the form Open?
Is lngCounter greater than 32767?
You could Dim lngCounter as long

 

[Eljefegeneo]

Yes and yes. The numbers must have been less than that when I previously tested it. Made the change and it did the trick. Thank you.

 

[jdraw]

You are welcome. Happy to help.

 

[Gasman]

Yes and yes. The numbers must have been less than that when I previously tested it. Made the change and it did the trick. Thank you.

Why would you call an integer variable lngxxxxxx ?