Can't prevent updates and allow new records on form/subform/subfor

[AccessMan]

I have a form with a subform that also contains a subform. The Data Entry
property for each is set to No. I would like to prevent users from updating
records on each form, but I would also like to allow them to create new
records on each form. To achieve this, I set the Allow Edits property to No
on each form, while retaining the Yes setting for Allow Additions and Allow
Deletions. The result is that I can create new records on the main form, but
not on either subform. Am I stuck with this, or am I doing something wrong?

Thanks!!!

 

[Allen Browne]

Yes, that is how Access works.

If you set AllowEdits to No, you can't edit in any of the controls on the
main form. The subform control is a form on the main form, and so you cannot
edit in that control. Hence you can't use the subform.

To work around that, leave the form's AllowEdits property set to Yes, and
lock the controls instead. Here's the code to loop through controls and
lock/unlock them, including any subforms (nested to any level):
Locking bound controls on a form and subforms
at:
http://allenbrowne.com/ser-56.html

 

[AccessMan]

Thanks. Unless I misunderstood though, locking the controls prevents the
user from creating new records, doesn't it?

 

[Allen Browne]

The article contains the code for locking and unlocking the form.

If you call the code in the form's Current event, you can use it to lock
existing records, and unlock new records:

Private Sub Form_Current()
Call LockBoundControls(Me, Not Me.NewRecord)
End Sub

 

[AccessMan]

I think I see what you are saying now. Would a lot of modification be
required to automatically lock bound controls (or the entire form/subforms)
for existing records, and automatically unlock for new records?

 

[Allen Browne]

Probably no changes at all.

Copy'n'paste the code.

Use the form's Current event instead of the Load event /button click as
suggested in the article.

 

[AccessMan]

I'm slowly building my comfort level with VBA. Would a more direct solution
be to set Allow Edits to No for the form and subforms by default, and reset
this property to Yes for all if the user clicks any of the New Record
buttons? I would also need to reset the properties back to No after the new
record is saved.

 

[Allen Browne]

You can try that.

My experience is that AllowEdits is way to restrictive in practice. For
example, if you use an unbound combo for navigating to a record, or unbound
controls so the user can filter the form, there are all useless if
AllowEdits is set to Yes.