Locked Record Indicator

DebbieG

I have created a database for a client and was told that it was to be a
one-user database. Well, you know the next statement ... now they want 3
people to be able to use the database. (FYI, I have never created a
database for multiusers. I've done some searching but not finding what I
want.)

I have split the database. In Tools, Options, I have set the following:

Default open mode = Shared
Default record locking = Edited Record
Checked Open databases using record-level locking

And this is some of the code behind one of my forms:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordLocks <> 2 Then
Me.RecordLocks = 2 'lock at the record level
End If
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End Sub

I reset the above in Form_Current.

In testing to see how my form will react, I opened the database twice and
tried to edit the same record. I get the Locked Record Indicator but
Form_Dirty is activated if they attempt to make a change, so the Save and
Undo buttons appear, but the user can't change anything.

Is there a way to capture the Locked Record Indicator? Or what can I do so
the Form_Dirty doesn't activate? I thought it would be a nice feature to
use a msgbox to tell the 2nd user that someone else is editing the same
record.

I will have a lot of forms to change so the easiest way I can do this the
better.

Thanks in advance for any suggestions/help,
Debbie

Nov 12 '05 #1

Tom van Stiphout

On Fri, 7 May 2004 00:56:21 -0500, "DebbieG"
<debbieg@accessus-REMOVE-THIS-.net> wrote:

Optimistic locking (.RecordLocks=0) typically is sufficient. This
means multiple users can edit the same record at the same time, and if
they save, the one that was a millisecond quicker to hit the button
saves first, immediately being overwritten by the changes made by the
other user. Typically that is fine.

I don't think there is a way to do what you asked - capture the Locked
Record indicator. However, you can train your users on observing it.

-Tom.

I have created a database for a client and was told that it was to be a
one-user database. Well, you know the next statement ... now they want 3
people to be able to use the database. (FYI, I have never created a
database for multiusers. I've done some searching but not finding what I
want.)

I have split the database. In Tools, Options, I have set the following:

Default open mode = Shared
Default record locking = Edited Record
Checked Open databases using record-level locking

And this is some of the code behind one of my forms:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordLocks <> 2 Then
Me.RecordLocks = 2 'lock at the record level
End If
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End Sub

I reset the above in Form_Current.

In testing to see how my form will react, I opened the database twice and
tried to edit the same record. I get the Locked Record Indicator but
Form_Dirty is activated if they attempt to make a change, so the Save and
Undo buttons appear, but the user can't change anything.

Is there a way to capture the Locked Record Indicator? Or what can I do so
the Form_Dirty doesn't activate? I thought it would be a nice feature to
use a msgbox to tell the 2nd user that someone else is editing the same
record.

I will have a lot of forms to change so the easiest way I can do this the
better.

Thanks in advance for any suggestions/help,
Debbie

Nov 12 '05 #2

DebbieG

Tom,

Thank you for your response. I do not want users to be able to edit the
same record at the same time so I have to keep it the way it is. However,
when I try to edit a locked record, it does beep and doesn't allow editing.
Isn't there a way to capture this somehow? Access knows it is locked, so
why does it think the record is dirty when they attempt to edit? What I
really want is for the Save and Undo button not to appear. That is their
clue that they can do something.

Thanks,
Debbie
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:vo********************************@4ax.com...
On Fri, 7 May 2004 00:56:21 -0500, "DebbieG"
<debbieg@accessus-REMOVE-THIS-.net> wrote:

Optimistic locking (.RecordLocks=0) typically is sufficient. This
means multiple users can edit the same record at the same time, and if
they save, the one that was a millisecond quicker to hit the button
saves first, immediately being overwritten by the changes made by the
other user. Typically that is fine.

I don't think there is a way to do what you asked - capture the Locked
Record indicator. However, you can train your users on observing it.

-Tom.

I have created a database for a client and was told that it was to be a
one-user database. Well, you know the next statement ... now they want 3
people to be able to use the database. (FYI, I have never created a
database for multiusers. I've done some searching but not finding what I
want.)

