Automatically Increment a single field Primary Key without using AutoNumber

[ysullivan]

I am working on a database for users who will be Access Novices. They will mostly be doing data entry and have little or no DB design experience.
I want to normalize the database so that referential integrity can be enforced.

I am working on a table that will be a list of people that will be used in other tables. Of course, I want to have a primary key and I believe I should be able to use a single field. I would like to do something that combines two or three letters that describe roles associated with each person and then sequentially number each record. Something along the lines of 'INP0001', 'INP0002', 'SRC0003', etc. I don't care if the number sequences overall or sequences with each text portion. I have NO VB skills.

I know I can use the AutoNumber data type to create a single field primary key that will automatically increment. The issue I have with that method is the number that is generated is not descriptive of the record in any way, so the same data can be entered multiple times. That then means you can end up with two or three records (possibly more) that are all the same but satisfy referential integrity since they each have a unique number associated with them. I wish to avoid this situation.

Would this be more achievable using a form? Again, this needs to be something that will be easy for novice users to use since once the DB is designed it will be turned over to them. I want to make it as easy for people to use as possible.

[June7]

How will custom ID eliminate the possibility of entering the same data more than once? Code will still assign a new id to record. I don't use macros, only VBA, and doubt macro can do this. Review:

https://www.accessforums.net/showthr...isting-records
https://www.accessforums.net/showthr...osite-ID-field

[ysullivan]

How will custom ID eliminate the possibility of entering the same data more than once? Code will still assign a new id to record. I don't use macros, only VBA, and doubt macro can do this. Review:

https://www.accessforums.net/showthr...isting-records
https://www.accessforums.net/showthr...osite-ID-field

Thank you for responding. It sounds like the easiest thing will be to use the AutoNumber field and just review the data routinely to see if duplicate data is entered.

[ssanfu]

In addition to June7's comments: (I don't use macros, either - only VBA code)

I am working on a table that will be a list of people that will be used in other tables. Of course, I want to have a primary key and I believe I should be able to use a single field. I would like to do something that combines two or three letters that describe roles associated with each person and then sequentially number each record. Something along the lines of 'INP0001', 'INP0002', 'SRC0003', etc. I don't care if the number sequences overall or sequences with each text portion. I have NO VB skills.

You will need to "roll-your-own" to have a field like that. But it will have to be a text field - not a good choice for a PK. You can still create the special number ('INP0001'), just don't use it as the PK.

I know I can use the AutoNumber data type to create a single field primary key that will automatically increment. The issue I have with that method is the number that is generated is not descriptive of the record in any way, so the same data can be entered multiple times. That then means you can end up with two or three records (possibly more) that are all the same but satisfy referential integrity since they each have a unique number associated with them. I wish to avoid this situation.

The autonumber type is not meant to be "descriptive" or have meaning, it is only meant to be unique. If it is an autonumber and the PK, it cannot be "entered" multiple times; it cannot be "entered" at all. Access enteres the number - you cannot.

Would this be more achievable using a form? Again, this needs to be something that will be easy for novice users to use since once the DB is designed it will be turned over to them. I want to make it as easy for people to use as possible.

Users should never be allowed access to tables. All data entry should be via forms.
If you are using look-up FIELDS in your tables, you should get rid of them. See http://access.mvps.org/access/tencommandments.htm Read #2 and follow the link.

[ysullivan]

In addition to June7's comments: (I don't use macros, either - only VBA code)

You will need to "roll-your-own" to have a field like that. But it will have to be a text field - not a good choice for a PK. You can still create the special number ('INP0001'), just don't use it as the PK.


The autonumber type is not meant to be "descriptive" or have meaning, it is only meant to be unique. If it is an autonumber and the PK, it cannot be "entered" multiple times; it cannot be "entered" at all. Access enteres the number - you cannot.




Users should never be allowed access to tables. All data entry should be via forms.
If you are using look-up FIELDS in your tables, you should get rid of them. See http://access.mvps.org/access/tencommandments.htm Read #2 and follow the link.

Steve,

Thank you for the additional information.

I do realize that creating a field similiar to what I asked about would require that it be a text field. And I do understand and know that by its nature one does not "enter" a value in the AutoNumber field. If it was possible to do that it would completely negate the use of the AutoNumber field to assign a unique identifier to each record as entered.

I completely agree that users should do all of their data entry via a form! Unfortunately, this database will not be mine and once turned over someone else will be responsible for administering it. I am going to have to make sure that what they have is within their level of understanding. The individual that asked for my help asked me why he would use a form rather than entering the data directly in the table.

All that being said, since I don't want to use any Visual Basic I am resigned to the fact that I am going to have to use the AutoNumber field. It is the best overall solution.