>Mohammed
>
>You got answers that will work but you are not asking the right question.
>
>Don't use MAX() to get the next number. It is better to generate next number.
Now since it's a Saturday and I have some time on my hands, here goes the general list of good and valid reasons for this:
- max() will give the next number based on what's already in the table. If two users request the max before either of them writes, they'll get the same number. And that will cause duplicate keys (which may be handy if you lock your keys in the car, but not so nice in a database situation).
- max() will not see any deleted or filtered-out records. If the last record is deleted (the last user just found out that record with the key 16 is bad, and decided to delete it and start over), max() will not see it and will produce 15 again. Also, if any of Set Filter To, Set Order To {index with a FOR clause} or Set Key To is in effect, it will calculate the max() from visible records only.
Therefore, an independent counter is the solution, Q.E.D.