Problem with incremental number

[basilyos]

hello guys

I want to create serial number for each record
this is my code

Dim strsql01 As String
Dim LastSerialNumber As Long
Dim NewSerialNumber As Long

If DCount("*", "tbl_violation_accepted_telegram") = 0 Then
NewSerialNumber = 1
Else
LastSerialNumber = DMax("Serial_Number", "tbl_violation_accepted_telegram")
NewSerialNumber = [LastSerialNumber] + 1
End If
DoCmd.SetWarnings False
strsql101 = "insert into tbl_violation_accepted_telegram (Serial_Number) Values ('" & NewSerialNumber & "')"
DoCmd.RunSQL strsql101

everything is fine until the serial number become 10 then all the serial number become 10 no more addition

any help ???

 

[Frothingslosh]

If you're just incrementing by 1 each time with no conditions, why not just turn the field into an Autonumber and let Access handle it automatically? (Assuming, of course, that this is either a new table or a SQL Server backend, anyway.)

 

[basilyos]

if I turn it into auto number I cant have the numbers in sequence
the user could delete the last record
and when he delete it in the autonumber can't be sequence anymore

 

[pbaldy]

Sounds and looks like the field tbl_violation_accepted_telegram is text. It needs to be numeric if you want it to act like one.

 

[plog]

The numbers will still be in sequence:

1, 2, 4, 5

Why must 3 exist? What happens in your office if it doesn't? Further, your method allows user to delete numbers and create gaps as well.

As to your issue, is [tbl_violation_accepted_telegram].[Serial_Number] a text or numeric field?

 

[arnelgp]

Is serial_number numeric, then remove the two single quote from your insert query:

... Values (" & NewSerialNumber & ")"