How to create a custom primary key in access 2010?

Question

I'm new to access and I am building a table who's primary key is ApplicantID and takes the format of a five digit number xxx07 where xxx is any number between 100 and 999. eg 10107

I am having problems with creating this custom calculated id any help is very much appreciated.

score 3 · Answer 1 · edited May 23 '17 at 12:18

3

A sequential number is more complicated in a multi-user system, for example: Access VBA: Find max number in column and add 1

If possible consider an autonumber as your primary key and add the applicant ID as an additional field with a unique index. It will make things a lot easier.

edited May 23 '17 at 12:18

Community

1
1

answered Feb 05 '13 at 12:00

Fionnuala

90,370
7
114
152

Thank you so much.. i used the autonumber – Reah Ali Feb 07 '13 at 20:36

score 0 · Answer 2 · answered Feb 05 '13 at 11:39

0

See: Incrementing your counter

Instead of their formula (which just adds one), you might want to try something like:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim x As Integer = Nz(DMax("MyCounter", "CustomerT"))
    If x = 0 Then
        MyCounter = 10007
    Else
        MyCounter = x + 100
    End If
End Sub

It won't stop at 99907 though.

answered Feb 05 '13 at 11:39

Jonathan

25,873
13
66
85

1

DMax is only suitable for getting the next number when there is only one user, otherwise there is a reasonable chance that 2 people will get the same id. – Fionnuala Feb 05 '13 at 11:57
Last time I used Access, it was considered a bad idea to have more than one user accessing it at all! It just shows how much time changes everything. Plus, I'm guessing that with only 900 different ApplicantIDs, the op isn't going to have very many inserts. :) – Jonathan Feb 05 '13 at 12:11
1

Access was pretty much always multi-user and pretty stable since 2000. – Fionnuala Feb 05 '13 at 12:17

How to create a custom primary key in access 2010?

2 Answers2