Protect a record and/or table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a split database which is used by a number of front-end users. The
database is used to hold bibliographic information which is being reviewed.
Is there a(n easy) way to protect a single record from being modified once
the review process is completed? (And how would you turn this off if a later
adjustment needed to be made?)

If this is not possible, can I protect an entire table?

I am new to Access and know very little about code, so if you do have an
answer to my questions, small step-by-step detailed instructions would be
most appreciated!

Thanks in advance!
Kylie
 
Presumably you have a form where users access this data. You can set the
AllowEdits and AllowDeletions property of the form so users cannot modify or
delete the record once the process is complete.

This example assumes you have a yes/no field named IsComplete. Once that
check box is checked, the record is locked.

1. Open the form in design view.

2. Open the Properties box (View menu.)

3. Make sure the Title of the Properties box reads, "Form", so you are
looking at the properties of the form, not those of a control.

4. On the Data tab of the Properties box, set the On Current property to:
[Event Procedure]

5. Click the Build button (...) beside this.
Access opens a code window.

6. Set up the code like this:

Private Sub Form_Current()
Dim bAllowEdit As Boolean
bAllowEdit = Nz(Me.IsComplete, False)
If Me.AllowEdits <> bAllowEdit Then
Me.AllowEdits = bAllowEdit
Me.AllowDeletions = bAllowEdit
End If
End Sub

Now you also want to provide a command button that allows the user to
override and edit anyway. Set the On Click property of this button to [Event
Procedure]. Click the Build button, and put this code between the "Private
Sub..." and "End Sub" lines:
Me.AllowEdits = True
Me.AllowDeletions = True

Setting AllowEdits to No means that even if you have some unbound controls
on the form, they are not editable. If that is undesirable, or if you have
subforms you want to lock also, or fields that you don't want to lock,
there's a more powerful solution in this article:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html
 
Thanks Allen. I will give this a go. Thanks for your detailed instructions
too!

Allen Browne said:
Presumably you have a form where users access this data. You can set the
AllowEdits and AllowDeletions property of the form so users cannot modify or
delete the record once the process is complete.

This example assumes you have a yes/no field named IsComplete. Once that
check box is checked, the record is locked.

1. Open the form in design view.

2. Open the Properties box (View menu.)

3. Make sure the Title of the Properties box reads, "Form", so you are
looking at the properties of the form, not those of a control.

4. On the Data tab of the Properties box, set the On Current property to:
[Event Procedure]

5. Click the Build button (...) beside this.
Access opens a code window.

6. Set up the code like this:

Private Sub Form_Current()
Dim bAllowEdit As Boolean
bAllowEdit = Nz(Me.IsComplete, False)
If Me.AllowEdits <> bAllowEdit Then
Me.AllowEdits = bAllowEdit
Me.AllowDeletions = bAllowEdit
End If
End Sub

Now you also want to provide a command button that allows the user to
override and edit anyway. Set the On Click property of this button to [Event
Procedure]. Click the Build button, and put this code between the "Private
Sub..." and "End Sub" lines:
Me.AllowEdits = True
Me.AllowDeletions = True

Setting AllowEdits to No means that even if you have some unbound controls
on the form, they are not editable. If that is undesirable, or if you have
subforms you want to lock also, or fields that you don't want to lock,
there's a more powerful solution in this article:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kylie said:
I have a split database which is used by a number of front-end users. The
database is used to hold bibliographic information which is being
reviewed.
Is there a(n easy) way to protect a single record from being modified once
the review process is completed? (And how would you turn this off if a
later
adjustment needed to be made?)

If this is not possible, can I protect an entire table?

I am new to Access and know very little about code, so if you do have an
answer to my questions, small step-by-step detailed instructions would be
most appreciated!

Thanks in advance!
Kylie
Click to expand...
Click to expand...
 
Hi Allen

I have tried the code below (now that I am back at work after the Christmas
break). I am slowly getting there, but what I notice is that the record is
only locked when the box isn't ticked, ie: Is Complete is not ticked.
Obviously this could get quite confusing - is there a way to get it so that
it is locked when Is Complete is checked?

Thanks.

Kylie said:
Thanks Allen. I will give this a go. Thanks for your detailed instructions
too!

Allen Browne said:
Presumably you have a form where users access this data. You can set the
AllowEdits and AllowDeletions property of the form so users cannot modify or
delete the record once the process is complete.

This example assumes you have a yes/no field named IsComplete. Once that
check box is checked, the record is locked.

1. Open the form in design view.

