Lock specific record fields

  • Thread starter Thread starter Samantha Rawson via AccessMonster.com
  • Start date Start date
S

Samantha Rawson via AccessMonster.com

Is there a way to lock the fields in a record, for example
if status = true then....
I've tried me.allowedits = false, which people recommended, but this doesn't seem to work.
 
I found a non-VBA solution: Went to Tools>Options>Advanced then locked edited records. Perfect!
 
Hi Samantha,

Do you want to lock when Status=true? (Wasn't sure from your post).

There are a variety of ways to do this in a form - each one using the
Current Event of the form to check a status field or whatever field you have
that indicates whether the record is to be locked. You also need to execute
this code in the AfterUpdate event of the form to ensure that the record is
locked after the status changes. I've shown this code as event code but in
reality I would put it in separate procedures within the form's class module
and then call that procedure form both the Current Event and the AfterUpdate
event.

As you have probably already discovered, the easiest way (with drawbacks) is
to toggle the AllowEdits property of the form. In the following, the record
will be 'locked' when the Status=True. The drawback to using the AllowEdits
property of the form is that it also locks unbound controls when it is
false. This means that an unbound control used for locating a record is
locked when the rest of the form is locked. Not ideal in many cases.

Private Sub Form_Current()
Me.AllowEdits = Not Me.Status
End Sub

Note that the above is a shortcut for the following

Private Sub Form_Current()
if Me.Status then
me.allowedits=false
else
me.allowedits=true
end if
End Sub

The other way is to toggle the Locked property of individual controls based
on the value of Status. Put some keyword in the Tag property of each
control that should be conditionally locked then loop through all the
controls and toggle the locked property of the relevant controls. For
example - using the keyword "ToggleLock"

Private Sub Form_Current()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "ToggleLock" Then
ctl.Locked = Me.Status
End If
Next ctl
Set ctl = Nothing
End Sub
 
One other thing to consider - "Status" is used as a property name for
several type of objects. I generally try to avoid using any name which might
conflict with a built-in name, property or keyword. As a general rule, most
common names can easily be adapted by adding a prefix which tells "what kind
of" thing it is. So if this is an order status, then "OrderStatus" becomes
the name. I *always* do this with 'name', 'date', 'status', 'id' (and many
other) type of fields. It's a bit more trouble on the front-end but saves
costly time in debugging the odd errors that can occur *plus* it makes your
code more readable. I know from experience that the code I wrote 3 years ago
doesn't make nearly as much sense today unless I consistantly used
descriptive names.
 
Samantha said:
Is there a way to lock the fields in a record, for example
if status = true then....
I've tried me.allowedits = false, which people recommended, but this doesn't seem to work.
Click to expand...


What do you mean by "doesn't seem to work"?

Generally, the AllowEdits property will do this, but it can
not take effect if the record has already been changed
(dirtied). If that's the issue, then save the record before
setting AllowEdits:

If Me.Dirty Then Me.Dirty = False
Me.AllowEdits = False

You will probably also want to set the AllowDeletions
property to False.
 
I have these fields:
OrderId
Date1
CustId
NoOrdered
Status

I need it so that once a value is entered into the first 4 fields, it
cannot be changed. I need to still be able to edit Status though.
 
Samantha said:
I have these fields:
OrderId
Date1
CustId
NoOrdered
Status

I need it so that once a value is entered into the first 4 fields, it
cannot be changed. I need to still be able to edit Status though.
Click to expand...


Did you try my suggestion?

If you did, what happened?
 
Back
Top