multi-user environment

  • Thread starter Thread starter coz
  • Start date Start date
C

coz

I'm working in a multi-user environmrent where the primary
key is a control number which can't be duplicated. When
the form is opened, a number is automatically assigned.
The problem is that when multiple people are entering
information on the form, the number which is primary key
control is duplicated because it is not saved when a
person begins entering information. The person who
finishes entering values on the form first gets the number
while others get a duplicate error message when the
finish. Is there any way to fix this?
 
I'm working in a multi-user environmrent where the primary
key is a control number which can't be duplicated. When
the form is opened, a number is automatically assigned.
The problem is that when multiple people are entering
information on the form, the number which is primary key
control is duplicated because it is not saved when a
person begins entering information. The person who
finishes entering values on the form first gets the number
while others get a duplicate error message when the
finish. Is there any way to fix this?
Click to expand...

How is this number determined/generated? If it is a sequential number, it could
be stored in a "next number" table (this needs to be placed in the shared data
file), the value in which can be read and incremented via code to ensure
uniqueness, preferably in the form's "BeforeUpdate" event procedure where there
can be some control over generating a new number only if the record is saved.
Here is an example function that does this (to use it, copy the function to a
standard module - a global module, not a module behind a form or report):

'**********EXAMPLE START
Public Function fncNextNumber(strTableName As String, _
strFieldName As String) As Long
' Comments : Returns "next number" value and
' increments value in field
' Parameters: strTableName - The name of the "next
' number" table
' strFieldName - The name of the field
' holding the "next number" value
' Returns : Long - The "next number"
' Created : 11/18/03 12:14 Bruce M. Thompson
' Usage : lngNextNumber = fncNextNumber("MyTableName", _
' "MyFieldName")
' Prerequisites : Reference to DAO Object Library version
' appropriate for your version of Access
' See following KB article for guidance:
' http://support.microsoft.com/default.aspx?kbid=283115
' --------------------------------------------------

On Error GoTo fncNextNumber_ERR

Dim dbNum As DAO.Database
Dim rsNum As DAO.Recordset
Dim NextNumber As Long
Dim strSQL As String

'Construct sql statement
strSQL = "SELECT " & strFieldName _
& " FROM " & strTableName

Set dbNum = CurrentDb()
Set rsNum = dbNum.OpenRecordset(strSQL, dbOpenDynaset)
With rsNum
If .RecordCount Then 'If record exists
'Retrieve "next number" value
NextNumber = Nz(.Fields(0), 1)
'Increment "next number" value in table
.Edit
.Fields(0) = NextNumber + 1
.Update
Else 'First time table has been accessed
'Start with 1
NextNumber = 1
'Generate new record and assign
'"next number" value
.AddNew
.Fields(0) = 2
.Update
End If
End With

'Assign function's return value
fncNextNumber = NextNumber

fncNextNumber_EXIT:
On Error Resume Next
rsNum.Close
Set rsNum = Nothing
Set dbNum = Nothing
Exit Function

fncNextNumber_ERR:
MsgBox "Error " & Err.Number & _
" occurred in fncNextNumber: " & Err.Description
Resume fncNextNumber_EXIT

End Function
'**********EXAMPLE END

Usage instructions are contained within the function header, as is information
regarding the need for a reference to the DAO Object Library and how to
determine which version of the object library you need for your version of
Access.
 
use transaction to save data rather than binding the form
directly to table:
especially if you are using autonumber field as PK in
multiuser environment.

Create a unbound form and have transaction to save data
when user clicks
save button or on exit.
have something like this:

private sub command1_click ()
on error goto dberror
wrkjet.begintrans
set rs = db.openrecordset ("tbl1", dbopendynaset, 0,
dboptimistic)
with rs
.addnew
!id = getkry () *** if not autonumber,
get PK
!name = me!text1
!ph = me!text2
....
.update
.close
end with
wrkjet.commit
exit sub
dberror:
select case err.number:
case 3042
msgbox ("Error......")
wrkjet.rollback
case 8889
.....
end select
exit sub
end sub

public function getkey () as long
sqlstr = "select max (id) from tbl;"
set rstmp = db.openrecordset ("sqlstr",
dbopendynaset, dbreadonly)
if rstmp.recordcount > 0 then
rstmp.movefirst
getkey = rstmp(0) + 1 *** or some
other scheme to
increment PK
else
getkey = 1
end if
rstmp.close
end function
 
Back
Top