After delete any record, autonumber start with next number (1 Viewer)

[d_profesor]

I create autonumber as primary key in a table, then i tried to enter a record in that table. Then i deleted it, but when i enter a new record, auto number started with the next number. Ex: i enter a record and autonumber for it is 1. I deleted this record and entered a new record, but i found autonumber is 2. I want the number is 1,

Why is it happen? any solution ? help me pls...

Thanks

 

[T.Smith]

Hi D,

This might be what your looking for.

Change your field from an autonumber to long integer and use the following code on your add record forms.

Dim varID As Variant

     If IsNothing(YourID) Then
        ' Get the previous high number and add 1
        VarID = DMax("YourFieldname", "YourTableName") + 1
        ' If this is first one, then value will be null
         If IsNull(YourID) Then YourID = 1
              FieldID = VarID
     End If

 

[d_profesor]

where should i fill that code? i'm newbie . I tried to fill it in event Form load but i got the error message : IsNothing function is not defined.


Thanks

 

[T.Smith]

D,

Your fine with that. Just create a new module and add this and you should be good.

Public Function IsNothing(ByVal varValueToTest) As Integer
'-----------------------------------------------------------
' Does a "nothing" test based on data type.
'   Null = nothing
'   Empty = nothing
'   Number = 0 is nothing
'   String = "" is nothing
'   Date/Time is never nothing
' Inputs: A value to test for logical "nothing"
' Outputs: True = value passed is a logical "nothing", False = it ain't
'-----------------------------------------------------------
Dim intSuccess As Integer

    On Error GoTo IsNothing_Err
    IsNothing = True

    Select Case varType(varValueToTest)
        Case 0      ' Empty
            GoTo IsNothing_Exit
        Case 1      ' Null
            GoTo IsNothing_Exit
        Case 2, 3, 4, 5, 6  ' Integer, Long, Single, Double, Currency
            If varValueToTest <> 0 Then IsNothing = False
        Case 7      ' Date / Time
            IsNothing = False
        Case 8      ' String
            If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
    End Select


IsNothing_Exit:
    On Error GoTo 0
    Exit Function

IsNothing_Err:
    IsNothing = True
    Resume IsNothing_Exit

End Function

Now you should be good to go.

Toby

 

[ahuvas]

Okay can you explain again step by step. I tried them on a practice database and it didnt work me. I think I didnt understand something.

Can you specify which number to start with?

 

[jdraw]

I create autonumber as primary key in a table, then i tried to enter a record in that table. Then i deleted it, but when i enter a new record, auto number started with the next number. Ex: i enter a record and autonumber for it is 1. I deleted this record and entered a new record, but i found autonumber is 2. I want the number is 1,

Why is it happen? any solution ? help me pls...

Thanks

That's the way autonumber works. It gives the next available number. If you delete a record, that number is gone - forever.

 

[d_profesor]

That's the way autonumber works. It gives the next available number. If you delete a record, that number is gone - forever.

in my case, i don't want it happen, because i want to record the movement of assets. so the an autonumber present a movement. 1 for move 1, 2 for move 2, etc. so the number have a meaning

 

[ahuvas]

Okay Can someone tell me what I am doing wrong?

I created a practice database with a table that has two variables 1- ParticipantID, 2 - Name
ParticipantID was set as a long interger.

I created a form with these two variables, a module with the text above, and in the on form load event I put the first suggestion by Dr. Smith and its still not working, updating it with the ParticipantID field name and tblPractice info.


Should it be an after insert event instead?

 

[T.Smith]

The code above looks at a table and gets the next number for a record instead of using autonumber. It will not do anything with other than that.

 

[Moniker]

Never use an autonumber as a stand-alone field if it's meant to be meaningful. The point of the autonumber is make a record unique, not be a counter. You've been provided several examples here on how to accomplish what you're after. Just don't confuse what an autonumber is and what its purpose is. It's not a counter. It's a generated number designed to provide uniqueness to a record. Because an autonumber cannot repeat, it guarantees uniqueness of the record. Its order, while not arbitrary, is not always sequential.

 

[raward1941uk]

Solved A Problem For Me!

I created a database many years ago in a DOS program to keep track of my orders and work from sub contractors and have carried on using it on the basis, "If it's not broken don't mend it." However, Windows doen't like it so I am repeating it using Access. The job number field , which is above 2600 now, is my unique reference field and I couldn't work out how to create it in Access without starting at 1 again, but you have solved my problem.

Thank you very much.

raward1941uk

All problems are to be overcome!

 

[neileg]

I created a database many years ago in a DOS program to keep track of my orders and work from sub contractors and have carried on using it on the basis, "If it's not broken don't mend it." However, Windows doen't like it so I am repeating it using Access. The job number field , which is above 2600 now, is my unique reference field and I couldn't work out how to create it in Access without starting at 1 again, but you have solved my problem.

If you append your old data to an Access table with an autonumber, Access will start autonumbering from the highest number in your old data (plus 1 of course).

However, the comments about not using an autonumber for fields with meaning remain completely valid.

 

[ahuvas]

Sorry I totally missed what the solution in. Im slow on this front

So we are supposed to change our auto number field to a long interger

Place this code in a module:

