Hi Mandy

I use a table driven solution which uses a table called LASTUSEDKEY. The table stores the name of each table and the last used key value. All of my tables have a primary key called KEYID so that the code works with all tables. The keyid is of type INTEGER.

Whenever I add a record I make a call e.g.
**************
lcTable = ALIAS() && Stores current table name
lnNewKey = oApp.GenKeyID( lcTable ) && Generates unique key for current table
IF !EMPTY(lnNewKey) .AND. lnNewKey > 0
INSERT INTO &lcTable (KEYID) VALUES (lnNewKey) && Inserts new record
=TABLEUPDATE()
THISFORM.LockIt() && Locks current record
ELSE
=messagebox(ALLT(oApp.cUserName)+': Record not added, invalid key field - Call CBS.', MB_OK+MB_ICONEXCLAMATION, 'Response Database')
ENDIF
****************

The GenKeyID code looks like this:-
**********************
LPARAMETERS lcTable
LOCAL llInUse, lcMessage, lnX, lnOldRepr


m.lnX = 0

m.lcOldWorkArea= ALIAS()
m.llInUse = USED("LASTUSEDKEY")
IF m.llInUse
SELECT LASTUSEDKEY
ELSE
SELECT 0
USE LASTUSEDKEY
ENDIF

LOCATE FOR ALLT(UPPER(lcTable)) == ALLT(UPPER( LASTUSEDKEY.TABLENAME ))

IF EOF() && couldn't find it - CREATE NEW RECORD
GO BOTTOM
m.lnOldRepr = set("REPR")
SET REPROCESS TO 5 SECONDS
IF RLOCK()
m.lnNextKeyID=LASTUSEDKEY.KEYID+1
=MESSAGEBOX("Creating Key field for " + lcTable)
INSERT INTO LASTUSEDKEY (KEYID, TABLENAME , KEY) ;
VALUES (m.lnNextKeyID, UPPER(m.lcTable), 0)
ELSE
=MESSAGEBOX("Someone else has the Key table locked - try again")
ENDIF
UNLOCK IN LASTUSEDKEY
SET REPROCESS TO (m.lnOldRepr)
ELSE
m.lnOldRepr = set("REPR")
SET REPROCESS TO 5 SECONDS
IF RLOCK()
m.lnX = LASTUSEDKEY.KEY+1
REPLACE LASTUSEDKEY.KEY WITH m.lnX
ELSE
=MESSAGEBOX("Someone else has the Key table locked - try again")
ENDIF
UNLOCK IN LASTUSEDKEY
SET REPROCESS TO (m.lnOldRepr)
ENDIF

IF EMPTY(m.lcOldWorkArea)
SELECT 0
ELSE
SELECT &lcOldWorkArea
ENDIF

RETURN m.lnX