Auto Increment of records not taking place?

[kaushiks]

Hi everyone,

I am facing a very unique problem. I have written a code for auto incrementing of registration numbers. The auto increment takes place when the form loads or the record is added.

Now the problem is after 13810 records the code is refusing to auto increment. The records are getting added, but with the number 13810. Can u guys please suggest what is causing this out of the blue error?

Public Sub Autogenregest()
On Error Resume Next
Dim a As Long, b As String

If rs1.State = 1 Then rs1.Close
rs1.Open "clientprofile", con, adOpenDynamic, adLockOptimistic
If rs1.BOF = True And rs1.EOF = True Then
    brs.Open "Backup", con, adOpenDynamic, adLockOptimistic
  a = Val(brs(0).Value)
    brs.Close
    
    b = "C" & a
    TxtRegNo.Text = b
Else

     rs1.MoveLast
    a = Val(Right(rs1(0).Value, Len(rs1(0).Value) - 1) + 1)
      
     b = "C" & a
    
    TxtRegNo.Text = b
End If
End Sub

Regards,
Kaushik

[Doogle]

Try removing the "On Error Resume Next" all that is doing is ignoring errors.

[VBFnewcomer]

what does this

rs1(0).Value

contain?

[kaushiks]

Try removing the "On Error Resume Next" all that is doing is ignoring errors.

Hi,

Thanks for the suggestion, but still no sign of any error messages

regards,
Kaushik

[VBFnewcomer]

add a long variable and keep incrementing it with each pass of record in rs. have a if statement when the value reaches 13810, set a break point here. Now go step by step using F8, hover mouse over each variable value, you should be finding the error.

[Doogle]

You are assuming that the last record in the table has the largest Registration Number. That's not necessarily the case. Have you actually examined the complete table ?
If you used an SQL statement like

SELECT * FROM clientprofile ORDER BY <whatever the column name is for the Registration number>

in the rs1.Open statement then when you execute rs1.MoveLast you will be at the record having the largest Registration Number

[VBFnewcomer]

Doogle is correct. Your attempt should be to check the largest value of number in rs1(0) than simply moving to the last record. The fact that largest no. will be found in the last row might only be true for a autonumber field (in your case it is not).

[leinad31]

And implementation is not for a multiuser environment.

TxtRegNo.Text = b

With that code, more than one user can retrieve your ID.

[CVMichael]

Why not just set your ID type to AutoNumber ?