MacroScope
Programmer
I have a DB in a small insurance company (about 14 users, all in the same building/room), and it's not unusual for a user to try to access a record already open by someone else. There are many instructions that run OnCurrent of the forms involved, and there's always an error (Can't set value) as those instructions try to run against a locked record. I spent considerable time searching forums trying to find a solution that would identify which user was in the record and stop the errors before they started, but I never found anything that answered the question.
I ultimately solved the problem myself, and I'm writing now not to ask a question but to share a solution.
The first thing I needed was a way of identifying a user. This code works perfectly.
<code>
Function UserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
UserName = Left$(strUserName, lngLen - 1)
Else
UserName = vbNullString
End If
End Function
</code>
I have 4 forms that are constantly in use by all the users. I started by creating 4 tables, one for each form, each with two fields labeled UserName and ID. These tables are where the current user information will be stored.
I then harnessed the OnOpen, OnCurrent, and OnClose events of the form in the following manner.
OnOpen: Each station verifies that the user's name is not saved in the table of open users, and then adds that user name to a new record of the table. If the user is there for some reason, the same name is not set a second time. Since OnOpen always takes me to a new record, no ID is set, and if the UserName previously existed the ID field is set to Null.
OnCurrent: The very first instruction that runs on current looks at the ID number of the record to be viewed. It then does a DLookup on the ID field to see if that record is already noted in the table, and therefore in use. If there is no match, then moving to the record is unimpeded, and the ID number of the record is set in the ID field of the table. Username xxx is in the table along with record 123.
When another user moves to record 123, their OnCurrent does a DLookup for the record ID, and finds a match. It pops a message on the screen that says, "this record is in use by " and then there's a DLookup of the UserName, and then stops all further action on the record, precluding errors.
OnClose: As a user closes a form, the last thing that happens is that there's a DLookup to locate the UserName(), and it selects and then deletes that record, so the user is removed from the table of current users.
Although it was tricky to integrate seamlessly with everything else going on, ultimately it proved to work perfectly. I can open the table and see all of the users that have a given form open, and the ID number of the record they're viewing.
I hope this information helps someone else. I found many instances online of people asking how to do this, but found no definitive solutions. I can tell you that it might be a bit clunky but it works exactly as I intended it to.
I ultimately solved the problem myself, and I'm writing now not to ask a question but to share a solution.
The first thing I needed was a way of identifying a user. This code works perfectly.
<code>
Function UserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
UserName = Left$(strUserName, lngLen - 1)
Else
UserName = vbNullString
End If
End Function
</code>
I have 4 forms that are constantly in use by all the users. I started by creating 4 tables, one for each form, each with two fields labeled UserName and ID. These tables are where the current user information will be stored.
I then harnessed the OnOpen, OnCurrent, and OnClose events of the form in the following manner.
OnOpen: Each station verifies that the user's name is not saved in the table of open users, and then adds that user name to a new record of the table. If the user is there for some reason, the same name is not set a second time. Since OnOpen always takes me to a new record, no ID is set, and if the UserName previously existed the ID field is set to Null.
OnCurrent: The very first instruction that runs on current looks at the ID number of the record to be viewed. It then does a DLookup on the ID field to see if that record is already noted in the table, and therefore in use. If there is no match, then moving to the record is unimpeded, and the ID number of the record is set in the ID field of the table. Username xxx is in the table along with record 123.
When another user moves to record 123, their OnCurrent does a DLookup for the record ID, and finds a match. It pops a message on the screen that says, "this record is in use by " and then there's a DLookup of the UserName, and then stops all further action on the record, precluding errors.
OnClose: As a user closes a form, the last thing that happens is that there's a DLookup to locate the UserName(), and it selects and then deletes that record, so the user is removed from the table of current users.
Although it was tricky to integrate seamlessly with everything else going on, ultimately it proved to work perfectly. I can open the table and see all of the users that have a given form open, and the ID number of the record they're viewing.
I hope this information helps someone else. I found many instances online of people asking how to do this, but found no definitive solutions. I can tell you that it might be a bit clunky but it works exactly as I intended it to.
