R
Robert M.
On a data entry form, I'm using a hidden form to pick up values to set
validation rules.
For example, I want a Number field (X) to be sequential, from 1 to N, where
N is defined in a (parent) table/form. I can't use Autonumber, as this
(child) table will have other sets of sequential records based on field (W).
Similarly, I have another field (call it Y) on the same form, which
represents Number of Days, which is chronologically correct, from 0 to M, and
must increment by at least 1 from the previous (X) record in the sequence.
Here's sample data, with incorrect data identified. My goal is to control
data entry to force/limit what the values of X & Y can be.
W X Y
1 1 0
1 2 3
1 3 4
1 4 5
1 6 9 incorrect - X has to be 5 for the 5th record in
this selection of records where W=1
4 1 6
4 2 7
4 3 2 incorrect - Y has to be 8 or greater, for this
3rd record where W=4
Note: I know that data edits will require even more vigorous testing, but
for data entry at this point....
1) created a select query with Totals, so that I can see my Max X and Max Y
for the currently selected W. When there are no records in the child table
for a selected W, Max X = 0 and Max Y= 0.
2) created & open a hidden form that populates with the current W, Max X,
and Max Y.
3) open data entry form where the bound field X is not enabled and has the
Default Value that X= Max X +1
and Y has the Rule Y= Max Y + 1 or (if Max X = 0 then Y= 0)
So this worked fine for the X field, but when I started adding the
validation on the Y field, the X field stopped working correctly.
Currently, whenever I open the form, the next New record will have the
correct value of X. But the default value - which was updating to reflect
the new Max X - is no longer updating. And if I press F5, the X field for a
new record shows #Error.
Help!
validation rules.
For example, I want a Number field (X) to be sequential, from 1 to N, where
N is defined in a (parent) table/form. I can't use Autonumber, as this
(child) table will have other sets of sequential records based on field (W).
Similarly, I have another field (call it Y) on the same form, which
represents Number of Days, which is chronologically correct, from 0 to M, and
must increment by at least 1 from the previous (X) record in the sequence.
Here's sample data, with incorrect data identified. My goal is to control
data entry to force/limit what the values of X & Y can be.
W X Y
1 1 0
1 2 3
1 3 4
1 4 5
1 6 9 incorrect - X has to be 5 for the 5th record in
this selection of records where W=1
4 1 6
4 2 7
4 3 2 incorrect - Y has to be 8 or greater, for this
3rd record where W=4
Note: I know that data edits will require even more vigorous testing, but
for data entry at this point....
1) created a select query with Totals, so that I can see my Max X and Max Y
for the currently selected W. When there are no records in the child table
for a selected W, Max X = 0 and Max Y= 0.
2) created & open a hidden form that populates with the current W, Max X,
and Max Y.
3) open data entry form where the bound field X is not enabled and has the
Default Value that X= Max X +1
and Y has the Rule Y= Max Y + 1 or (if Max X = 0 then Y= 0)
So this worked fine for the X field, but when I started adding the
validation on the Y field, the X field stopped working correctly.
Currently, whenever I open the form, the next New record will have the
correct value of X. But the default value - which was updating to reflect
the new Max X - is no longer updating. And if I press F5, the X field for a
new record shows #Error.
Help!