An easy auto incrementing primary key

[fneily]

Let's say you want to create your own autoincrementing primary key. And it's in the form of abc#### - abc001, abc002, etc. What do you all think of this? Create a table with a field say Increment. This will be set to automatically increment. Then create a field for your primary key, eg. customer id. Create an input form. In design view, bring up the property sheet for the text box Increment. In the Default value box, type =DMAX("[Increment]","Tablename&quot+1 Tablename is the name of your table. Then hide this control by changing the Visible box to NO. In the Format box, type 000 (zeros) or how many places you want.
NOTE:I got the above function statement from somewhere in this thread.
Click on the text box for customer id so the property sheet reflects this control. In this control's Default value box, type ="abc"&Format([Incremant],"000&quot.
Set Tab Stop to NO for both controls.
Now when you go to a new record, the Increment goes up by 1 and the result is concatenated to whatever prefix you want in your primary key.
Any critics are welcomed.
Neil

 

[KenReay]

Hi

Why not just include the autoincrement key in the main table and append the "ABC" when you show it to the user?

Since you are using an autonumber in you table Increment I can see no advatnage in your method, or am I missing something? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[MichaelRed]

Autoincrement in any table does not guarntee uniqueness. It does NOT lock the table, therefore 'concurrent' access returns the same value. This generates problems (ERRORS) in use as a primary key. See faq700-184


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

Use an autonumber field. When you display or print it, use the format function to make it look the way you want it to look.

Autonumbers are designed to produce unique numbers, and that's what they do.

jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[fneily]

Thanks to you all. You helped me make the process "cleaner". I appreciate it.
Neil

 

[MichaelRed]

JermyNYC

Autonumbers are designed to produce unique numbers

Is WRONG. AutoNumber only produces an incrementing (or random) value. It is the PK property of the field which ~~~~ keeps it unique, as this does not permit the inclusion of [a | the] duplicate value.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

Michael,

I'm afraid you're just completely wrong on this.

You continue to maintain that Autonumbers produce duplicates, without offering evidence or even annecdotes of personal experience of Autonumber fields generating duplicate values in non-corrupt Access databases.

Try making a table with an autonumber field and a text field. Make the text field the primary key. Enter a bunch of records. Thousands of records. Have hundreds of users do this at the same time. And let me know when the autonumber field generates a duplicate value.

It won't happen. It doesn't happen. I don't know that I would say it can't happen, but I've never seen it, and none of the developers I'm in contact with have ever seen it.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[MichaelRed]

JermyNYC,

I have seen it and have stated so.

If you want/need 'proof', you will (obviously) need to set up the test and execute it yourself. In some early trials w/ Ms. A., when I thought the way you do, I set it up with just two users adding records - with the caveat that I stood right behind them and let each fill out the "form" to add a record up to the "Click" on the cmd button which did the record insertion. From that point, I did a countdown for them to do the simultaneouse Click. It only took a few tries to get the error on one of the entries. We were able to re-produce the error almost at will (within a few tries), until I developed the routine to seperate (and LOCK) the table with the Next sequential number during the fetch / increment procedure.

I cannot construct the scenario for you, and nothing short of direct observation will (obviously) convenience you, so this public discussion will simply continue untill you either give up or convenience yourself. Since I've "been there - done that", it is not likely you will convenience me any more than I can convenience you. You ask me to "Prove" my assertation, and I can only ask you to do the same. Since it is not a common occurance, I cannot prove it and you also appear to not be able to disprove it through anything except your 'experience' which is contrary to mine.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

Michael,

Was that with Access 2?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[MichaelRed]

Belittling my experience is not an answer.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

Michael,

I'm not belittling anything. I'm asking a question.

In Access 2, the next Autonumber was stored in the table header. This meant that, until the record currently being added was saved, no one else could add a record, because the table header prevented the generation of the next Counter, as they used to be called.

This could have caused the error you write about.

But in newer versions of Access this is not an issue. Warning people away from the use of Autonumber fields is akin to warning people against using bound forms, which I've seen people doing. Yes, you can do everything a bound form does with an unbound form. And you may do it faster. But your development time will be much larger, the result will be the same, and you will have avoided approximately zero issues that cannot be dealt with through smart coding.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.

 

[JeremyNYC]

PS: I'm not even sure how implying that you've been working with Access since version 2 could possibly belittle your experience! =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.


Remember to reward helpful tips with the stars they deserve.