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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|