Hi Chris,
First, I would have never guessed in a million years that your closing
statement:
"let's see if I can get this right?" Apparently not!
Hope that helps anyway...
was in reference to color formatting of any kind.
The error that I experienced was apparently due to a misintrepretation on my
part. I had performed a quick test of your code, using the following steps on
the Northwind sample database:
1.) Copied the Customers table (data and structure) to a new tblCustomers
table.
2.) Deleted the text-based CustomerID primary key.
3.) Added CustomerID back in as an autonumber. Saved the table. The records
were now numbered 1 through 91.
4.) Converted the autonumber to a number (long) and then set this field as a
primary key.
I copied your procedure to a new module. I ran it in the immediate window,
using the following command:
? GetNextNo ("tblCustomers", "CustomerID")
This resulted in Error 3022:
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again.
I removed the primary key index from the CustomerID field. When I re-ran the
function from the immediate window, I noticed that the first record in the
table had been reassigned an ID from 1 to 2. There were now two records with
CustomerID =2.
Upon further consideration, I realized that your function was intended to
act upon a table that contains a single record. This record holds the number
ready to be assigned to another table that needs a guaranteed increment of
one. This was not made obvious in your post, or even in the original post by
Stephen.
This is called an "Intelligent Key" - and unfortunately that's not a
compliment. The trouble is this setup allows the possibility for repeated
numbers, even when there’s only a single user, but it will happen often
enough in a multiuser environment, too. The reason for this is that the
value of GetNextNo is assigned before the record in the table is actually
updated. If the record fails to update -- or the record is read at the same
time by multiple users in a multiuser environment, then multiple records will
be passed this same number for assignment. The fact that the value sitting in
the table doesn’t match the value being passed to the calling function would
be considered a bug by most programmers.
In summary, my initial statement:
"...especially given the fact that the code that Chris gave does not work.
He even seems to have admitted this with his closing remark..."
was due to a misinterpretation on my part. However, attempting to implement
an intelligent key strategy certainly has problems of its own.
Tom
_________________________________________
:
G’day Tom.
I think the code I posted does work, at least within the context of
the question.
If I’ve misread the question please let me know and I’ll try to fix
it.
BTW. What I meant by: -
"let's see if I can get this right?" Apparently not!
Was that I didn’t get the colour formatting correct.
I was looking to get black code with green comments but got all green
in stead.
(I guess you could say I’m a bit green with this sites tags. :wink:
)
Regards,
Chris.
