>>>What about extending this sceneio (can never spell that word). I am doing all of this within a transaction. If a separate form or user queries the table, the new value will not be there until the first form or user ends the transaction. This has caused me a problem or two. For example, imagine an ID table shared by all forms and or users. How do I get a new ID when the id table isn't updated until the first transaction ends. ?
>>>
>>>Nancy, seems like everytime I'm stuck Jim's name shows up ;)
>>Stephen,
>>
>>Ok you have to handle getting the iD outside of the transaction by;
>>
>>
>>Move to the ID record
>>LOCK the ID record
>>Get the ID
>>Increment the Record
>>Unlock the record
>>
>>
>>With this appraoch you get the new ID into a memvar, once the memvar is update the ID record has been incremented and unlocked. That prevents others from getting a collision on the id.
>>
>>If you were doing this inside a transaction , the other would have to wait for the transaction to end before they could get an ID because the transactio holds he lock until it is ended.
>
>Agreed, now here is an interesting situation. I'm logging calls and each call gets a new incremented number, begining with 1 for the first call of the month. I record a lot of detail against each call and my call form is modeless. I can receive another call before I complete the first one. The new call number is dependent upon the previous one which is in transaction. Further, I really want my call numbers to be sequence contiguously. But, if I cancel the first call, the second is in transaction and already has what it thinks is the next call number, 2. Hmm. not trying to be argumentative, this is a real situation.
>
>Bye the way Jim, we met a FoxTeach and I hope to be at DevCon. Thanks for the chat and assistance.

Rather than hoping call numbers are contiguous, why not give each call a timestamp and let that be the order determinant? PK really should be surrogate, and this means have no real world meaning. Requiring that they be in chronological order by the time of the beginning of the call is assigning this field real meaning.

Now I'll step off of my soapbox. If you decide that you really want the primary key to reflect the order of the call, just insert the record to the base table as soon as the call is first initiated, or as Jim said, call the nextid method outside of the transaction.(ie- as soon as the call is received, but before you tableupdate) The act of calling the method increments the number, and the next (logically and chronologically) process to call the method gets a higher number.