Forum Discussion

hedwards400's avatar
hedwards400
Copper Contributor
Jan 26, 2022

Primary key sequence defined by selections from a Lookup Table

I’m trying to use information in a field to set a number sequence for my primary key field.  For example, I have a dropdown lookup with the following word choices One, Two, Three in field b. If I choose One in field b, I want field a (my primary key) to display a number sequence starting with 10000. If I choose two in field b, then I want field a to display a number sequence starting with 20000, and so on. So if I choose One in field b for two different records, then my primary key should be 10000 for the first record and 10001 for the second.

 

Any suggestions how to make this happen?

access
  • MariaBarnes's avatar
    MariaBarnes
    MVP
    Jan 26, 2022
    You should be able to use the BeforeInsert event of your form to accomplish this. You will also need a Seed table where you store the last used value for each of your sequences. On the BeforeInsert event, get the appropriate Seed number, increment it, and use that for your primary key. You probably do not need me to tell you this, but the type of the Primary Key should NOT be autonumber...

Resources