Data Entry Form Tries to Create a Record Too Soon

[Guest]

Hello.

BACKGROUND: I have created a form in Access2003 for data entry. This form
also contains 1 subform. There are 2 input fields on the form and 2 on the
subform and all of these fields feed a single table that requires all four
fields to contain valid data to create a single record.

PROBLEM: After I input data into the first two input fields located on the
form and try to navigate to the next field found on the subform (named
‘project’), I receive an error that reads, “The field ‘Time Card Hours
Table.Project’ cannot contain a Null value because the Required property for
this field is set to True. Enter a value in this field.â€

ANALYSIS: If I diagnose this error correctly, it appears to me Access is
trying to create a new record when I navigate from the form to the subform.
Yet, I’d need Access to not create a record until after the subform has its
two fields populated with inputs. Is this possible? Thank you in advance
for any assistance.

 

[Dirk Goldgar]

Hello.

BACKGROUND: I have created a form in Access2003 for data entry. This
form also contains 1 subform. There are 2 input fields on the form
and 2 on the subform and all of these fields feed a single table that
requires all four fields to contain valid data to create a single
record.

PROBLEM: After I input data into the first two input fields located
on the form and try to navigate to the next field found on the
subform (named 'project'), I receive an error that reads, "The field
'Time Card Hours Table.Project' cannot contain a Null value because
the Required property for this field is set to True. Enter a value in
this field."

ANALYSIS: If I diagnose this error correctly, it appears to me Access
is trying to create a new record when I navigate from the form to the
subform. Yet, I'd need Access to not create a record until after the
subform has its two fields populated with inputs. Is this possible?
Thank you in advance for any assistance.

Your analysis is good, but your design is wrong. You shouldn't have a
subform bound to the same table as the form. Since subforms are
generally bound to a table that is related to the main form's
recordsource, Access will always attempt to save the main form's record
when switching the focus to a subform, and will always attempt to save
the subform's record when switching the focus back to the main form.
There is nothing you can do to stop this from happening.

Why do you have a subform bound to the same table as the main form? If
you 'll explain your reason, we may be able to suggest a good
workaround.

 

[Guest]

Thank you Dirk for your help and insight.
As you can surmise from the table name, I am building a DB that tracks labor
hours per employee per project. On this table each record has just four
fields (outside of a unique key): an employee obtained from an employee
table, a project from a projects table, and a workweek from a third table;
the fourth field is for hours input. I have a form and subform because
currently users log their hours once a week. With design this form I want to
maintain that business process, so during that weekly input a user will
identify who they are and what work week it is one time. Then, on the subform
they enter project and hours combination as many times as necessary—creating
a new record with each project and hours entry.
So does my wrong form design illustrate flaws in my table design and its
relationships?
Again, many thanks. Ned

 

[Dirk Goldgar]

Thank you Dirk for your help and insight.
As you can surmise from the table name, I am building a DB that
tracks labor hours per employee per project. On this table each
record has just four fields (outside of a unique key): an employee
obtained from an employee table, a project from a projects table, and
a workweek from a third table; the fourth field is for hours input.
I have a form and subform because currently users log their hours
once a week. With design this form I want to maintain that business
process, so during that weekly input a user will identify who they
are and what work week it is one time. Then, on the subform they
enter project and hours combination as many times as
necessary-creating a new record with each project and hours entry.
So does my wrong form design illustrate flaws in my table design and
its relationships?

Your table design sounds fine, but your main form shouldn't be bound to
the same table as the subform. If I understand your description
correctly, it shouldn't be bound to any table. It would probably have
two unbound controls: one (probably a combo box) for selecting the
employee from the Employees table, and another (maybe a text box, maybe
a combo box) for entering/selecting the workweek. These two controls
would be Link Master Fields for the subform, so that the values entered
on the main form will always be filled in automatically in each subform
record. You would probably make the corresponding Link Child Fields on
the subform invisible, so the user can't even try to fill them in.

I'd recommend adding some code to the subform control's Enter event to
keep the user from even attempting to enter the subform if the employee
and workweek haven't both been filled in on the main form. That way,
the user won't be confronted with ugly "field cannot contain a Null
value ..." messages if they forget to fill out the main form before
entering data on the subform.