Autonumber Problem

[RainLover]

All these reasons and excuses stem from people who do not understand the proper use of Auto Number.

Sure it can be used as proposed if you truly want, but again why.

A randomly selected Auto Number is just as effective as an in sequential number

Learn how to use D Max plus 1. It does more for you than an Auto Number will any day of the week.

An auto Number must never be seen by the end user, Learn how to apply this rule and you can have an extra slice of Turkey on Sunday.

 

[gemma-the-husky]

All these reasons and excuses stem from people who do not understand the proper use of Auto Number.

Sure it can be used as proposed if you truly want, but again why.

A randomly selected Auto Number is just as effective as an in sequential number

Learn how to use D Max plus 1. It does more for you than an Auto Number will any day of the week.

An auto Number must never be seen by the end user, Learn how to apply this rule and you cab have an extra slice of Turkey on Sunday.

DMax+1 is not 100% foolproof either.

It's not a question of the autonumber being seen or not seen. Autonumber seeds can go astray, and then you need to fix the problem, which is what the OP started the thread about.

 

[RainLover]

Dave

If an Auto Number goes astray it is because of something the user has done or perhaps a crash on the server or front end. This is sometimes referred to as corruption.

Other than that I have only seen D Max fail once. To do this I had to write the fault and prove it. Then write the correction so that if it does happen it can be over come.

I have a Data base that has not been published. Reason being that of its complexity. I think this is one where I did a million tests.

Dave do you have such an animal where you can recreate a failure with D Max.

The answer is that two or more request a number at exactly the same time. In this case both get rejected hence you have an error. Next problem is to solve it.

Dave do you have something different to add?

Cheers

 

[gemma-the-husky]

I don't mean a failure with dmax. But there are 2 issues

a) you may not want a completely sequential series. you may want to restart a sequence with a new number. In which case a better method might be to use a table to provide the next value

b) the timing of the dmax is important. it is best to get the dmax just before saving the record, (rather than early on in the edit/entry) to eliminate/reduce the likelihood of another user getting the same value.

 

[RainLover]

Hi Dave

If you want

Green 01
Green 02
Green 03
Green 04

Red 05
Red 06

Blue 09
Blue 10

Then with a few tricks this is easy with D Max Plus 1. Remember D Max like so many other commands are usually used in conjunction with additional code. D Max is the command the plus one is simply an equation. Access does not have a built in command of D Max plus 1. Like Print is no good without saying print what.

I said in an earlier post that it is possible for Access to request the new number by two users at the same time. I have written code to get around this.

Bear in Mind, we are looking for a fault with D Max not D Max under certain conditions.

Did I read you right by saying these are faults.

Actually it is just a matter of the way the user wants to apply it.

Do you have anything else.

 

[Libre]

Learn how to use D Max plus 1. It does more for you than an Auto Number will any day of the week.

I had no idea this built in function existed, and developed my own method to get the highest value out of a table. I designed a query to descend order the value and return only 1 value - obviously the highest. Then I'd run that query, and have to add one to the returned value, blah blah blah and so on. Had I known about DMax it would have saved me so much time - but at least I know about it now. Very useful function indeed - thanks RainLover!

 

[Leo_Polla_Psemata]

Learn how to use D Max plus 1. It does more for you than an Auto Number will any day of the week.
.

I want to use D MAX +1
I found this example
MyCounter = Nz(DMax("MyCounter", "CustomerT")) + 1
This is placed in the forms properties, "before insert" event

It works. My problem is that I retrieve from another system, 300 records or more. I copy paste in an update form/table all together in one shot.
The D Max +1 generates the same increment number to all these records
If i insert manually the records one by one, okay, it works , 1,2,3 and so on
If I copy paste all together, I get 1 ,1,1,1,

Is there any way I could use dmax+1 ?
I don't like autonumber because it is uncontrollable

 

[CJ_London]

this is an old thread - you should really start a new thread since the question is different.

Anyway, we are here now.

Not sure it can be done with copy/paste. Why not just use an autonumber?

But it can be done with some provisos:

