Primary Key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello. I need to create a primary key which will be my customers ID. I don't want this key to start from 1 and so on but I would like it to begin with 1000. Example: when I enter a new customer the ID won't be 2 but 1002.
 
Hello. I need to create a primary key which will be my customers ID. I don't want this key to start from 1 and so on but I would like it to begin with 1000. Example: when I enter a new customer the ID won't be 2 but 1002.
Click to expand...

It's best NOT to use Microsoft's Autonumber facility for this. You can
indeed set the starting point for autonumbers, but you cannot prevent
gaps in the number series; if you delete Customer 3022, that number
will never be used again. For that matter if you start entering a
record and then hit <Esc> to cancel, an autonumber will be "used up"
and will leave a gap.

I'd use a Custom Counter maintained in VBA code instead. The simplest
way is to use a Form's BeforeInsert event, with code resembling

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!CustomerID = DMax("[CustomerID]", "[Customers]") + 1
End Sub

You'ld manually insert the first customer record with ID 1001 before
using this code. Note that this is a bit risky if multiple users may
be assigning new customer ID's at the same time; there may be
collisions. There are more elaborate schemes which avoid this problem.
 
Back
Top