Required Field at Subform level

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gg - 04 Aug 2005 00:51 GMT

Continued from 8/2/05. I have a check box field in my Main form called
"PO_flag." On my subform, I have a field called "PO_no". If the PO_flag
field is checked (=True) the PO_no field on the subform must be required
(filled in). If not, the message will be MsgBox "You must enter a PO#." I
put the following code (thank you, Allen Browne) in the BeforeUpdate event of
the subform, not in the property section of the PO_no field (in subform):

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Parent!PO_flag.Value Then
If IsNull(Me.PO_no) Then
Cancel=True
MsgBox "PO# required."
End If
End If
End Sub

When I check the box for PO_flag, and the PO_no in the subform line items
are blank, the MsgBox message does not display. What am I doing incorrectly?

Thank you!

Reply to this Message

Allen Browne - 04 Aug 2005 03:08 GMT

In Access you have to create the main form record first, and then the
subform record. When you enter the subform record, the code therefore looks
back at the main form, to see if your PO_flag was set, and if so it
complains if the PO_no was left blank.

It sounds like you are now changing the PO_flag in the main form, after the
subform record already exists. If that happens, what are you wanting Access
do: to tell the user they cannot set this flag until they go into the
subform and enter a PO_no on all rows?

If that's what you want, you could use the BeforeUpdate event of the PO_flag
in the main form to cancel the change, force the user to undo the entry if
there are any related records at all where PO_no is null. To do that, use
DLookup() to look up the primary key of the subform's table where the
foreign key matches the primary key of the main form, and PO_no is null.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Continued from 8/2/05. I have a check box field in my Main form called
> "PO_flag." On my subform, I have a field called "PO_no". If the PO_flag
[quoted text clipped - 18 lines]
>
> Thank you!

Reply to this Message

gg - 04 Aug 2005 05:59 GMT

I got it to work! Thank you for all of your help, Allen.

> In Access you have to create the main form record first, and then the
> subform record. When you enter the subform record, the code therefore looks
[quoted text clipped - 34 lines]
> >
> > Thank you!

Reply to this Message