Add one digit or plus one help in table field

[bradgerb]

i have a table that has a field called prefix and the first number is 00765 (stored as text) is there a way to have the next record in the table be 00766 and so on? This way I don't have to type that new number everytime in the table. its always +1 from the previous

thanks

Brad

 

[isladogs]

Autonumber fields are ideal for this but too late now....

The following is clunky but should work.

Function GetNewPrefix As String

Dim intPrefix as Integer, MaxPrefix As String, NewPrefix As String

MaxPrefix = DLast("Prefix","MyTable")

intPrefix = CInt(MaxPrefix)   

If intPrefix < 9 Then
	NewPrefix = "0000" & (intPrefix+1)
ElseIf intPrefix < 99 Then
	NewPrefix = "000" & (intPrefix+1)
ElseIf intPrefix < 999 Then
	NewPrefix = "00" & (intPrefix+1)
ElseIf intPrefix < 9999 Then
	NewPrefix = "0" & (intPrefix+1)
Else
	NewPrefix = CStr(intPrefix+1)
End If

GetNewPrefix = NewPrefix

End Function

Then when creating a new record set the Prefix field = GetNewPrefix function output

I'm sure someone will come up with a more elegant solution than this

 

[Wayne]

Here's what I use:

If IsNull(Me.YourFieldName) Then
        Me.YourFieldName = Nz(DMax("YourFieldName", "YourTable"), 0) + 1
    Else
        Exit Sub
End If

I put this in the OnClick event of a command button to generate the next number, but you could put in in the BeforeUpdate event.

 

[isladogs]

Hi Wayne

Much better for a number PK field ....
But how will that work for a text field which is what the OP has

 

[Wayne]

Hi Colin,

Ya, you got a point there. I use it to generate the next numeric invoice number when I am ready to bill out a finished job. It won't work with an alpha-numeric field. I thought he was asking how to advance 00765 to 00766 on the next record, and so on.

If he needed an alpha numeric field, he could concatenate the alpha from a separate field to the numeric field. That could work.

Wayne

 

[Wayne]

Hi Colin,

Just checked my Orders table. My invoice number field is a Short Text field type, and that code works on my form to select the next invoice number. As long as the data in the field is numeric only, it works. Put an alpha character in there and it crashes.

Hope this helps.

Wayne

 

[Pat Hartman]

I understand why you would want a fixed length number and the only way to do that with leading zeros is to make it a string. If in fact the "string" will ALWAYS, ONLY be numeric, you can use the DMax() technique suggested by Wayne but you have to format the generated number.

If IsNull(Me.YourFieldName) Then
        Format(Me.YourFieldName = Nz(DMax(Val("YourFieldName"), "YourTable"), 0) + 1, "00000")
End If

Keep in mind that in a busy application, you might run into conflicts during busy times so whenever you generate your own sequence numbers, you need to trap the "duplicate" error raised when two users have generated the same sequence number and loop to generate a new number until you get one that can be saved.

It is best to generate the number as close to the save as possible so make this the last statement in the BeforeUpdate event and be prepared to add code to trap errors.

It is not too late to switch to an autonumber. You would have to create a new table with the PK defined as autonumber and then run an append query that appends your existing data. The existing sequence would be appended to the PK field.

The downside to the autonumber is that you will at some time end up with gaps. If gaps are a problem, then you'll have to roll your own so you can avoid them.

 

[Wayne]

Hi Pat,

I use this code to trap duplicates:

Private Sub OrderNumber_BeforeUpdate(Cancel As Integer)

    Dim Answer As Variant
    
    Answer = DLookup("[OrderNumber]", "tblOrders", "[OrderNumber] = '" & Me.OrderNumber & "'")
    
    If Not IsNull(Answer) Then
    MsgBox "Order Number already exists. Order Number must be unique.", vbInformation, "Attention"
    Cancel = True
    Me.OrderNumber.Undo
    Else:
    End If
    
End Sub

It's simple, but it works. Mind you, we only have three users on this database. So far, so good.

Wayne

Wayne

 

[Pat Hartman]

Looking for dups before you insert will work the majority of the time but in a busy application it still might not be sufficient. Trapping the error and looping to generate a new one is the only way to avoid gaps and get unique numbers.

Access avoids the issue by committing the Autonumber as soon as it is assigned. However, as we all know, that method can result in gaps if the record add is abandoned.