>>>In any case, only assign the number when the record is saved.
>>>
>>>If you need code for getting a sequence from a sequence table, I can search for mine.
>>
>>Hilmar,
>>
>>I've changed my mind - I better add default values and counters table, so I would appreciate the code.
>>
>>Thanks.
>
>Sure, here goes. I put the following function in database stored procedures; that way it is found by open tables if they are part of the database; otherwise, you might have to issue "open database" or have another copy of the function somewhere else (which will cause problems for maintenance!):
>
>

>**********************************************************************
>FUNCTION SerialNumber(tcSequence)
>	* Get serial number. Used mainly to generate primary keys.
>	* The easiest way to achieve this is to call this function from a fields default value.
>	* Example: default value for primary key "Client" of table "Client" = SerialNumber("Client")
>	* This function accesses table SerialNumber. Fields: Sequence C(30), NextNum I.
>	tcSequence = lower(tcSequence)
>	local lnSelect
>	lnSelect = select()
>	if used("serialnumber")
>		select serialnumber
>	else
>		select 0
>		use serialnumber
>	endif
>	set order to "sequence"
>	seek padr(tcSequence, len(sequence))
>	if not found()
>		append blank
>		replace sequence with tcSequence, nextnum with 1
>	endif
>	local lnReturnValue
>	if lock()
>		lnReturnValue = nextnum
>		replace nextnum with nextnum + 1
>	else
>		lnReturnValue = -1
>	endif
>	unlock
>	select (lnSelect)
>	return lnReturnValue
>ENDFUNC
>

>
>If for some table I want a primary key which will not be seen by the user, I just put the default value at SerialNumber("TableName"). Note that if the user adds a record, then cancels, this number will be skipped, not used again. This is no problem for primary keys without business value.
>
>For document numbers, as I mentioned before, I only assign the document number when the document is saved. In case the save fails, incrementing the sequence number must be undone - i.e., use a transaction. For this purpose, the sequence table should be part of the database, in Visual FoxPro 6. I am not sure how this works in Visual FoxPro 9. I heard something about being able to include tables that were not part of a database, in a transaction.
>
>HTH,
>
>Hilmar.

Thanks, Hilmar. Nice point about PADR - I may need to adjust my procedure.