Repost:: Locking Records

  • Thread starter Thread starter David Ehrenreich
  • Start date Start date
D

David Ehrenreich

Sorry this is a repost. I posted the orginal in wrong
forum.

Is there a was to stop a user from opening a record if it
is already open elsewhere. I was thinking of some sort of
vb expression that would notify the user that the record
is locked try again later. I am familiar with default
record locking, but it did not work how I would like it.
This is how I have the db setup there is the backend db
file with just the tables then the frontend that are
seperate files that have the forms in them.


Thank you

David Ehrenreich
..
 
Hello David,

I don't know you experience,but I will tell you what I did to identify users
on a record. I only display them, but if a user is on a record, it can
easily be protected from multiple users. This is navigating from a form,
and doesn't apply to opening a table directly or from an alternate source.

I created a logging table which tracks the user, the computer the form name
and the recordID (autonumber)

I used an API from the Access Web, that Dev Ashish wrote to identify the
machine, and a local function to read the logged in user (this is a
proprietary security system I wrote) to track the User.

On the current event of the form, I create a record in the Log using DAO.

IT looks like this:
 
Hello David,

If this is a repeat I apologize. I hit a key and my window disappeared. I
don't know if I closed or posted it.

I don't know if this will help, but I just wrote a procedure to track the
uses on a record on a form. It can probably be modified to suit you
purpose, but it it would need to be implemented on each form that reads the
table and wouldn't protect a record from an open table, query or other
access.

I created a logging table to track the users, which includes the Form Name,
User Name, Computer name and RecordID. I would think for your purpose
recordID would suffice.

The API came from the Access Web and was written by Dev Ashish.

I place this code in the Current event of the Form: Some of it doesn't apply
to this, but I am thinking you can figure out what you need.

'****** Record User section
'Clear records in table [ScreenUserIdent] for Logged in Used for this
machine
Call ClearLoggedUsers
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("ScreenUserIdent", dbOpenDynaset,
dbSeeChanges)
If IsNull([ordID]) Or Me.RecordSource = "Order Entry Header Blank" Then
'So records aren't added when no user is logged on
'Exit Sub
GoTo exit_Section
End If

rst.FindFirst "[empLoginName] = '" & GetLoggedUser & "' And
[FormChildLink] = " & [ordID] & " And [MachineName] = '" & fOSMachineName &
"' And [ScreenName] = '" & Me.Name & "'"
If rst.NoMatch Then
rst.AddNew
rst![ScreenName] = Me.Name
rst![EmpLoginName] = GetLoggedUser()
rst![EmpLocation] = getUserLocation()
rst![FullName] = GetLoggedFullName
rst![MachineName] = fOSMachineName
rst![FormChildLink] = [ordID]
rst![TimeLog] = Time
rst.Update
End If


The function ClearLoggedUsers clears the log table, [ScreenUserIdent], the
current user from all records that he is logged into on the current form and
current machine: THis is done first then the new record is created.


Public Sub ClearLoggedUsers()
'On Error Resume Next

Dim strScreenName As Variant
strScreenName = Screen.ActiveForm.Name

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE [ScreenUserIdent].EmpLoginName,
[ScreenUserIdent].MachineName " & _
"FROM [ScreenUserIdent] " & _
"WHERE ((([ScreenUserIdent].EmpLoginName)=getLoggedUser()) AND
(([ScreenUserIdent].MachineName)=fosMachineName()) AND
(([ScreenUserIdent].ScreenName)= '" & Screen.ActiveForm.Name & "'));"

DoCmd.SetWarnings True

End Sub

ClearLoggedUsers is also in the Unload event of the form. This assures that
the user will be cleared whenever the form is formerly closed. If Access
bombs out the user will show as using that record, but that will be remedied
when he opens the form next. You can also provide a maintenence screen to
clear orphan users.

If you have an questions, post back on this thread.

I hope this helps.

God Bless,

Mark A. Sam
 
You may want to look up help on the EditMode property.
By reading the value of this property you should be able
to see whether the record is currently being edited or
not. This would give another approach, depending on what
you are trying to do.

-Ted
 
Back
Top