How to validate a form field based on information in a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jessi - 19 Apr 2008 22:26 GMT

I have two tables:

tbl:Field_Mass1
StationID (fk)
SampleID (pk)
Mass1

tbl:Field_Mass2
StationID (fk)
SampleID (pk)
DateMeOHAdded
Mass2

These two tables are related with a 1:1 relationship on the field SampleID
(I've considered the 1:1 relationship, and I think is appropriate due to
other constraints I want to impose and on the order of data entry).

I use 2 forms to enter data into these tables (frm:FieldMass1 and
frmFieldMass2)

Users enter data into tblFieldMass1 first and then into tblFieldMass2.

When users enter Mass2 using the form frm:FieldMass2 I want to ensure that
Mass2 is greater than Mass1 from tbl:FieldMass1.

I've tried writing an expression into the validation rule for Mass2 on
frm:FieldMass2 but can't seem to get it to work. Any suggestions would be
appreciated.

Reply to this Message

Steve Schapel - 19 Apr 2008 23:22 GMT

> (I've considered the 1:1 relationship, and I think is appropriate due to
> other constraints I want to impose and on the order of data entry).

What you are doing will be a lot simpler if you would review this
decision, which on the face of what you have told us so far is probably
not the best way to go here. All this data should be in a single table,
with the Mass data in a single field, and an additional field to
identify whether the Mass data is the 1st or 2nd.

Signature

Steve Schapel, Microsoft Access MVP

Reply to this Message

Jessi - 20 Apr 2008 16:38 GMT

Steve:

I appreciate your advice, but I think I prefer to keep the 1:1 relationship.
I want to be able to enforce the "required" property for both Mass1 and
Mass2. I don't see how to do this if these attributes are in the same table
because the user will be entering Mass2 anywhere from 1 to 4 weeks after they
enter Mass1. In addition, the records in Mass2 have an additional attribute,
DateMeOHAdded, that does not apply to Mass1. But, alternative suggestions
are always welcome.

So, I'm still wondering if the validation step in the original question is
possible.

Thank you again for your reply,

Jessi

> > (I've considered the 1:1 relationship, and I think is appropriate due to
> > other constraints I want to impose and on the order of data entry).
[quoted text clipped - 4 lines]
> with the Mass data in a single field, and an additional field to
> identify whether the Mass data is the 1st or 2nd.

Reply to this Message

Steve Schapel - 20 Apr 2008 20:20 GMT

Jessi,

You appear to have misunderstood my earlier reply. The 1 and 2 entries
for Mass will be in the same field in separate records. You can enforce
Required if you wish. And the fact that there is a field that only
applies to the 2s and not the 1s is not a valid reason for going to an
unnormalised design.

Like this...

tbl:Field_Mass
StationID
SampleID
DateMeOHAdded
Mass
1_or_2

Signature

Steve Schapel, Microsoft Access MVP

> Steve:
>
[quoted text clipped - 5 lines]
> DateMeOHAdded, that does not apply to Mass1. But, alternative suggestions
> are always welcome.

Reply to this Message

Jessi - 21 Apr 2008 03:09 GMT

Steve,

I see what you're saying. I've used this approach before. However, I'm
still uncertain how to write the validation rule to ensure that Mass2 is
greater than Mass1. Do you have any suggestions for writing this rule?

Thanks for your help/time,

Jessi

> Jessi,
>
[quoted text clipped - 22 lines]
> > DateMeOHAdded, that does not apply to Mass1. But, alternative suggestions
> > are always welcome.

Reply to this Message

Steve Schapel - 21 Apr 2008 11:15 GMT

Jessi,

No, a Validation Rule would not apply to this situation. You would need
to write your own validation procedure, to go on either the Before
Update event of the form itself, or the Before Update event of the Mass
control. Here is a skeleton example of such code...

If Me.OneOrTwo = 2 Then
If Me.Mass <= DLookup("[Mass]","Field_Mass","[OneOrTwo]=1 And
SampleID=" & Me.SampleID) Then
MsgBox "Oi!!"
Cancel = True
End If
End If

Signature

Steve Schapel, Microsoft Access MVP

> Steve,
>
> I see what you're saying. I've used this approach before. However, I'm
> still uncertain how to write the validation rule to ensure that Mass2 is
> greater than Mass1. Do you have any suggestions for writing this rule?

Reply to this Message

Jessi - 21 Apr 2008 14:55 GMT

Steve,

Ah, okay. Thank you for the example code. This should do the trick. Thank
you for all of your suggestions.

Jessi

> Jessi,
>
[quoted text clipped - 16 lines]
> > still uncertain how to write the validation rule to ensure that Mass2 is
> > greater than Mass1. Do you have any suggestions for writing this rule?

Reply to this Message