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

hotmalepiyush

Registered User.
Local time
Today, 09:44
Joined
Jun 3, 2008
Messages
60
now i am getting a "0" in that control and an "invalid outside procedure" error when i try adding new records using that form at that point where i have replaced YourID with the name of my control. records get stored with 0 as serial no.(myfield).
 
Last edited:

szymciooo84

New member
Local time
Today, 09:44
Joined
Mar 27, 2017
Messages
2
hello

Please explain me differences between "YourFieldnameInTable" and "YourTableName"?
"YourTableName" - i know where is tables but where is "YourFieldnameInTable"?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:44
Joined
Feb 28, 2001
Messages
26,999
OK, this is an old post in which I played no part. Having offered a disclaimer, ...

In the context being used in this thread, you must substitute the actual name of the table for which you are attempting to apply a solution where it says "YourTableName." As to "YourFieldNameInTable" - the update of a particular field value requires you to substitute the actual name of the field in the table you were updating. Since this was a discussion on autonumbering (or not), it would be the name of the field that was the prime key of the table on which you are working.

Obviously you find this question or topic via a search, which helps you get going faster. Congratulations on not just stabbing in the dark. You'd be surprised how many people ignore the Search options.
 

szymciooo84

New member
Local time
Today, 09:44
Joined
Mar 27, 2017
Messages
2
Hi

Why i have this part in red?

If IsNull(22) Then 22 = 1
22 = varID


Please see all my code:

Private Sub Ctl22_Click()
Dim varID As Variant

If IsNothing(22) Then
' Get the previous high number and add 1
varID = DMax("Baza", "22") + 1
' If this is first one, then value will be null
If IsNull(22) Then 22 = 1
22 = varID

End If
End Sub
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:44
Joined
Feb 28, 2001
Messages
26,999
First, it is time to take this to a new thread. But second, WHEN is it red? At compile time? I believe I know why this happens, though.

You have two statements that appear to grossly violate VBA syntax rules for expressions.

After an IF ... THEN sequence, the next thing after THEN must be a valid statement or expression. At the beginning of a new line, you must also have a valid statement or expression. The two red lines are not valid because BOTH of them try to assign a value to a numeric constant.

You can say "22 = 1" all you want, but it won't happen. Ditto, "22 = varID" because you cannot assign a value to a constant. Not legal.

Perhaps you meant "ctl22" as the thing to which you were attempting to assign a value?

The "IsNothing" won't work that way either. IsNothing(22) will be false because 22 is not nothing. Did you mean to use ctl22 here, too?

When deciding there was nothing in the control, did you really mean IsNull or IsEmpty (both of which might be possible depending on what you meant?)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:44
Joined
Sep 12, 2006
Messages
15,613
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

personally, I would use the move date to generate the movement sequence, not an artificial sequential number.

what happens in your case if you fail to log a move. so you have movements 1,2,3,4, but you find you have missed movement 3, and therefore need to renumber movements 3 and 4 to new references 4 and 5.
 

avincent61

New member
Local time
Today, 12:44
Joined
Mar 7, 2022
Messages
19
D,

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

Code:
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
Your solution worked great for me!
I know that it doesn't matter if the auto number is sequential, but if/when we use our "Clear Form" button it still burns a number and we just don't want that. This was the easiest solution for our troubles.
 

Users who are viewing this thread

Top Bottom