Public Function IsNothing(ByVal varValueToTest) As Integer
'-----------------------------------------------------------
' Does a "nothing" test based on data type.
'   Null = nothing
'   Empty = nothing
'   Number = 0 is nothing
'   String = "" is nothing
'   Date/Time is never nothing
' Inputs: A value to test for logical "nothing"
' Outputs: True = value passed is a logical "nothing", False = it ain't
'-----------------------------------------------------------
Dim intSuccess As Integer

    On Error GoTo IsNothing_Err
    IsNothing = True

    Select Case varType(varValueToTest)
        Case 0      ' Empty
            GoTo IsNothing_Exit
        Case 1      ' Null
            GoTo IsNothing_Exit
        Case 2, 3, 4, 5, 6  ' Integer, Long, Single, Double, Currency
            If varValueToTest <> 0 Then IsNothing = False
        Case 7      ' Date / Time
            IsNothing = False
        Case 8      ' String
            If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
    End Select


IsNothing_Exit:
    On Error GoTo 0
    Exit Function

IsNothing_Err:
    IsNothing = True
    Resume IsNothing_Exit

End Function

And do what with this code?

Dim varID As Variant

     If IsNothing(YourID) Then
        ' Get the previous high number and add 1
        VarID = DMax("YourFieldname", "YourTableName") + 1
        ' If this is first one, then value will be null
         If IsNull(YourID) Then YourID = 1
              FieldID = VarID
     End If

Which bits should I replace with my own field/variable name? Only Yourtablename and yourID?

 

[raward1941uk]

The first piece of code checks to see if the field contains nothing. If it does contain nothing it returns "Tue", if it contains something it returns "False".

The second piece of code increments your ID field, whatever you want to call it, by 1. If it is the first record then it will place 1 in the field. If say you have 342 records in your database it will select the largest number, which would be 342 and then add 1 for your 343rd record. If however you delete this record, the next time you enter a record the highest number would be 342 again which would be 342 +1 = 343; which is what you want.

raward1941uk.

 

[ahuvas]

The first piece of code checks to see if the field contains nothing. If it does contain nothing it returns "Tue", if it contains something it returns "False".

The second piece of code increments your ID field, whatever you want to call it, by 1. If it is the first record then it will place 1 in the field. If say you have 342 records in your database it will select the largest number, which would be 342 and then add 1 for your 343rd record. If however you delete this record, the next time you enter a record the highest number would be 342 again which would be 342 +1 = 343; which is what you want.

raward1941uk.

Thanks. I understood this part I just wasnt sure where to place the second smaller part of code (the one not in the module). I tried but it didnt seem to work me?

 

[T.Smith]

Thanks. I understood this part I just wasnt sure where to place the second smaller part of code (the one not in the module). I tried but it didnt seem to work me?

Dim varID As Variant

     If IsNothing([COLOR="red"]YourFormID[/COLOR]) Then
        ' Get the previous high number and add 1
        VarID = DMax("[COLOR="red"]YourFieldnameInTable[/COLOR]", "[COLOR="red"]YourTableName[/COLOR]") + 1
        ' If this is first one, then value will be null
         If IsNull([COLOR="red"]YourFormID[/COLOR]) Then [COLOR="red"]YourFormID[/COLOR] = 1
              [COLOR="red"]YourFormID[/COLOR] = VarID
     End If

Everything in red has to be changed to else it won't work.  If you did a debug you would most likely get the errors highlighted in red.  If you still can't get it to work then give us something to work on.

Hope this helps.
Toby

 

[sabariab]

Hi D,

This might be what your looking for.

Change your field from an autonumber to long integer and use the following code on your add record forms.

Dim varID As Variant
 
     If IsNothing(YourID) Then
        ' Get the previous high number and add 1
        VarID = DMax("YourFieldname", "YourTableName") + 1
        ' If this is first one, then value will be null
         If IsNull(YourID) Then YourID = 1
              FieldID = VarID
     End If

What is "YourID" where does it come from?

 

[hotmalepiyush]

i understood everything but "yourID" part.. what do i change this variable to?? please tell me..
earlier "serial" field was Autonumber and i replaced yourFieldName with serial in the code... but i am not sure what to do with "yourID"

help,
piyush

 

[datAdrenaline]

Hello raward1941uk,

If your table is empty, you can create an APPEND query AND write to the Autonumber field ... then Access will (*should*) pick up the next Autonumber ... if it does not, you can reset the SEED value with this procedure:

Public Function ChangeSeed(lngStartAt As Long, intIncrementBy As Integer)
    Dim strSQL As String     
    strSQL = "ALTER TABLE yourtablename ALTER COLUMN yourfieldname" & _
                   " AUTOINCREMENT(" & lngStartAt & "," & intIncrementBy & ")"
    CurrentProject.Connection.Execute SQL
End Function

I know the other info in the thread has helped you out, but I thought this info to be a viable alternative.

 

[T.Smith]

i understood everything but "yourID" part.. what do i change this variable to?? please tell me..
earlier "serial" field was Autonumber and i replaced yourFieldName with serial in the code... but i am not sure what to do with "yourID"

help,
piyush

It's the name of the control on your form. The control can be hidden or locked and should be reference your number field in a table.

Hope this helps.

Toby