AutoNumbers or a macro? Help required please?

[austen]

Hi,

I know it is not recommended to use the Autonumber as a numbering system for Access but I am unfamiliar with macros and I know of no other way to think of creating an autonumbering system. Does anyone know which macro I should choose and options if I want a sequential numbering system as described please? I use Access 2003 here.

Many thanks,
Austen...

 

[Remou]

It is only not recommended if you want the numbers to mean something, and that is because autonumbers can jump around (1, 5, -1097654) and that can upset users, so it is recommended that users should _not_ see autonumbers.

There are a number of ways to create sequential numbers, some safer than others, so it depends on how many users you have and what you want to do with them.

The best bet is to create a function using an ordinary module page.

Function GetNextNumber() As Long
    GetNextNumber = <SomeCalculation>
End Function

You can now call GetNextNumber from a macro or even use it as the default value of a textbox:

Default Value: =GetNextNumber()

So now you need to decide how complicated <SomeCalculation> needs to be. However, because you have used a function, you can improve the code here without having to go through everything to find where you used it. Let us say that you only have one user and the number is not wildly important:

Function GetNextNumber() As Long
    GetNextNumber = Nz(DMax("NumberFieldNameHere","TableNameHere"),0)+1
End Function

http://lessthandot.com

 

[austen]

Thanks for that. I created a modular page. This is something I have never used before. I entered the text as shown. It looks like this currently;

Function GetNextNumber() As Long
GetNextNumber = Nz(DMax("Deviation No (DN)", "TBL_FrontEnd_EDIR_505"), 0) + 1
End Function

However, how do I call the module from a macro or default value of the field itself to make it sequential. Users wise I have approx 30 but no more than 5-10 in at one time usually. It's a linked database at the moment.

Thanks Austen...

 

[MajP]

Most users want to see a sequential number based on how many records you have. They do not want to see that a record was deleted. You can increment by one, but what happens when you delete a record in the middle?
1,2,3,5,8,9 (deleted 4,6,7)
If that is OK then an incrementing strategy will work. If the users want to see 1,2,3,4,5 then it is more important that you save a sort order field such as a date time stamp. Then regardless of additions and deletions you can use a query to show "record number" 1,2,3,4.. based on when a record was entered.

Bottom line need to decide if it is
sequential
contiguous sequential
non changing sequential

 

[austen]

I never mentioned anything about deleting I'm afraid. I apologise if I worded be mail incorrectly. No records will be deleted.
I want basically that when a user goes into the form and asks for a new record they get the next sequential number as you would in the Autonumber sequence. However, you cannot do this as the primary key acts as the autonumber which is why I was asking about other methods. I am now completely confused. Is the above suggestion incorrect?

 

[Remou]

If you have another look at my answer, you will notice that I say that the code is suitable for a single user. The trouble is that if you have several users, you could end up with both of them having the same number, hence my long post stating that there were several ways to do this according to the importance of the sequence and the number of users. MajP has added a further level of possible complication.

To do this right takes a bit of coding. Here is the proper way:

http://www.tek-tips.com/faqs.cfm?fid=184

But we may be able to simplify it for you.

http://lessthandot.com

 

[MajP]

Not to confuse you, but users often want to make primary keys and autonumbers have unecessary meaning.

It looks to me that you want to use an autonumber for what it is intended to do. Provide a unique increasing number, but not necessarily contigous or starting at 1/0. If that is the case then there is absolutely no problem with using an autonumber especially if this is a single user application.

As long as you are fine with numbers that may end up looking like
123,124,126,129,130,131

There are just few databases I have seen where a user never delets a record out of the table. So if you ever have to delete a record your function may fail, but an autonumber will not.
Ex. You have nine records so the last ID is 9. You have to delete record 2, now you have 8 records. You try to add a new record but the function tries to assign 8 + 1 = 9 which it can not do since the ID of 9 exists.

The issue is that people want autonumbers or primary keys to have meaning. They want "123" to represent the 123rd item in a list or the 123rd item entered. It may be the 1st,4th, etc.

Yes an autonumber can get corrupt, but I have seen far more corruption in forms or the database itself. I am comfortable using autonumbers for their intended purpose and use them often.

 

[Remou]

MajP
In what way can DMax ever return 8 when the last number is 9? I cannot see how this statement can be correct:

Ex. You have nine records so the last ID is 9. You have to delete record 2, now you have 8 records. You try to add a new record but the function tries to assign 8 + 1 = 9 which it can not do since the ID of 9 exists.

A major problem with autonumbers would be the case of invoice numbering. In this country the tax office gets upset if you have unexplained gaps. If you try to add a record and cancel, you can get such a gap in autonumbers, furthermore, in some circumstances you can get very large gaps indeed, or even negative numbers. I have a long-standing table with records from -2145669581 to 2135918810 but it only contains about 4,000 records.

http://lessthandot.com

 

[MajP]

my fault i thought I read Dcount not dmax, but regardless not really my point.

But I disagree then with your next statement. If you have huge gaps, negatives, then the reviewer is more likely to comprehend that the number is only unique. In the above strategies you actually make it more likely to assign meaning to the number. Which may be OK especially if you somehow ensure that you never delete a record. But a reviewer is far more likely to ask questions when seeing 1,2,3,5,6 then a less continuous sequence.

So my point is I am rarely going to store sequence order and attach meaning to it, I will present it dynamically.

PartID AssemblyOrder
abc5137 123

and not try

PartID
abc123

With implied meaning that it is the 123rd assembled. It may not end up that way.

 

[Remou]

My point is that the government tax office here requires invoice numbers to be sequential, and an explanation for any missing numbers. They are probably not the only ones that require certain numbers to be sequential.

http://lessthandot.com

 

[PHV]

the government tax office here requires invoice numbers to be sequential, and an explanation for any missing numbers
So, use a real RDBMS, not a personnal tool like msaccess !

 

[Remou]

Not necessarily. Access should be suitable for very small companies and one-man businesses where RDBMS would be overkill. In addition, invoice numbers were an example of a case where it is necessary to have sequential numbers.

http://lessthandot.com

 

[austen]

Thanks all, it looks like I need to decide whether the database I am using will delete the autonumber or not by the sound of what you are saying. I don't think it will but then again you can never rule it out I suppose but one does have back ups. It would be a lot easier if Access had written in two auto numbers...

 

[Remou]

In my opinion, whether or not the numbers are to be deleted is a red herring. What usually bothers people is the fact that the numbers jump and that undoing does not reset the autonumber.

Try creating a simple table with one field and an autonumber, create a form based on this table, add a record and enter a value for the field, choose until you are back to an empty record, reenter a value. Watch the autonumber, it will go up each time.

The question you need to ask yourself is "how sophisticated and / or compulsive are my users, and do I need a sequential number?" If gaps do not matter and your users are happy understanding that it is just a number, not a number that means something, you can probably use an autonumber.

There is one last problem, and that is once a number appears in public, management are quite likely to want it to mean something, and you will get requests such as "can you make anyone who lives in Uqbar have a number between 1000 and 2000" and that is likely to be the simplest request. The other problem is thinking that this could not happen in your situation, because that is often a famous last thought.

http://lessthandot.com