Real Chalange, Making Field on Subform Compulsory

[bocaboca]

Situation: Two tables. Relation One-To-Many. Form and one subform. Few fields on form. One field on subform (non linked one).

Goal: Prevent leaving entering the new record on form (consequently Record updating in table) if field on the subform IsNull=True.

Problem 1: Can't use BeforeUpdate Event for cheking value of field on subform and DoCmd.CancelEvent because it will make unpossible entering data in the subform field.

Problem 2: Dont want to use Public Variable (flag, CurrentRecord ...) because of multiuser environment and problems with Public Variable integrity.

Problem 3: I can make new Field in subform related table which will be filled on entering new record on the form. Consequently on entering new record in form, new record in other table will be created which will enable automated "validation" of "Required" field in the table (field on subform). In this scenario I dont know were to put error traping procedure in order to prevent poping out of Warnings and handling error.


Open to other solutions.

Thanks

 

[tunsarod]

Assuming the locking field on the subform is a check box then in the On Current event for the mainform type:


Private Sub Form_Current()

If MySubform!MyField = False Then
Me.Form.AllowEdits = True
Else
Me.Form.AllowEdits = False
End If
End Sub


Each time the mainform displays a different record the Subform's field will determine whether the mainform's Allow Edits property is set to True or False.

You can easily change the criteria to suit the lock field type or even it's properties e.g.


If MySubform!MyField.backcolor = 0 Then
Me.Form.AllowEdits = True
Else If MySubform!MyField.backcolor = 255 then
Me.Form.AllowEdits = False
End If


Regards
Rod