0

I am new in microsoft access. I was just wondering on how can I use create a custom primary key? for example abc-123 format?

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
sean
  • 9,198
  • 22
  • 65
  • 80

2 Answers2

2

It depends on how you want the abc-123 values to be created.

If you want to create them by yourself in your code, just create a Text column and use that as your primary key.

If you want Access to create these values...that's not really possible. The only thing that Access is able to auto-generate are increasing numerical values (data type AutoNumber).

So the best thing you can do is to use an AutoNumber internally as the actual primary key, and create the abc-123 value out of that, just for displaying.

Here are some examples how to do this, from previous similar questions that I answered in the past:

Disclaimer: I don't know if a similar approach would work in your case.
If not, you need to give more information how exactly you want your numbers to be created:

  • do you want the number to increase?
  • do you want the letters to change/"increase"/always stay the same?
Community
  • 1
  • 1
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
2

Actually, you could create a table trigger if using 2010 or later. The table trigger could take some field (where you get the abc from) and then some other field (seq num) and then add + 1 to the value.

The "air" code would look like this:

enter image description here

The beauty of the table trigger is it runs at table (data engine) level, and thus if you open the database with ODBC, VB.net, FoxPro, Access etc. then the PK key will always auto generate for you.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Thanks, Albert. I had tried something similar with `DMax()` and it worked when a record was inserted from within Access itself, but it caused INSERTs from other applications to fail. ("DMax is not allowed in a Data Macro.") I've been trying variations on the above in Access 2010, but if my LookUpRecord tries to do a SELECT on the same table to which the Data Macro belongs I get "The 'LookUpRecord' macro action has an invalid value for the 'Look Up A Record In' argument." (A SELECT from a different table seems to be OK.) Could you please update your answer to provide a working example? Thanks! – Gord Thompson Dec 12 '13 at 22:07
  • The above example is a working example (it missing code if the prefix never existed in the database). If you look close in the upper left shows "custpart" before change in the windows heading. So the posted code is store procedure running on the SAME table as the update code. The important part is when you reference a field name are you referencing the current table as a result of the trigger, or the query? As a result note how I used an Alias to distinguish the two tables. – Albert D. Kallal Dec 13 '13 at 21:59
  • Did you by chance create your example in Access 2013? I just tried the exact same thing again in Access 2010 and got the same error I cited in my previous comment when I tried to save the Before Change macro. (See screenshot [here](http://i.stack.imgur.com/jIqwf.png).) I notice that the macro editor layout in your screenshot is slightly different from mine, so I'm wondering if perhaps your approach is the result of an enhancement introduced in Access 2013. – Gord Thompson Dec 16 '13 at 01:37
  • Thanks again, Albert. I downloaded your sample database and the existing Data Macro works fine, but as soon as I "touch" it in Access 2010 I am no longer able to save it (same error as above). I have opened a new question [here](http://stackoverflow.com/q/20661510/2144390). – Gord Thompson Dec 18 '13 at 15:05