A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old April 19th, 2008, 10:26 PM posted to microsoft.public.access.forms
jessi
external usenet poster
 
Posts: 23
Default How to validate a form field based on information in a table

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.
  #2  
Old April 19th, 2008, 11:22 PM posted to microsoft.public.access.forms
Steve Schapel
external usenet poster
 
Posts: 1,422
Default How to validate a form field based on information in a table

Jessi wrote:
(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.

--
Steve Schapel, Microsoft Access MVP
  #3  
Old April 20th, 2008, 04:38 PM posted to microsoft.public.access.forms
jessi
external usenet poster
 
Posts: 23
Default How to validate a form field based on information in a table

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" wrote:

Jessi wrote:
(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.

--
Steve Schapel, Microsoft Access MVP

  #4  
Old April 20th, 2008, 08:20 PM posted to microsoft.public.access.forms
Steve Schapel
external usenet poster
 
Posts: 1,422
Default How to validate a form field based on information in a table

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

--
Steve Schapel, Microsoft Access MVP

Jessi wrote:
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.

  #5  
Old April 21st, 2008, 03:09 AM posted to microsoft.public.access.forms
jessi
external usenet poster
 
Posts: 23
Default How to validate a form field based on information in a table

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" wrote:

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

--
Steve Schapel, Microsoft Access MVP

Jessi wrote:
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.


  #6  
Old April 21st, 2008, 11:15 AM posted to microsoft.public.access.forms
Steve Schapel
external usenet poster
 
Posts: 1,422
Default How to validate a form field based on information in a table

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

Jessi wrote:
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?

  #7  
Old April 21st, 2008, 02:55 PM posted to microsoft.public.access.forms
jessi
external usenet poster
 
Posts: 23
Default How to validate a form field based on information in a table

Steve,

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

Jessi

"Steve Schapel" wrote:

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

Jessi wrote:
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?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:57 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.