Hi,
How do I set multiple validations on a table? I know how to set one. Do I
need to use VBA code to do this or is there a simple way?
Thanks in advance for your help.
L. Drolette
Thank you John. Yes, I agree that VBA code is more powerful. Unfortunately
I don't know it! I have [several] questions where the 1st question asks for
a number and the following question asks for another number that must be <=
to the previous number entered. Since you can't use the value of another
control in a validation rule for a control, I'm have trouble visualizing the
easiest way to accomplish this. Is VBA code in the form's BeforeUpdate event
the only way to do this? Can this be done in a macro? And your correct, I
do indeed need different error messages for each error.
John W. Vinson said:Thank you John. Yes, I agree that VBA code is more powerful. Unfortunately
I don't know it! I have [several] questions where the 1st question asks for
a number and the following question asks for another number that must be <=
to the previous number entered. Since you can't use the value of another
control in a validation rule for a control, I'm have trouble visualizing the
easiest way to accomplish this. Is VBA code in the form's BeforeUpdate event
the only way to do this? Can this be done in a macro? And your correct, I
do indeed need different error messages for each error.
If that were the only rule (I gather that it isn't) then a table validation
rule might be
[FirstField] IS NOT NULL AND [SecondField] IS NOT NULL
And [SecondField] <= [FirstField]
with a validation text like
"Both Firstfield and secondfield must be entered, and secondfield must be less
than or equal to firstfield".
As you can see, this can get hairy with multiple rules!
I'm not experienced with macros; VBA for this kind of thing is actually easier
since you can use If... Then blocks of code.
Since I don't know the structure of your table or your rules, I can't be
specific about the code; but it would be something like
Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!txtFirst) Then
MsgBox "First must be filled in!", vbOKOnly
Me!txtFirst.SetFocus
Exit Sub
End If
If IsNull(Me!txtSecond) Then
MsgBox "First must be filled in!", vbOKOnly
Me!txtSecond.SetFocus
Exit Sub
End If
If Me!txtSecond > Me!txtFirst Then
MsgBox "Second must be less than or equal to First", vbOKOnly
Me!txtSecond.SetFocus
Exit Sub
End If
.... <etc etc>
End sub
Steve Schapel said:Drolette,
If you are talking about the value of different *fields*, then the
Validation Rule can easily be set up in the table design, for example:
[YourFirstField]>=[YourSecondField]
However, from your description, it soulds like you are trying to enforce
the value entered compared with the value in the previous *record*... am
I correct? If so, this is another kettle of fish altogether.
--
Steve Schapel, Microsoft Access MVP
Thank you John. Yes, I agree that VBA code is more powerful. Unfortunately
I don't know it! I have [several] questions where the 1st question asks for
a number and the following question asks for another number that must be <=
to the previous number entered. Since you can't use the value of another
control in a validation rule for a control, I'm have trouble visualizing the
easiest way to accomplish this. Is VBA code in the form's BeforeUpdate event
the only way to do this? Can this be done in a macro? And your correct, I
do indeed need different error messages for each error.
Hi John. Thanks for the examples and your response. Let me explain a bit
more. I'm developing a database that will be used for data entry of
questionnaires filled out by subjects in a research study. These
questionnaires often have crazy data and I want to avoid having data-entry
staff enter data that makes no sense. Here's an example of a questionnaire:
1. Did you have any outbreaks since your last visit? [hadob]
1a. If yes, how many outbreaks did you have? [numob]
1b. For how many of these outbreaks did you take therapy? [numtreated]
1c. For how many of these outbreaks did you come into the clinic?
[numclinic]
etc.
1a, 1b, and 1c are skipped if the subject had no outbreaks, so it's
perfectly fine for all of them to be null when [hadob]=no. In addition, if
the subject didn't answer any of the questions, data-entry is told to enter a
missing code (999 in this case). So in some cases, 1b will not be less than
1a (when 1b is 999). Then the same check needs to be done for 1a and 1c.
There are a ton of other checks I could do as well, like restrict entry of 1b
and 1c when 1a is 'no'. Am I beyond my capabilities given what little I know
about Access and VBA?
John W. Vinson said:Hi John. Thanks for the examples and your response. Let me explain a bit
more. I'm developing a database that will be used for data entry of
questionnaires filled out by subjects in a research study. These
questionnaires often have crazy data and I want to avoid having data-entry
staff enter data that makes no sense. Here's an example of a questionnaire:
1. Did you have any outbreaks since your last visit? [hadob]
1a. If yes, how many outbreaks did you have? [numob]
1b. For how many of these outbreaks did you take therapy? [numtreated]
1c. For how many of these outbreaks did you come into the clinic?
[numclinic]
etc.
Well... it sounds like you're making The Common Questionnaire Mistake, storing
one question per FIELD instead of one question per RECORD (in a related
table).
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 000'
for a valuable alterntive structure.
1a, 1b, and 1c are skipped if the subject had no outbreaks, so it's
perfectly fine for all of them to be null when [hadob]=no. In addition, if
the subject didn't answer any of the questions, data-entry is told to enter a
missing code (999 in this case). So in some cases, 1b will not be less than
1a (when 1b is 999). Then the same check needs to be done for 1a and 1c.
There are a ton of other checks I could do as well, like restrict entry of 1b
and 1c when 1a is 'no'. Am I beyond my capabilities given what little I know
about Access and VBA?
Well, you'll need to learn some VBA even with the normalized structure. What
you're requiring will be all but impossible with just table design and macros
since there are evidently a vast number of logical decisions to be made.
John W. Vinson said:Hi John. Thanks for the examples and your response. Let me explain a bit
more. I'm developing a database that will be used for data entry of
questionnaires filled out by subjects in a research study. These
questionnaires often have crazy data and I want to avoid having data-entry
staff enter data that makes no sense. Here's an example of a questionnaire:
1. Did you have any outbreaks since your last visit? [hadob]
1a. If yes, how many outbreaks did you have? [numob]
1b. For how many of these outbreaks did you take therapy? [numtreated]
1c. For how many of these outbreaks did you come into the clinic?
[numclinic]
etc.
Well... it sounds like you're making The Common Questionnaire Mistake, storing
one question per FIELD instead of one question per RECORD (in a related
table).
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 000'
for a valuable alterntive structure.
1a, 1b, and 1c are skipped if the subject had no outbreaks, so it's
perfectly fine for all of them to be null when [hadob]=no. In addition, if
the subject didn't answer any of the questions, data-entry is told to enter a
missing code (999 in this case). So in some cases, 1b will not be less than
1a (when 1b is 999). Then the same check needs to be done for 1a and 1c.
There are a ton of other checks I could do as well, like restrict entry of 1b
and 1c when 1a is 'no'. Am I beyond my capabilities given what little I know
about Access and VBA?
Well, you'll need to learn some VBA even with the normalized structure. What
you're requiring will be all but impossible with just table design and macros
since there are evidently a vast number of logical decisions to be made.
Hey John. I tried the link but get an error message saying that arguments
are of the wrong type, are out of acceptable range, or are in conflick with
one another. I also tried to cut and paste the URL but got the same message.
Any ideas?
The link worked but I can't get the database to run on my Access 2007
platform. The error message says that I need to 'enable content' to get
certain macros to run and that the database is not trusted. However when I
close the error message box and follow the instuctions, there is no 'message
bar' for which to 'enable content' and 'trust in office'.