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

[Jessi]

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.

 

[Steve Schapel]

(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.

 

[Jessi]

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

 

[Steve Schapel]

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

 

[Jessi]

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

 

[Steve Schapel]

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

 

[Jessi]

Steve,

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

Jessi

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

--
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?