4 Replies - 975 Views - Last Post: 10 August 2016 - 01:14 PM

#1 Kipol  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 08-August 16

Autofill unique identifyer containing letters and numbers

Posted 08 August 2016 - 03:19 PM

Hi everyone,
I'm still fairly new to Microsoft Access but I've hit a bit of an impasse.
How do I auto-fill a record number that also includes letters. Here's an example:

I have the following records in the database:
XYZ001 XYZ002 XYZ003
ABC001
DEF001 DEF002

I want to add a new ABC Record but it should autofill as "ABC002" or if I add an XYZ record it would fill in "XYZ004"

Hopefully I'm not being too vague here.

Is This A Good Question/Topic? 0
  • +

Replies To: Autofill unique identifyer containing letters and numbers

#2 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,579
  • Joined: 12-December 12

Re: Autofill unique identifyer containing letters and numbers

Posted 08 August 2016 - 11:10 PM

Question moved to MS Access subforum.
Was This Post Helpful? 0
  • +
  • -

#3 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: Autofill unique identifyer containing letters and numbers

Posted 09 August 2016 - 06:16 AM

I don't know of anything that is built in that does that. (I also am not familiar if whether or not Access allows stored procedures.) If Access does though, I would write a stored proc that handles the inserting of a new record. Then inside that stored proc you will need to come up with something that does what you need.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,579
  • Joined: 12-December 12

Re: Autofill unique identifyer containing letters and numbers

Posted 09 August 2016 - 10:45 AM

Access 2010 onwards does have a version of procedural code and triggers, Data Macros.

If you use autonumbers then in the field properties you can use formats of "ABC"000, etc.. Autonumbers will have gaps in the sequence and bear in mind that the value stored is simply a number. If possible, just use autonumbers, there is no conflict between these values across the different tables (as long as you identify which table a particular value is coming from).

You could just format the values on forms and reports for display rather than using the field properties, but, in either case, if you are doing this just for display is it worth the trouble?

If you actually want the values stored as ABC001, ABC002, etc., then it will require some effort to ensure their integrity. You could, I suppose, store and retrieve the next value from tables (marking each one off as it is used), but Access isn't an ideal tool to attempt this.
Was This Post Helpful? 1
  • +
  • -

#5 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6340
  • View blog
  • Posts: 25,579
  • Joined: 12-December 12

Re: Autofill unique identifyer containing letters and numbers

Posted 10 August 2016 - 01:14 PM

Sequential Numbering
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1