Start assigned unique key at specified value

[John]

I am trying to set up a table that will use a 6 digit consecutive number
with a letter prefix as the key. I've got the format figured out, but how
do I start the numbering at say N100500 rather then N000001 ?

TIA!

 

[Arvin Meyer [MVP]]

If you are using an autonumber field as part of your system, you can append
a record which is 1 before your starting point. Remember though, that even
though you may be formatting your number with the letter prefix, it will not
be used in searches.

If you have your own system coded counter, you should be able to start it
anywhere you want.

 

[Ken Sheridan]

Will the prefix number always be the same letter, e.g. N? If so then you
can increment the number by putting the following function in the module of a
form bound to the table:

Private Function NextNumber(strStart As String) As String

Dim var As Variant

var = DMax("YourKey", "YourTable")

If IsNull(var) Then
NextNumber = strStart
Else
NextNumber = Left(var, 1) & Mid(var, 2) + 1
End If

End Function

and calling it in the BeforeUpdate event procedure of the form with:

Me.YourKey = NextNumber("N100500")

If the table is currently empty then the function will return whatever value
you pass into it as the strStart argument and this will be inserted as the
value for the first row in the table, otherwise it will increment the
highest existing value in the YourKey column by 1.

This should work fine in a single user environment, but conflicts could
arise in a multi-user environment if two or more users are adding a record to
the table simultaneously. The first user to save the record would succeed,
but an error would be raised when each other user attempts to save their
record. You could either handle the error in the form's Error event
procedure, or you could adopt a different method for generating the next
number which guarantees uniqueness. There are various ways of doing this,
one of which you'll find at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[John]

Yes, using the autonumber field. Unfortunately the append query thing isn't
working out for us. Each time we try (following the instructions in the
Help file) we end up with an error and it doesn't work. Errors are either
something about Criteria or data mismatch. We were just hoping there was an
easier solution for us very novice users.

We'll keep trying.
Thank you!

 

[John W. Vinson]

I am trying to set up a table that will use a 6 digit consecutive number
with a letter prefix as the key. I've got the format figured out, but how
do I start the numbering at say N100500 rather then N000001 ?

TIA!

Well, I'd say DON'T use an Access autonumber. For one thing they cannot
contain letters; for another, they can and will develop gaps in the number
system, and can become random (if you replicate your database).

If the N is constant (all records will have N, never any other letter) then
don't store it in your table at all. Instead, use a Format to just *display*
the letter in conjunction with a Long Integer number as the key. If it is
variable (e.g. you'll have N002154 and K000052 and G000052 as different valid
keys), use two fields, one for the letter, one for the number, and concatenate
them for display.

To increment the number you can enforce all data entry to be done using a
Form, and put code in the form's BeforeInsert event like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim iNext As Long
iNext = DMax("[ID]", "[yourtablename]")
If iNext >= 999999 Then
MsgBox "Shut down the computer, go home. No more ID's available."
Else
Me!txtID = INext+1
Me.Dirty = False ' write the record to disk
End If
End Sub


If you have a multiuser system, this is a LITTLE bit risky as it's just
possible that one user will be inserting a record at the same time as this
code is running and you'll get duplicate value errors. There are ways around
this but they involve more code.

 

[Arvin Meyer [MVP]]

An append query will absolutely work. Use this as an Example:

INSERT INTO tblContacts( ContactID, LastName, FirstName )
SELECT 100499 AS Expr1, "Meyer" AS Expr2, "Arvin" AS Expr3;

Remember what I said about not being able to search on N100500 because "N"
is only formatted, not part of the data. There is no such thing as a number
with the letter "N"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[John]

Thank you! I've got it! The format thing I think was the key.
Cheers!