How to prevent subform entry until parent record exists

[sjg1314]

Hello,

I have a form with a subform--let's call the records parent and child.

Most of the fields of both records are required. The first field the
user encounters in the subform is represented as a combobox.

If the first thing the user does is select an item from the combobox in
the subform, the error "The field "X.masterID" cannot contain a Null
value because the Required property is set to True. Enter a value in
this field."

What's happing, of course, is that masterID is the foreign key in the
child record that points to the master record--which has not been
created yet (since the user started entering data into the subform).

The error is too cryptic for my users. I can catch and replace the
error, of course. What I would like to do is to prevent the user from
entering data until the master record is created.

I tried disabling the subform, which took care of the early entry
problem. But now, the user can't tab into the subform until it is
enabled. And the subform can't be enabled until the master record is
saved, which used to happen when the user tabbed into the subform. I
could add a "Save" button or the user could use the navigation buttons
to cause the record to be saved. Both slow down record entry.

What is the usual way MS Access experts deal with this? Do people just
reword the error message and leave it at that? Or is there a better
way?

P.S. If the user enters at least one field in the master record, any
attempt to move into the subform generates a much clearer error message
that I created.

 

[Rick Brandt]

Hello,

I have a form with a subform--let's call the records parent and child.

Most of the fields of both records are required. The first field the
user encounters in the subform is represented as a combobox.

If the first thing the user does is select an item from the combobox
in the subform, the error "The field "X.masterID" cannot contain a
Null value because the Required property is set to True. Enter a
value in this field." [snip]

What I usually do is hide or disbale the subform and then use the
BeforeInsert event of the main form to show/enable it. Then it becomes
available as soon as they type the first character into the main form.

You also need to test the NewRecord property in the Current event of the
main form so you can then disable/hide the subform again.

 

[Guest]

You may also want to consider creating a second form that is just the parent
form. When the data in the parent form is completed the user presses a
command button that saves the parent form information and opens your current
form with the parent form fields locked. At that point they can still see
the information on the parent form and can enter appropriate information on
the child form.

A few more keystrokes, but no errors.

Jennifer

 

[Allen Browne]

You already have some good answers.

Another is to cancel the BeforeUpdate event of the subform if the parent
form is still at a new record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Please enter the main form record first."
End If
End Sub

 

[sjg1314]

First and foremost, a big thanks to all who replied. I really
appreciate your help.

With regards to Rick's reply, it seems like a very good solution.

For Jennifer: interesting trick, but I like to have the computer work
harder, not my users (even if it's just a keystroke or two).

As for Allen, I'm not sure whether you mean that I should set the
Before Update event handler for each control or for the subform. The
former is a bit of a pain (for the programmer) and prone to error (if a
control is added later) and the latter wouldn't prevent partial entry.
Of course, I may have misunderstood.

I wound up solving this myself, as often happens. I set a visual
indication that the subform is out-of-bounds by changing the background
color. On the main form, the After Update and On Current events set the
subform color based on whether the main form's record is a NewRecord.

The subform has a Dirty event handler. If the parent form is a
NewRecord, then I output an error message and then set the focus back
to a resonable field in the parent form. If we disregard the color
indicator, you can get away with just this--so it's not too far off
from Rick's idea in terms of complexity. It may also be a little nicer
to get an error message than to have fields that seem not to work.

Thanks again everyone.