2. Open the Properties box (View menu.)

3. Make sure the Title of the Properties box reads, "Form", so you are
looking at the properties of the form, not those of a control.

4. On the Data tab of the Properties box, set the On Current property to:
[Event Procedure]

5. Click the Build button (...) beside this.
Access opens a code window.

6. Set up the code like this:

Private Sub Form_Current()
Dim bAllowEdit As Boolean
bAllowEdit = Nz(Me.IsComplete, False)
If Me.AllowEdits <> bAllowEdit Then
Me.AllowEdits = bAllowEdit
Me.AllowDeletions = bAllowEdit
End If
End Sub

Now you also want to provide a command button that allows the user to
override and edit anyway. Set the On Click property of this button to [Event
Procedure]. Click the Build button, and put this code between the "Private
Sub..." and "End Sub" lines:
Me.AllowEdits = True
Me.AllowDeletions = True

Setting AllowEdits to No means that even if you have some unbound controls
on the form, they are not editable. If that is undesirable, or if you have
subforms you want to lock also, or fields that you don't want to lock,
there's a more powerful solution in this article:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kylie said:
I have a split database which is used by a number of front-end users. The
database is used to hold bibliographic information which is being
reviewed.
Is there a(n easy) way to protect a single record from being modified once
the review process is completed? (And how would you turn this off if a
later
adjustment needed to be made?)

If this is not possible, can I protect an entire table?

I am new to Access and know very little about code, so if you do have an
answer to my questions, small step-by-step detailed instructions would be
most appreciated!

Thanks in advance!
Kylie
Click to expand...
Click to expand...
Click to expand...
 
Also, another point, when I have the command button and code for allowing the
user to override and edit the record anyway, I am not able to click on this
once the record is locked. Is this correct?

Kylie said:
Thanks Allen. I will give this a go. Thanks for your detailed instructions
too!

Allen Browne said:
Presumably you have a form where users access this data. You can set the
AllowEdits and AllowDeletions property of the form so users cannot modify or
delete the record once the process is complete.

This example assumes you have a yes/no field named IsComplete. Once that
check box is checked, the record is locked.

1. Open the form in design view.

2. Open the Properties box (View menu.)

3. Make sure the Title of the Properties box reads, "Form", so you are
looking at the properties of the form, not those of a control.

4. On the Data tab of the Properties box, set the On Current property to:
[Event Procedure]

5. Click the Build button (...) beside this.
Access opens a code window.

6. Set up the code like this:

Private Sub Form_Current()
Dim bAllowEdit As Boolean
bAllowEdit = Nz(Me.IsComplete, False)
If Me.AllowEdits <> bAllowEdit Then
Me.AllowEdits = bAllowEdit
Me.AllowDeletions = bAllowEdit
End If
End Sub

Now you also want to provide a command button that allows the user to
override and edit anyway. Set the On Click property of this button to [Event
Procedure]. Click the Build button, and put this code between the "Private
Sub..." and "End Sub" lines:
Me.AllowEdits = True
Me.AllowDeletions = True

Setting AllowEdits to No means that even if you have some unbound controls
on the form, they are not editable. If that is undesirable, or if you have
subforms you want to lock also, or fields that you don't want to lock,
there's a more powerful solution in this article:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kylie said:
I have a split database which is used by a number of front-end users. The
database is used to hold bibliographic information which is being
reviewed.
Is there a(n easy) way to protect a single record from being modified once
the review process is completed? (And how would you turn this off if a
later
adjustment needed to be made?)

If this is not possible, can I protect an entire table?

I am new to Access and know very little about code, so if you do have an
answer to my questions, small step-by-step detailed instructions would be
most appreciated!

Thanks in advance!
Kylie
Click to expand...
Click to expand...
Click to expand...
 
To reverse the logic, change the line:
bAllowEdit = Nz(Me.IsComplete, False)
into:
bAllowEdit = Not Nz(Me.IsComplete, False)

To answer your other question, you should still be able to click the command
button even after the form is locked. If it's not working for you, you may
have used a toggle button instead of a command button?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kylie said:
Hi Allen

I have tried the code below (now that I am back at work after the
Christmas
break). I am slowly getting there, but what I notice is that the record
is
only locked when the box isn't ticked, ie: Is Complete is not ticked.
Obviously this could get quite confusing - is there a way to get it so
that
it is locked when Is Complete is checked?

Thanks.

Kylie said:
Thanks Allen. I will give this a go. Thanks for your detailed
instructions
too!