I have split the database. In Tools, Options, I have set the following:

Default open mode = Shared
Default record locking = Edited Record
Checked Open databases using record-level locking

And this is some of the code behind one of my forms:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordLocks <> 2 Then
Me.RecordLocks = 2 'lock at the record level
End If
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End Sub

I reset the above in Form_Current.

In testing to see how my form will react, I opened the database twice and
tried to edit the same record. I get the Locked Record Indicator but
Form_Dirty is activated if they attempt to make a change, so the Save and
Undo buttons appear, but the user can't change anything.

Is there a way to capture the Locked Record Indicator? Or what can I do so
the Form_Dirty doesn't activate? I thought it would be a nice feature to
use a msgbox to tell the 2nd user that someone else is editing the same
record.

I will have a lot of forms to change so the easiest way I can do this the
better.

Thanks in advance for any suggestions/help,
Debbie

Nov 12 '05 #3

Tom van Stiphout

On Fri, 7 May 2004 09:52:40 -0500, "DebbieG"
<debbieg@accessus-REMOVE-THIS-.net> wrote:

I know Access knows. But it is not exposing that to the developer,
afaik.
I do agree with you that it's strange that the Dirty event fires for
the second user. I noticed that too when I ran a test. This seems
contra to what the help file says about the Dirty event. Perhaps we
can resort to some trickery to detect we can't edit, and then not
enable the buttons:
Private Sub Form_Dirty(Cancel As Integer)
If Me.Dirty Then
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End If
End Sub

-Tom.

Tom,

Thank you for your response. I do not want users to be able to edit the
same record at the same time so I have to keep it the way it is. However,
when I try to edit a locked record, it does beep and doesn't allow editing.
Isn't there a way to capture this somehow? Access knows it is locked, so
why does it think the record is dirty when they attempt to edit? What I
really want is for the Save and Undo button not to appear. That is their
clue that they can do something.

Thanks,
Debbie
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:vo********************************@4ax.com.. .
On Fri, 7 May 2004 00:56:21 -0500, "DebbieG"
<debbieg@accessus-REMOVE-THIS-.net> wrote:

Optimistic locking (.RecordLocks=0) typically is sufficient. This
means multiple users can edit the same record at the same time, and if
they save, the one that was a millisecond quicker to hit the button
saves first, immediately being overwritten by the changes made by the
other user. Typically that is fine.

I don't think there is a way to do what you asked - capture the Locked
Record indicator. However, you can train your users on observing it.

-Tom.

I have created a database for a client and was told that it was to be a
one-user database. Well, you know the next statement ... now they want 3
people to be able to use the database. (FYI, I have never created a
database for multiusers. I've done some searching but not finding what I
want.)

I have split the database. In Tools, Options, I have set the following:

Default open mode = Shared
Default record locking = Edited Record
Checked Open databases using record-level locking

And this is some of the code behind one of my forms:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordLocks <> 2 Then
Me.RecordLocks = 2 'lock at the record level
End If
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End Sub

I reset the above in Form_Current.

In testing to see how my form will react, I opened the database twice and
tried to edit the same record. I get the Locked Record Indicator but
Form_Dirty is activated if they attempt to make a change, so the Save and
Undo buttons appear, but the user can't change anything.

Is there a way to capture the Locked Record Indicator? Or what can I do so
the Form_Dirty doesn't activate? I thought it would be a nice feature to
use a msgbox to tell the 2nd user that someone else is editing the same
record.

I will have a lot of forms to change so the easiest way I can do this the
better.

Thanks in advance for any suggestions/help,
Debbie

Nov 12 '05 #4

DebbieG

THAT DID IT! You're wonderful!

Thanks,
Debbie

"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:vv********************************@4ax.com...
On Fri, 7 May 2004 09:52:40 -0500, "DebbieG"
<debbieg@accessus-REMOVE-THIS-.net> wrote:

