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

 

[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