For sake of argument, I'm going to refer the problematic table as SysFile. FYI, this table only ever has one record.

All of the forms' data environments that contain SysFile have the buffering set up as optimistic table (5). Following is the scenario that causes a problem:

User A launches a daily processing form which contains Sysfile and starts the process which goes through all the records in the database. At the same time, User B launches a data entry form which also contains Sysfile. When he adds a new record, the new record's primary key is based on Sysfile.NextNumber, which then gets incremented and saved (note: the field is not set up as an auto-inc field, we have a stored proc to do this). After User B is done with data entry, User A's process completes and stores a processing date in SysFile, followed by a TABLEUPDATE(). The problem is that User A's copy of SysFile overwrites User B's copy and the NextNumber field appears as if it has never incremented.

SysFile does need to be open during User A's process because of information in that table that is used during the process.

Admittedly, our multi-user contention design is clearly faulted. Could someone please throw forth some ideas as to how we can resolve this issue and better handle multi-user issues?

Thanks.

Laterness,
Jon