I know Access knows. But it is not exposing that to the developer,
afaik.
I do agree with you that it's strange that the Dirty event fires for
the second user. I noticed that too when I ran a test. This seems
contra to what the help file says about the Dirty event. Perhaps we
can resort to some trickery to detect we can't edit, and then not
enable the buttons:
Private Sub Form_Dirty(Cancel As Integer)
If Me.Dirty Then
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End If
End Sub

-Tom.

Tom,

Thank you for your response. I do not want users to be able to edit the
same record at the same time so I have to keep it the way it is. However,
when I try to edit a locked record, it does beep and doesn't allow editing.
Isn't there a way to capture this somehow? Access knows it is locked, so
why does it think the record is dirty when they attempt to edit? What I
really want is for the Save and Undo button not to appear. That is their
clue that they can do something.

Thanks,
Debbie
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:vo********************************@4ax.com.. .
On Fri, 7 May 2004 00:56:21 -0500, "DebbieG"
<debbieg@accessus-REMOVE-THIS-.net> wrote:

Optimistic locking (.RecordLocks=0) typically is sufficient. This
means multiple users can edit the same record at the same time, and if
they save, the one that was a millisecond quicker to hit the button
saves first, immediately being overwritten by the changes made by the
other user. Typically that is fine.

I don't think there is a way to do what you asked - capture the Locked
Record indicator. However, you can train your users on observing it.

-Tom.

I have created a database for a client and was told that it was to be a
one-user database. Well, you know the next statement ... now they want 3
people to be able to use the database. (FYI, I have never created a
database for multiusers. I've done some searching but not finding what I
want.)

I have split the database. In Tools, Options, I have set the following:

Default open mode = Shared
Default record locking = Edited Record
Checked Open databases using record-level locking

And this is some of the code behind one of my forms:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordLocks <> 2 Then
Me.RecordLocks = 2 'lock at the record level
End If
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End Sub

I reset the above in Form_Current.

In testing to see how my form will react, I opened the database twice and
tried to edit the same record. I get the Locked Record Indicator but
Form_Dirty is activated if they attempt to make a change, so the Save and
Undo buttons appear, but the user can't change anything.

Is there a way to capture the Locked Record Indicator? Or what can I do sothe Form_Dirty doesn't activate? I thought it would be a nice feature to
use a msgbox to tell the 2nd user that someone else is editing the same
record.

I will have a lot of forms to change so the easiest way I can do this the
better.

Thanks in advance for any suggestions/help,
Debbie

Nov 12 '05 #5

DebbieG

Tome,

I celebrated a little too soon. This works on the 2nd user but now it
doesn't fire on the 1st user. I tried moving this code to OnCurrent but
that didn't work either. Did it work on your test?

Debbie
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:vv********************************@4ax.com...
On Fri, 7 May 2004 09:52:40 -0500, "DebbieG"
<debbieg@accessus-REMOVE-THIS-.net> wrote:

I know Access knows. But it is not exposing that to the developer,
afaik.
I do agree with you that it's strange that the Dirty event fires for
the second user. I noticed that too when I ran a test. This seems
contra to what the help file says about the Dirty event. Perhaps we
can resort to some trickery to detect we can't edit, and then not
enable the buttons:
Private Sub Form_Dirty(Cancel As Integer)
If Me.Dirty Then
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End If
End Sub

-Tom.

Tom,

Thank you for your response. I do not want users to be able to edit the
same record at the same time so I have to keep it the way it is. However,
when I try to edit a locked record, it does beep and doesn't allow editing.
Isn't there a way to capture this somehow? Access knows it is locked, so
why does it think the record is dirty when they attempt to edit? What I
really want is for the Save and Undo button not to appear. That is their
clue that they can do something.

Thanks,
Debbie
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:vo********************************@4ax.com.. .
On Fri, 7 May 2004 00:56:21 -0500, "DebbieG"
<debbieg@accessus-REMOVE-THIS-.net> wrote:

