How to lock records when 2 users r at the sime time in the same record

[easycode]

Hello all
OS: WINXP
I am experiencing this problem in ACCESS 2002 (XP) i have developed a customized application that is used in a network environment, every time a user clicks on a "new contact" button the system gets a new reference number for that user, but if 3 user hit the "new contact" button at the same time usually one user gets the correct reference number but the other 2 users get the same reference number, if those 3 users click the "new contact button" at different times the system works just fine e.g.

click at the same time:
user 1: 700435
user 2: 700437
user 3: 700437

i am not using the autonumber property, cause the initial number needs to be set up by the user.
I have tested with Tools, options, advanced and use, No lock, edited record and all records but that also did not work

if you need any extra info, please let me know.


here is the code: (i will just copy and paste but i've seen some people place the code within a nice form and the title bar 'Code', i dont know how to do it"


Function getnewrefnum() As Long
On Error GoTo Err_getnewrefnum

Dim rst As adodb.Recordset
Dim rstHist As adodb.Recordset 'This recordset will be used to ensure the new ref-number is not in histdet
Dim fsql As String, fsqlhist As String

Set rst = New adodb.Recordset
Set rstHist = New adodb.Recordset
rst.CursorLocation = adUseServer
fsql = "Select sysparam.[lastrefnum] from sysparam"

rst.Open fsql, CurrentProject.Connection, adOpenDynamic, adLockPessimistic
If rst.EOF And rst.BOF Then 'This will never happen because there is already another routine that controls it
MsgBox "There's no parameters on table sysparam. Call Administrator"
Quit
Else
rst![lastrefnum] = rst![lastrefnum] + 1
rst.Update
fsqlhist = "select left(histdet.[ref-number],len(histdet.[ref-number])-1) as refnumber from histdet where left(histdet.[ref-number],len(histdet.[ref-number])-1)= '" & rst![lastrefnum] & "'"
'here check if this refnum is already taken in histdet
rstHist.Open fsqlhist, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If rstHist.EOF And rstHist.BOF Then
getnewrefnum = rst![lastrefnum] 'if new ref-number is NOT in Histdet then is a VALID ref-numer (no duplicated)
Else
getnewrefnum = 0 'if new ref-number IS in Histdet then is a INVALID ref-numer (duplicated)
MsgBox "Invalid reference number " + rst![lastrefnum] + ". It 's already used in another contact record. Call Administrator", vbInformation, "ECIT Information. ContactsTab,Getnewrefnum"
End If

End If
rst.close
Set rst = Nothing

Exit_getnewrefnum:
Exit Function

Err_getnewrefnum:
If ((Err = 3197) Or (Err = 3260) Or (Err = 3262) Or (Err = 3000) Or (Err = -2147217887)) Then
rst![lastrefnum] = rst![lastrefnum] + 1
getnewrefnum = rst![lastrefnum]
Resume
End If
Resume Exit_getnewrefnum
End Function


Thank you in advance

 

[jadams0173]

You may try using a different cursor like adOPenKeyset

rst.Open fsql, CurrentProject.Connection, [red]adOpenKeyset[/red], adLockPessimistic

To make the code appear in the 'box' use this without the spaces in the brackets

[ code ]
yourcode
[ /code ]

have a look here for more cursor info HTH

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconkeysetcursors.asp

I tried to have patience but it took to long! -DW

 

[easycode]

Thanks for replying jadams0173
I've tried that, with no luch, and i also found this in the link that you posted.

The keyset-driven cursor is difficult to use correctly because the sensitivity to data changes depends on many differing circumstances, as described above. However, if your application "is not concerned with CONCURRENT UPDATES", can programmatically handle bad keys, and must directly access certain keyed rows, the keyset-driven cursor might work for you.

Thanks again

 

[StarPassing]

Check out the code in Thread705-735830

I use it with a lot of users - no problems.

 

[jadams0173]

I made a little test program and changed the lock to optomistic and ran the code on 2 different computers at the same time. I had a form and command button. I was able to trap the error when both machines attempted to update the records and used error handling to 'loop' back and requery the table again for the number. I ran the number form 50 to 100 and the error handler seemed to handle the exception. (I had a msgbox in the error handler to know when it was being executed.). I don't know if this is an option for you or not.

I tried to have patience but it took to long! -DW