473,224 Members | 1,597 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Locked Record Indicator

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
6 5584
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
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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: David C. Barber | last post by:
I'm trying to determine which user has locked a given record from VB6. I know I can use sp_lock and sp_who, and match up the data to determine which users have locked records in my database,...
2
by: Anastassios Giannaras | last post by:
Hello I have made an small access Database that is used by 4-5 people every day. Very often through the users get the message "Could Not Update; Currently Locked" There is an entry about that...
3
by: BillB | last post by:
When I activate frmList.List0, it positions at the end of the table: - frmList Private Sub Form_Activate() Me.List0.SetFocus Me.List0.ListIndex = Me.List0.ListCount - 1 - When I DblClick a...
0
by: ethanj /personal/ | last post by:
We are using Access 2000 database, with the following settings. Tool > Options > Advance > Default Record Locking = Edited Record Open databases using record level locking = True Form...
1
by: Rolan | last post by:
Having tried various permutations of Before Update and well for that matter, After Update, OnExit, OnEnter, etc. and also Locked controls, I'm still unable to obtain the intended results. There are...
1
by: andrew.newell | last post by:
Hi, I use MS Access to connect to and SQL Database this has been working fine until recently. Whenever I add a new record (post record 723) it does not allow me to change it. My DBA has...
0
by: Access Programming only with macros, no code | last post by:
ERROR MESSAGE: Could not update; currently locked by another session on this machine. BACKGROUND I have the following objects: Table1 - HO (which has about 51,000+ records) Table2 -...
6
by: ljstern | last post by:
Hello. I am using MS Access 2003 with XP/pro. I have a form that has the following properties set through the property sheet: form: AllowAdditions = False CartridgeID field: Locked = True ...
5
by: breal | last post by:
I have a db table that holds a list of ports. There is a column in_use that is used as a flag for whether the port is currently in use. When choosing a port the table is read and the first...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.