>>>>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.

Hi Erik
Yes I agree with all of that and in fact I do have a timestamp on each record and a surrogate PK hidden from the user of course. The call ID is a visible number that the user knows the call by. This is a client request and I have to live with it and its really not out of the norm except that it gets tricky to handle the incremented number when more than one call can be in progress and any one could be cancelled. I guess this steps outside the issue of Transactions but the problem really arose for me when I was trying to use a primary key table to get the next PK. I realize that I have to get the key before I start the transaction. Of couse PK do not have to run serially, there's more than enough integers for all :)