CODEWITHSUNDEEP
ms-accessms-access-2007
Reah Ali
Reah Ali

Reputation: 11

How to create a custom primary key in access 2010?

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.

Upvotes: 1

Views: 1946

Answers (2)

Fionnuala
Fionnuala

Reputation: 91366

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.

Upvotes: 3

Jonathan
Jonathan

Reputation: 26629

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.

Upvotes: 0

Related Questions