>I have an application that a database on a networked computer. There are usually around 6 people using this at the same time. My problem is with a logging table called cescontactlog.dbf. When the user changes 1 of three things the application gathers some details and writes a log. Due to complications in the past I have done away with buffering for this table but now I am trying to bring it back to help solve the issue. The issue is that logid's are being duplicated when people have log records written right around the same time. So... if a user sends an email to a client through the application and another user does the same.... we end up with 2 different logs that share the same logid. Here is a snippit of the code I'm using atm:
>
>-----------------------------------
>

>*NEW PART: lock table
>lcErr = ''
>
>TRY
>	SET MULTILOCKS ON && ensure
>	CURSORSETPROP('Buffering', 4, 'cescontactlog')
>	SET REPROCESS TO 5 SECONDS
>
>	*get latest logid - John McCarthy [04/07/2006]
>	lcLogid = cescontactlog_logid_getNext()
>	
>	INSERT INTO cesContactLog (compid, YEAR, logdate, DESCRIPT, USERID, logid) ;
>		VALUES (lcCompid, lcYear, DATETIME(), lcDescript, lcUserid, lcLogid)
>	*perform table update so inserted log sticks
>	=TABLEUPDATE(1, .F., 'cescontactlog')
>CATCH TO lcErr
>FINALLY
>	IF !EMPTY(lcErr)
>		MESSAGEBOX('Failed to save the log entry: ' + lcErr, 16, 'Failed To Save Log')
>	ENDIF
>	*remove buffering
>	CURSORSETPROP('Buffering', 1, 'cescontactlog')
>ENDTRY
>

>-------------------------------
>
>
>Here is the code behind cescontactlog_logid_getNext():
>-------------------------
>

>IF !USED('cescontactlog')
>	USE cesmanager!cescontactlog IN 0 SHARED
>ENDIF
>
>SELECT MAX(ROUND(VAL(logid), 0)) as logid ;
>	FROM cescontactlog ;
>	INTO CURSOR curTemp
>	
>IF _tally = 0
>	MESSAGEBOX('An error has occured which prevents'+;
' a unique record from being created in the cescontactlog table. Please try again.', ;
>		16, 'UID Exception Encountered')
>	RETURN .F.
>ELSE
>	*add 1 and return
>	RETURN ALLTRIM(STR(curTemp.logid + 1, 7, 0))
>ENDIF
>
>

>-----------------------------
>
>Now... I thought that by using pessimistic table buffering it would restrict user access to the table for the brief time it took to save the log (in most cases less than a second). I've made sure that I only use pessimistic table buffering for a very brief amount of time and with Reprocess to 5 seconds... I would think that VFP would just hang and wait for the table to become free so it can get its own unique id and write its own log record.
>
>Any help on this matter would be appreciated. It's a very pesky problem that is taking up much of my time.
>
>Thanks very much,

If you query max value from the same table you're inserting into then you have always have a chance for dupes. Just within the small gap between query and insert you and I could get the same max value (and buffering state changes contribute the chance to be higher).
There are several solutions to the problem:
1) Use an autoinc field.
2) Use an external table to return nextID from (see solution.app)
3) Use a GUID which doesn't have any dependency to existing values nor values generated by someone else on network (my preference).
4) Lock last record, insert adding 1 to logid, unlock.
Cetin