Kevin,

My NextId code starts with

DO WHILE !RLOCK()

ENDDO

and returns .F. if there is no lock.

Your code still returns _ref even if there is no lock.

You've also got a CheckPk() function in there, and we don't know what it does. Last week on another newsgroup the guy was checking in the main table for the largest value and the routine failed because UserB can sneak a new record into the main table between the time UserA checks the table and the time UserA adds his new record.

My best take would be to give each record a numeric key. Then use the combination of SiteID+KitID+SomeOtheValue as a regular key - OR - use SiteID+KitID+TheValueFromNextID as the key.

Conclusion - for primary keys, stick with what works. Either the NextID method (See stored procedure in TasTrade), or a GUID. Definitely not any values related to the data. These values can change.

Also, Kevin, note how putting < pre > and < /pre > (no spaces) makes the code easier to read.

---

>*------------------------------------------------------------------------------
>*
>*  Program       : GETREF
>*
>*  Authors       : K.Shelton
>*
>*  Date Created  : Nov 92
>*
>*  g_revision    = '1.01'
>*  g_revision    = '1.02' && addition of ORD reference
>*  g_revision    = '1.03' && Ord number to use system Order Prefix not
>*  						  && Site ID
>   g_revision	 = "1.04" && Checks that the allocated PK is not already
> 						  && in use by the Table. This is a temporary test
> 						  && run that will only check CLIENT/REFERRAL/PROPERTY - K Lawrence 11/08/2000
>*
>*------------------------------------------------------------------------------
>private _ref,_alias,llPkOk
>_alias = alias()
>restore from sysstat.mem addi
>p_refType = upper(p_refType)
>select 0
>use nextref
>goto 1
>*=rlock()
>if rlock()
>  goto 1
>  llPkOk = .F.
>do case
>  case p_refType = 'REF'
>
>  	*Checks that the PK doesn't exist
>    DO WHILE !llPkOk
>    	_ref = s_siteID+s_kitID+str(nr_refer,6)
>	    replace nr_refer with nr_refer +1 	
>    	
>    	llPkOk = CheckPk("REFERRAL",_ref,1)
>	   	SELECT nextref
>    ENDDO
>    	
>  case p_refType = 'CLI'
>
>   	*Checks that the PK doesn't exist
>    DO WHILE !llPkOk
>   	    _ref = s_siteID+s_kitID+str(nr_client,6)
>	    replace nr_client with nr_client +1
>    	
>    	llPkOk = CheckPk("CLIENT",_ref,1)
>	   	SELECT nextref
>    ENDDO
>
>  case p_refType = 'PRO'
>	
>	*Checks that the PK doesn't exist
>    DO WHILE !llPkOk
>    	_ref = s_siteID+s_kitID+str(nr_prop,6)
>	    replace nr_prop with nr_prop +1
>    	
>    	llPkOk = CheckPk("PROPERTY",_ref,1)
>	   	SELECT nextref
>    ENDDO
>
>  case p_refType = 'PAC'
>    _ref = s_siteID+s_kitID+str(nr_pack,8)
>    replace nr_pack with nr_pack +1
>  case p_refType = 'RES'
>    _ref = s_siteID+s_kitID+str(nr_resour,6)
>    replace nr_resour with nr_resour +1
>  case p_refType = 'CAR'
>    _ref = s_siteID+s_kitID+str(nr_care,4)
>    replace nr_care with nr_care +1
>  case p_refType = 'LOG'
>    _ref = s_siteID+s_kitID+str(nr_loggen,8)
>    replace nr_loggen with nr_loggen +1
>  case p_refType = 'MES'
>    _ref = s_siteID+s_kitID+str(nr_mess,8)
>    replace nr_mess with nr_mess +1
>  case p_refType = 'DIA'
>    _ref = s_siteID+s_kitID+str(nr_diary,8)
>    replace nr_diary with nr_diary +1
>  case p_refType = 'PUR'
>    _ref = s_siteID+s_kitID+str(nr_purch,6)
>    replace nr_purch with nr_purch +1
>  case p_refType = 'POL'
>    _ref = s_siteID+s_kitID+str(nr_poline,6)
>    replace nr_poline with nr_poline +1
>  case p_refType = 'SUS'
>    _ref = s_siteID+s_kitID+str(nr_susp,6)
>    replace nr_susp with nr_susp +1
>  case p_refType = 'ORD'
>    _ref = s_ordprefix+str(nr_order,7)
>    replace nr_order with nr_order+1
>  otherwise
>    do message with 'Invalid number type passed - phone for help'
>endcase
>unlock
>endif
>use
>if !empty(_alias)
>  select &_alias.
>endif
>return _ref

>
>Thanks
>Kev