Validation of one column based on value of another column

[webinsomniac]

Hello,

This is the first time i've ever posted on this board, so bear with
me. I am trying to add data validation to one column, that checks the
value of another column in the same form, and if the value is true,
not allow a 0 to be entered in the column.

For example, Column 1 = Type, Column 2 = Usage.


If Column1 = Gas, Electric, Water, or Sewer, Then Column2 should not
allow a 0 value. If Column1 = Fire, Garbage, Sanitation Then Column2
should allow a zero value.


Currently my validation is >0 which works, but doesn't filter its
validation based on column1, only on the value of column2.


Is this even possible with Access? I am using Access 2007, but the
actual database was created in Access 2000

 

[tina]

there are several ways to do this. the simplest way to explain here is: add
validation code to the *form's* BeforeUpdate event procedure. something
along the lines of

Select Case Me!FirstControlName
Case "Gas", "Electric", "Water", "Sewer"
If Me!SecondControlName = 0 Then
Cancel = True
Me!SecondControlName.SetFocus
MsgBox "whatever message you want to " _
& "give the user"
End If
End Select

the above assumes that what you refer to as "Column1" and "Column2" are
actually *controls* in a form set to Continuous View or Datasheet View. so
replace FirstControlName and SecondControlName with the correct names of the
controls, of course.

hth

 

[webinsomniac]

there are several ways to do this. the simplest way to explain here is: add
validation code to the *form's* BeforeUpdate event procedure. something
along the lines of

Select Case Me!FirstControlName
Case "Gas", "Electric", "Water", "Sewer"
If Me!SecondControlName = 0 Then
Cancel = True
Me!SecondControlName.SetFocus
MsgBox "whatever message you want to " _
& "give the user"
End If
End Select

the above assumes that what you refer to as "Column1" and "Column2" are
actually *controls* in a form set to Continuous View or Datasheet View. so
replace FirstControlName and SecondControlName with the correct names of the
controls, of course.

hth

Thank you very much for the quick response. I will test this out, and
get back to you.

Sincerely,

M. Carrizales

 

[noservice]

I got a variable not defined on the cancel = true statement. I will
attempt to troubleshoot this and hopefully have a working validation
soon. Thanks for the help thusfar!

 

[Douglas J. Steele]

Did you put the code in the form's BeforeUpdate event, as Tina suggested, or
did you put it in some other event?

 

[noservice]

Did you put the code in the form's BeforeUpdate event, as Tina suggested, or
did you put it in some other event?

When I put the event in the BeforeUpdate event, nothing happened when
I tested the field with a zero value. Perhaps I am not defining the
control names properly. I will look into this.

 

[noservice]

Ok, first of all, thanks for the help with this. I seemed to have
solved the issues and it is validating correctly. I had to remove the
Me!SecondControlName.SetFocus because that threw out an error
relating to the data not yet being saved (Runtime 2108). Your code
gave me enough clues as to what I was doing wrong. Again, THANK YOU
VERY MUCH for your help. I really appreciate it.


Sincerely,


M. Carrizales

 

[tina]

you're welcome
and though i didn't set out to give you code that would err, that's not
always a bad thing - you can learn a lot from digging around and solving a
problem "the hard way", as you did. good job, and good luck as you continue
your project.