Main Form, Subform question

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I have a main form where I enter total hours [Thours], I also have a subform
[IDRsubform] with a field [Hours] where I enter hours from 1 to 10 records.
The hours on the subform could range from .1 hrs to 15.0 hrs for each
subform record. All records in the subform must equal the total hours on
the main form. I'm looking at a way to prevent a new record from being
entered unless both forms agree on the hours. May be an IF statement
somewhere...Any ideas...Thanks...Randy
 
I have a main form where I enter total hours [Thours], I also have a subform
[IDRsubform] with a field [Hours] where I enter hours from 1 to 10 records.
The hours on the subform could range from .1 hrs to 15.0 hrs for each
subform record. All records in the subform must equal the total hours on
the main form. I'm looking at a way to prevent a new record from being
entered unless both forms agree on the hours. May be an IF statement
somewhere...Any ideas...Thanks...Randy
Click to expand...

This will be REALLY difficult. The problem is that with a form and
subform arrangement, the mainform record is saved immediately, the
instant you set focus to any control in the subform. It needs to be
done this way since (typically) the tables in a Form and Subform are
in an enforced one-to-many relationship and you need to store the
mainform record in order for subform records to be entered; otherwise
they would have no matching record in the main table.

Therefore, you can't use the main form's BeforeUpdate event to check
to see if the sum is correct - it will fire before any records have
been entered on the subform at all! You may need to use two tables, a
"scratchpad" table bound to the subform, and a "real" table, with VBA
code to append the scratch pad records to the real table when (and
only when) the sum is correct.

Another complicating factor is that if [Hours] is a Single or Double
Float, roundoff error may make it impossible to get an exact match.
0.1 + 0.1 + 0.1 does NOT equal 0.3! It is different in about the 14th
decimal place. You can use a Currency or Decimal datatype for Hours to
avoid this problem.

John W. Vinson[MVP]
 
Back
Top