Locking a Recordset in a form

[Paul M]

Hello, I have been having great luch with this project so far.... you guys
are great.

New question..

I have build from scratch a contact database using a sub form to keep the
record ID (in the main form) and several linked tables holding the
information from the main form. I have split the database and have several
users inputting data into this main form but I ran into a problem.

It seems that when one person is inputing into the main form, someone else
can come in using the same record ID and put thier own information in the
form erasing the previous entry without knowing about it, and also someone
can go back to a previous entry and completely change or remove that entry
all together.

How can I lock the data one someone has opened a new record so that it is
exclusive to that entry, and keep that record from being altered later unless
the Admin makes the changes?

 

[Marshall Barton]

Hello, I have been having great luch with this project so far.... you guys
are great.

New question..

I have build from scratch a contact database using a sub form to keep the
record ID (in the main form) and several linked tables holding the
information from the main form. I have split the database and have several
users inputting data into this main form but I ran into a problem.

It seems that when one person is inputing into the main form, someone else
can come in using the same record ID and put thier own information in the
form erasing the previous entry without knowing about it, and also someone
can go back to a previous entry and completely change or remove that entry
all together.

How can I lock the data one someone has opened a new record so that it is
exclusive to that entry, and keep that record from being altered later unless
the Admin makes the changes?

You can use the form's Current event to prevent edits to
existing records:

Me.AllowEdits = Me.NewRecord

 

[Paul M]

cool, where do I put it?

You can use the form's Current event to prevent edits to
existing records:

Me.AllowEdits = Me.NewRecord

 

[Marshall Barton]

In the Current event procedure.

 

[Paul M]

ok, I am not trying to be stupid about this but I am still pretty new at this
whole building from scratch thing..... in the current event procedure for
which part, the form or the table?

In the Current event procedure.
--
Marsh
MVP [MS Access]

cool, where do I put it?

 

[Marshall Barton]

Sorry I wasn't more explicit. There is no Current event for
anything but forms, so I thought that was clear enough.

Note that tables do not have events, so that was never a
consideration.

Do you need help creating the event procedure?

 

[Paul M]

yes, that would be great! =0P

Sorry I wasn't more explicit. There is no Current event for
anything but forms, so I thought that was clear enough.

Note that tables do not have events, so that was never a
consideration.

Do you need help creating the event procedure?
--
Marsh
MVP [MS Access]

ok, I am not trying to be stupid about this but I am still pretty new at this
whole building from scratch thing..... in the current event procedure for
which part, the form or the table?

 

[Marshall Barton]

Open the form in design view. Then click in the little box
at the upper left (at the intersection of the vertical and
horizontal rulers) to make sure it has a little black square
in it. Now use the View menu to display the Properties
window. Click on the Events tab and look down the list for
the On Current property (should be the first one). Click
anywhere in the On Current property, then select
[Event Procedure]
from the drop down list on the right side. Next click on
the ... builder button in the right margin, which will open
the form's module, create a skeletal procedure and place the
cursor between the Sub and End Sub lines. Type the line of
code I posted earlier.

To verify things, you should then use the Debug menu to
Compile your project.

Finally, use the Access window's toolbar to switch the form
to Form view test and see if it all works. If you need to
make further changes to the form's module, be certain that
you switch back to Design view before editing anything in
the module.

 

[Paul M]

This works great in locking the current entry, but it created another issue
(sorry for the problems)

Now if I am on entry 17 and someone trys to make a new after me while I am
inputing, mine will lock and I will not be able to input any further in that
slot, and also while I am on 17 someone else can open 17 at the same time.

how can I get my back end to skip the recordset that is opened by another
user and let both continue what they were doing?

Open the form in design view. Then click in the little box
at the upper left (at the intersection of the vertical and
horizontal rulers) to make sure it has a little black square
in it. Now use the View menu to display the Properties
window. Click on the Events tab and look down the list for
the On Current property (should be the first one). Click
anywhere in the On Current property, then select
[Event Procedure]
from the drop down list on the right side. Next click on
the ... builder button in the right margin, which will open
the form's module, create a skeletal procedure and place the
cursor between the Sub and End Sub lines. Type the line of
code I posted earlier.

To verify things, you should then use the Debug menu to
Compile your project.

Finally, use the Access window's toolbar to switch the form
to Form view test and see if it all works. If you need to
make further changes to the form's module, be certain that
you switch back to Design view before editing anything in
the module.
--
Marsh
MVP [MS Access]

yes, that would be great! =0P

 

[Marshall Barton]

Not sure what you are talking about, but try setting the
form's RecordLocks property to Edited Record.

Different users should be able to add new records
simultaneaously. If you can't do that, then I think you are
doing something to prevent it or maybe you have an unusual
situation that I don't understand.
--
Marsh
MVP [MS Access]

This works great in locking the current entry, but it created another issue
(sorry for the problems)

Now if I am on entry 17 and someone trys to make a new after me while I am
inputing, mine will lock and I will not be able to input any further in that
slot, and also while I am on 17 someone else can open 17 at the same time.

how can I get my back end to skip the recordset that is opened by another
user and let both continue what they were doing?

Open the form in design view. Then click in the little box
at the upper left (at the intersection of the vertical and
horizontal rulers) to make sure it has a little black square
in it. Now use the View menu to display the Properties
window. Click on the Events tab and look down the list for
the On Current property (should be the first one). Click
anywhere in the On Current property, then select
[Event Procedure]
from the drop down list on the right side. Next click on
the ... builder button in the right margin, which will open
the form's module, create a skeletal procedure and place the
cursor between the Sub and End Sub lines. Type the line of
code I posted earlier.

To verify things, you should then use the Debug menu to
Compile your project.

Finally, use the Access window's toolbar to switch the form
to Form view test and see if it all works. If you need to
make further changes to the form's module, be certain that
you switch back to Design view before editing anything in
the module.
--
Marsh
MVP [MS Access]

yes, that would be great! =0P

:

Sorry I wasn't more explicit. There is no Current event for
anything but forms, so I thought that was clear enough.

Note that tables do not have events, so that was never a
consideration.

Do you need help creating the event procedure?


Paul M wrote:
ok, I am not trying to be stupid about this but I am still pretty new at this
whole building from scratch thing..... in the current event procedure for
which part, the form or the table?

:

In the Current event procedure.


Paul M wrote:
cool, where do I put it?


:

Paul M wrote:

Hello, I have been having great luch with this project so far.... you guys
are great.

New question..

I have build from scratch a contact database using a sub form to keep the
record ID (in the main form) and several linked tables holding the
information from the main form. I have split the database and have several
users inputting data into this main form but I ran into a problem.

It seems that when one person is inputing into the main form, someone else
can come in using the same record ID and put thier own information in the
form erasing the previous entry without knowing about it, and also someone
can go back to a previous entry and completely change or remove that entry
all together.

How can I lock the data one someone has opened a new record so that it is
exclusive to that entry, and keep that record from being altered later unless
the Admin makes the changes?


You can use the form's Current event to prevent edits to
existing records:

Me.AllowEdits = Me.NewRecord