1. requires in your 300 records a field or combinations of fields which will be unique for this particular set of records. This might be an account number, invoice number, unique ID, a transaction number or anything. table could be excel or text file

2. importing would be by linking to your table with 300 records and using an insert query (transfertext/worksheet won't do it)

3. The assignment of the value will be in the order based on your unique field/combination, so this technique not suitable if there is no order - but see 4.

4. if your data is in excel, an easy way to get a uniqueid is to have a column with a calculated number - might be '=row()' or might be '=A2+1' or something else. This has the benefit you can set the order as you want it if it matters.

From 4, you can probably see where this is going - your formula would be

dmax("somenumber","destinationtable")+sourcetable.uniqueid

 

[Leo_Polla_Psemata]

Dear CJ_London, thanks for your input.
You suggest to insert the dmax+1 into excel and drop data into access ready .
This doesn't work because the excel table, feeds three tables which are related
in the DB with one to many relationship , so, the excel could update the last
access table on the row but not the first or the second.

Anyway
I used this code from this site http://allenbrowne.com/ser-40.html

The module "fixes" the autonumber but if I compact and repair database,
the autonumber seeding goes wrong again.

This autonumber MUST have some "engine" somewhere which dictates to the table
which next number to use... Why is it so difficult to find it , touch it and fix it ?

 

[Leo_Polla_Psemata]

And another think I don't understand
Why Autonumbers should not be made visible to the users of the application.??

In my database, autonumbers play important role and are visible by users.
Locked and enabled of course but visible.
Orders we send to our suppliers ,take the autonumber as purchase order number.
We refer to ID's literally as being the id of the task we complete nad file.

Last year, for an unknown reason, autonumber started duplicate. I mean repeating older
numbers. In 2015 i started with a new database, I start a new one every new year,
no problem up to mid November were I came up to same problem.
Now I am thinking of d-max+1 but It doesn't work as I need.

Anyway, why not autonumbers made visible ? (enabled locked) ?
Whats wrong with it ?

 

[CJ_London]

You suggest to insert the dmax+1 into excel and drop data into access ready

No, only the +1 bit could come from excel

This autonumber MUST have some "engine" somewhere which dictates to the table
which next number to use... Why is it so difficult to find it , touch it and fix it ?

and

Why Autonumbers should not be made visible to the users of the application.??

this link may help to explain
http://www.utteraccess.com/wiki/index.php/Autonumbers

Locked and enabled of course but visible.

not much point, they aren't editable anyway

In my database, autonumbers play important role and are visible by users.

see link

Anyway, why not autonumbers made visible ? (enabled locked) ?

they can be, but not editable

Orders we send to our suppliers ,take the autonumber as purchase order number.

see link

 

[gemma-the-husky]

I want to use D MAX +1
I found this example
MyCounter = Nz(DMax("MyCounter", "CustomerT")) + 1
This is placed in the forms properties, "before insert" event

It works. My problem is that I retrieve from another system, 300 records or more. I copy paste in an update form/table all together in one shot.
The D Max +1 generates the same increment number to all these records
If i insert manually the records one by one, okay, it works , 1,2,3 and so on
If I copy paste all together, I get 1 ,1,1,1,

Is there any way I could use dmax+1 ?
I don't like autonumber because it is uncontrollable

before insert is the wrong event. the only safe event to use is the form's beforeupdate. if you use before insert, then the number you get is not stored in the table until later, so another users could also get the same dmax value.

the best (maybe the only) way to process multiple records is to use vba to "get" the relevant sequential refs.

 

[Leo_Polla_Psemata]

Thanks very much for your comments and input.

I fixed manually the autonumber problem using a method "ping pong data"
I exported the table with the problem into an excel.
Then re-imported in a new db the table without autonumber, made a new table with a query (make table query) , then insert an autonumber field, then through "external data" i pulled in the new data base, all the other objects from the old one, re set the table relations and checked that I have not lost any data.

After intense tests and pres, it works fine.
(I hope tomorrow colleagues will not come up with something "strange and weird".