Make a record read only

Question

I have a form based on a table in which one of the fields that is a check box. I want to make the entire record read only if this box is checked (i.e. all fields are read-only for that record).

This is the VBA code I have so far

Private Sub Form_Current()

If Me.Sent=True Then 'where sent is the field name of the check box

Form.AllowEdits=False

Form.AllowDeletions=False

Form.AllowUpdates=False

End If

End Sub

This didn't work because the fields on the form cannot be updated even for records where the box is unchecked.

I would appreciate your help

Answer 1

Probably  the simplest way is to conditionally lock/disable the subform control by adding this to the code I gave you earlier:

    With Me.[YourSubFormControlNameGoesHere]

        .Enabled = Not Me.Sent Or Me.NewRecord

        .Locked = Me.Sent And Not Me.NewRecord

    End With

The subform control name is the name of the control in the parent form which 'houses' the subform.  It might or might not be the same as its source form object.

Answer 2

Thanks it worked. This form has a subform and I want to do the same thing for that, only that the check box is not on the subform. How would I accomplish that

Answer 3

First, you have to understand this can only be done on a form. So you need to make sure users can't see the tables.

Second, you have the right idea of sing the On Current event, but as Ken said you need to set the Allows to True if the box is not checked.

However, there is a third point. In some scenarios you may have unbound controls on your form that you may need to edit whether you can edit the data or not. In that case I suggest looking at Allen Browne's function Lock Bound controls.

Answer 4

You need to set it both ways.  The easiest way to do this is to reference the value of the Boolean column to which the check box is bound, rather than the Boolean constants TRUE or FALSE:

    Me.AllowEdits = Not Me.Sent

    Me.AllowDeletions = Not Me.Sent

There is no AllowUpdates property that I'm aware of.