Optimistic locking (.RecordLocks=0) typically is sufficient. This
means multiple users can edit the same record at the same time, and if
they save, the one that was a millisecond quicker to hit the button
saves first, immediately being overwritten by the changes made by the
other user. Typically that is fine.

I don't think there is a way to do what you asked - capture the Locked
Record indicator. However, you can train your users on observing it.

-Tom.

I have created a database for a client and was told that it was to be a
one-user database. Well, you know the next statement ... now they want 3
people to be able to use the database. (FYI, I have never created a
database for multiusers. I've done some searching but not finding what I
want.)

I have split the database. In Tools, Options, I have set the following:

Default open mode = Shared
Default record locking = Edited Record
Checked Open databases using record-level locking

And this is some of the code behind one of my forms:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordLocks <> 2 Then
Me.RecordLocks = 2 'lock at the record level
End If
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End Sub

I reset the above in Form_Current.

In testing to see how my form will react, I opened the database twice and
tried to edit the same record. I get the Locked Record Indicator but
Form_Dirty is activated if they attempt to make a change, so the Save and
Undo buttons appear, but the user can't change anything.

Is there a way to capture the Locked Record Indicator? Or what can I do sothe Form_Dirty doesn't activate? I thought it would be a nice feature to
use a msgbox to tell the 2nd user that someone else is editing the same
record.

I will have a lot of forms to change so the easiest way I can do this the
better.

Thanks in advance for any suggestions/help,
Debbie

Nov 12 '05 #6

DebbieG

I finally found what I needed. I found it on the Microsoft Knowledge Base
(article 122294 - How to automatically detect if a form is being edited).
There were two methods suggested and I chose to use the Dirty Property in an
Expression. In summary, here's what worked:

I created the following function in the module:

Function EditModeChange (F as Form) as Variant

If F.Dirty then

F!cmdSave.Visible = True

F!cmdUndo.Visible = True

F!cmdAdd.Visible = False

F!ComboStudent.Enabled = False

F!cmdDelete.Visible = False

else

F!cmdSave.Visible = False

F!cmdUndo.Visible = False

F!cmdAdd.Visible = True

F!ComboStudent.Enabled = True

F!cmdDelete.Visible = True

End If

End Function

I added a new textbox to my form:

Name: txtEditModeChange

Control Source: =[Form].[Dirty] & EditModeChange([Form])

Visible: No

In Form_AfterUpdate:

Sub Form_AfterUpdate()

Me!txtEditModeChange.Requery

End Sub

Now, if a second user tries to edit the same record as someone else the Save
and Undo buttons don't appear.

Hope this helps someone else.

Debbie

"DebbieG" <debbieg@accessus-REMOVE-THIS-.net> wrote in message
news:j5********************@accessus.net...
I have created a database for a client and was told that it was to be a
one-user database. Well, you know the next statement ... now they want 3
people to be able to use the database. (FYI, I have never created a
database for multiusers. I've done some searching but not finding what I
want.)

I have split the database. In Tools, Options, I have set the following:

Default open mode = Shared
Default record locking = Edited Record
Checked Open databases using record-level locking

And this is some of the code behind one of my forms:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordLocks <> 2 Then
Me.RecordLocks = 2 'lock at the record level
End If
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Me.cmdSave.Visible = True
Me.cmdUndo.Visible = True
Me.cmdAdd.Visible = False
Me.ComboStudent.Enabled = False
Me.cmdDelete.Visible = False
End Sub

I reset the above in Form_Current.

In testing to see how my form will react, I opened the database twice and
tried to edit the same record. I get the Locked Record Indicator but
Form_Dirty is activated if they attempt to make a change, so the Save and
Undo buttons appear, but the user can't change anything.

Is there a way to capture the Locked Record Indicator? Or what can I do so
the Form_Dirty doesn't activate? I thought it would be a nice feature to
use a msgbox to tell the 2nd user that someone else is editing the same
record.

I will have a lot of forms to change so the easiest way I can do this the
better.

Thanks in advance for any suggestions/help,
Debbie

Nov 12 '05 #7