>

Hi Bernard,
>
>   Why don't you instead of finding the last client number in CLIENT.DBF have
>a control table which would have the last client number. When you add a record
>to the CLIENT.DBF you can then lock the control, add 1 to the last client
>number, update the control table, store the last client number in a local
>variable, unlock the control table and then return the new client number.
>
>Try something like this:
>
>FUNCTION NextClient
>
>LOCAL nNextClientNo
>
>* ----- Assumes that controltable is already open
>SELECT controltable
>
>IF RLOCK()
>   * ----- Controltable can be updated
>   *       Update the lastclientno in the control table
>   REPLACE lastclientno ;
>      WITH lastclientno + 1
>   nNextClientNo = lastclientno
>   UNLOCK
>   RETURN nNextClientNo
>ELSE
>   * ----- Error Message for Controltable in use
>   *	   and appropriate coding
>ENDIF
>
>
>Hope this helps,
>    Gavin...
>

>
>
>>I have this table in a database.
>>CLIENTS.DBF Cl_id N(10)
>> cl_name C(30)...
>>

Yes Gavin would work. What if I wanted to have Sequential Numbers strictly. Then I'd have to have a reverse bit of code as well for the times the user cancels, and then there are the multi user issues as well. Normally you'd save and update the control table on SAVE but if you look at the code in the vfp wizard, an append blank is done and on cancel a TABLEREVERT() reverts.

Unfortunately there is a trade off here.

The main reason I put this question on the UT was the funny behavior of this code.

If the table was open exclusive then all was ok. If opened shared I got this message. I just wanted to know why. So far none of the explanations have been correct about this. Re the pointer moving when calculate is used?

So how come the same code works in EXCLusive mode? What happens to the pointer then?

Thanks all the same to you and the others who have replied.

Bernard
>>and other fields for address etc.
>>
>>In the Default Value for the field cl_id, I have this expr - nextid("Clients","cl_id")
>>
>>I edited the Stored Procedures for the database and added the foll code:
>>
>>FUNCTION NextId
>>PARAMETERS pAlias,pId
>>PRIVATE ALL LIKE l*
>>SELECT (pAlias)
>>CALCULATE MAX(&pId) TO lnMax
>>RETURN lnMax+1
>>
>>and saved it all.
>>
>>Now close all
>>
>>use clients in 0 SHARED
>>SELECT clients
>>APPEND BLANK
>>
>>and I get the error - "Cannot write to the record because it is in use"
>>
>>I next ran the form wizard and created a clients form. But when I hit the "ADD" button, same result.
>>
>>If I open the table EXCLUSIVE then no error and all works fine.
>>
>>What is happening and how do I solve this? I must have the tables shared!
>>
>>Bernard