Duplicate values in Multiuser Environment

[yiotaa]

I have a dbase in miltuser environment.
I have a form frm_Entries that may be use from many user in the same time.
When we enter data we cant see all records that are entered at that time
unless we close and reopen the form.
In that form I have a field name ReceiptNo which is a counter field. When 2
are open the frm_Entries they get the same ReceiptNo but when one of the
saved the record the other one can't contiunue because ReceiptNo field is has
indexed not duplicate.
I want when these happens the field ReceiptNo to be recalculated and to give
new ReceiptNo.

 

[Stefan Hoffmann]

hi,

I have a dbase in miltuser environment.
I have a form frm_Entries that may be use from many user in the same time.
When we enter data we cant see all records that are entered at that time
unless we close and reopen the form.
In that form I have a field name ReceiptNo which is a counter field. When 2
are open the frm_Entries they get the same ReceiptNo but when one of the
saved the record the other one can't contiunue because ReceiptNo field is has
indexed not duplicate.
I want when these happens the field ReceiptNo to be recalculated and to give
new ReceiptNo.

This simple solution should work, even it is not really race condition
secure, but it limits the time window.

Private Sub cmdSave_Click()

If Me.Dirty Then
txtRecieptNo.Requery
Me.Dirty = False
End If

End Sub

To avoid the race condition you need to loop over that until it is
stored, e.g.

Private Sub cmdSave_Click()

On Local Error GoTo 0

Do While Me.Dirty
txtRecieptNo.Requery
Me.Dirty = False
Loop

Exit Sub

LocalError:
If Err.Number = <duplicate entry number> Then
Resume Next
Else
' error handling
End If

End Sub


mfG
--> stefan <--

 

[Dale Fye]

If ReceiptNo is an autonumber field in your database, you will not be able to
do this. Instead, make it a LongInteger data type and make sure it is
indexed (no duplicates), or is the Primary Key for the table.

Then, in the Forms BeforeUpdate event add some code that gets the next value
for that field, something like:

Private sub Form_BeforeUpdate(Cancel as Integer)

'Use the if statement so that you only get a new ReceiptNo if the record
does
'not already have one
If LEN(me.txtReceiptNo) = 0 then
me.txtReceiptNo = NZ(DMAX("ReceiptNo", "YourTableName"), 0) + 1
end if

Exit Sub

With this technique, the receipt number will not have a value until the
record is written to the database, but will prevent the problem you are
encountering.

HTH
Dale