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
Autonumber fields are ideal for this but too late now....
The following is clunky but should work.
Code:
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
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.
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.
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.
Code:
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.
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.
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.
