Jzachar,
The way I read it, there are a number of related questions here. You
need to check that for every subform record there is an entry in both
the [Date Incurred] and [Amount Incurred] fields. You need to check
that for every main form record, there is at least one subform record.
And I suppose you also want to check that the subform records total to
the Amount on the main form... though this concept is actually problematic.
Well, the first is taken care of pretty easily, either via the
Validation Rule property of the fields as discussed earlier, or via some
simple code on the subform's Before Update event.
The second is not going to be easy, if you are using the built-in
navigation buttons, as there is no event really available that you can
use to do your validation. Depends a bit how tightly you want to
enforce it. You could, for example, put a big red label at the bottom
of the main form, near the navigation buttons, and use code to hide or
show it, depending on whether there is a subform record or not. Or you
could go a step further, and use the Current event of the main form to
hide the navigation buttons, if there is no subform record, and the only
way to make them available again is via code on the subform's Exit
event. But then the user could still close the form, with no subform
records, which is not what you want either. You could remove the
navigation buttons and close button, so that the only way the user can
move from the current main form record is by using command buttons that
you place on the form, in which case you can use the Click event of the
command buttons to check the data. Or you could forget the idea of
checking the records one by one as they are entered, and instead make a
procedure that runs every time you open the database, and throws an
message box if it finds any records in the main form's table with no
corresponding records in the subform's table. Sorry to be non-specific
at this stage, but I think first we can clarify the general approach
that might be most suitable.
As for the Amount field, it is unnecessary, and probably an unwise idea,
to store this value. You have the Amount Incurred in the subform's
table, and the invoice amount will always be the sum of these, so you
can always very easily calculate the Amount when needed.
--
Steve Schapel, Microsoft Access MVP
my fault,
The relationship between the subform and form tables is a one to many
relationship using an autonumber ID with referential integrity enabled. The
user can always see the subform therefore I would assume that it is
continuous view. The subform allows multiple record entries that relate to
one record on the form. There are three fields in the subform and its table:
[description], [Date Incurred], and [Amount Incurred]. I want the user to be
required to enter data in both [Date Incurred] and [Amount Incurred], but
they won't have to enter anything in the [Description] field. For instance,
the form has a field that is [Amount], which holds the total amount for an
invoice, while the subform allows the user to enter multiple records which
break out the amount across multiple dates. An example would be an [Amount]
of $1000 in the form and two records related to this in the subform that
would show in which months these amounts were incurred. These two records in
the subform could be $500 in April and $500 in May. The user navigates
between records on both the form and subform by user the built-in navigation
buttons.
please let me know if you need more...
thanks again for the help
jzachar
