 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / July 2007Validation of one column based on value of another column
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 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 > Hello, > [quoted text clipped - 14 lines] > Is this even possible with Access? I am using Access 2007, but the > actual database was created in Access 2000 > 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 [quoted text clipped - 39 lines] > > Is this even possible with Access? I am using Access 2007, but the > > actual database was created in Access 2000 Thank you very much for the quick response. I will test this out, and get back to you. Sincerely, M. Carrizales 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! On Jul 13, 10:08 pm, "webinsomn...@gmail.com" <webinsomn...@gmail.com> wrote: > > 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 [quoted text clipped - 46 lines] > > M. Carrizales Did you put the code in the form's BeforeUpdate event, as Tina suggested, or did you put it in some other event?  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I got a variable not defined on the cancel = true statement. I will > attempt to troubleshoot this and hopefully have a working validation [quoted text clipped - 56 lines] >> >> M. Carrizales On Jul 16, 11:20 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote: > Did you put the code in the form's BeforeUpdate event, as Tina suggested, or > did you put it in some other event? [quoted text clipped - 67 lines] > > >> M. Carrizales 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. 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 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. > 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 [quoted text clipped - 6 lines] > > M. Carrizales |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.