Allen Browne said:
Presumably you have a form where users access this data. You can set
the
AllowEdits and AllowDeletions property of the form so users cannot
modify or
delete the record once the process is complete.

This example assumes you have a yes/no field named IsComplete. Once
that
check box is checked, the record is locked.

1. Open the form in design view.

2. Open the Properties box (View menu.)

3. Make sure the Title of the Properties box reads, "Form", so you are
looking at the properties of the form, not those of a control.

4. On the Data tab of the Properties box, set the On Current property
to:
[Event Procedure]

5. Click the Build button (...) beside this.
Access opens a code window.

6. Set up the code like this:

Private Sub Form_Current()
Dim bAllowEdit As Boolean
bAllowEdit = Nz(Me.IsComplete, False)
If Me.AllowEdits <> bAllowEdit Then
Me.AllowEdits = bAllowEdit
Me.AllowDeletions = bAllowEdit
End If
End Sub

Now you also want to provide a command button that allows the user to
override and edit anyway. Set the On Click property of this button to
[Event
Procedure]. Click the Build button, and put this code between the
"Private
Sub..." and "End Sub" lines:
Me.AllowEdits = True
Me.AllowDeletions = True

Setting AllowEdits to No means that even if you have some unbound
controls
on the form, they are not editable. If that is undesirable, or if you
have
subforms you want to lock also, or fields that you don't want to lock,
there's a more powerful solution in this article:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I have a split database which is used by a number of front-end users.
The
database is used to hold bibliographic information which is being
reviewed.
Is there a(n easy) way to protect a single record from being modified
once
the review process is completed? (And how would you turn this off if
a
later
adjustment needed to be made?)

If this is not possible, can I protect an entire table?

I am new to Access and know very little about code, so if you do have
an
answer to my questions, small step-by-step detailed instructions
would be
most appreciated!

Thanks in advance!
Kylie
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen

Thank you for your help - all has worked successfully!

Thanks again,
Kylie

Allen Browne said:
To reverse the logic, change the line:
bAllowEdit = Nz(Me.IsComplete, False)
into:
bAllowEdit = Not Nz(Me.IsComplete, False)

To answer your other question, you should still be able to click the command
button even after the form is locked. If it's not working for you, you may
have used a toggle button instead of a command button?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kylie said:
Hi Allen

I have tried the code below (now that I am back at work after the
Christmas
break). I am slowly getting there, but what I notice is that the record
is
only locked when the box isn't ticked, ie: Is Complete is not ticked.
Obviously this could get quite confusing - is there a way to get it so
that
it is locked when Is Complete is checked?

Thanks.

Kylie said:
Thanks Allen. I will give this a go. Thanks for your detailed
instructions
too!

:

Presumably you have a form where users access this data. You can set
the
AllowEdits and AllowDeletions property of the form so users cannot
modify or
delete the record once the process is complete.

This example assumes you have a yes/no field named IsComplete. Once
that
check box is checked, the record is locked.

1. Open the form in design view.

2. Open the Properties box (View menu.)

3. Make sure the Title of the Properties box reads, "Form", so you are
looking at the properties of the form, not those of a control.

4. On the Data tab of the Properties box, set the On Current property
to:
[Event Procedure]

5. Click the Build button (...) beside this.
Access opens a code window.

6. Set up the code like this:

Private Sub Form_Current()
Dim bAllowEdit As Boolean
bAllowEdit = Nz(Me.IsComplete, False)
If Me.AllowEdits <> bAllowEdit Then
Me.AllowEdits = bAllowEdit
Me.AllowDeletions = bAllowEdit
End If
End Sub

Now you also want to provide a command button that allows the user to
override and edit anyway. Set the On Click property of this button to
[Event
Procedure]. Click the Build button, and put this code between the
"Private
Sub..." and "End Sub" lines:
Me.AllowEdits = True
Me.AllowDeletions = True

Setting AllowEdits to No means that even if you have some unbound
controls
on the form, they are not editable. If that is undesirable, or if you
have
subforms you want to lock also, or fields that you don't want to lock,
there's a more powerful solution in this article:
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I have a split database which is used by a number of front-end users.
The
database is used to hold bibliographic information which is being
reviewed.
Is there a(n easy) way to protect a single record from being modified
once
the review process is completed? (And how would you turn this off if
a
later
adjustment needed to be made?)

If this is not possible, can I protect an entire table?

I am new to Access and know very little about code, so if you do have
an
answer to my questions, small step-by-step detailed instructions
would be
most appreciated!

Thanks in advance!
Kylie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top