Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2005
    Posts
    29

    Unanswered: Field level Validation - MS Access Forms

    My application have been developed using MS Access forms with come VB code behind it...In one of the forms, I have two date fields,say,Date A and Date B.At any one given time,Date B must be greater than Date A,otherwise an error message(customized) should pop up requesting a valid input.This error should be envoked once the Date B field loose the focus......hence no more focus to other fields unless and until the valid date has been entered!!!!
    I have tried it using Validation Rule property at table and form level but it didnt work out properly...
    Any idea?

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    Textboxes have a 'LostFocus' event; put your validation code there, setting the focus back to the textbox if validation fails.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    lost focus event should be used very carefully and IMHV even rarer have a message box prompting and Requiring user input, and virtually never use set focus methods.

    bear in mind you are concerned that date A is less than OR Equal to date B

    so you trigger event should happen on the change of either date. If you set a lost fouce event on both dates then you will have a problem if you move to DateB from Date A (a lost focus event will occur on DateB) and wiull also occur on DateA

    the possinble candiates are lost focus (but be carefull not to use set focus), the controls on change, also consider using the forms onvalidation event and set the cancel to true on error.

    Instead of displaying a message you could uses an asterix "*" adjacent to the control, similar to many web pages required field. you could even be creative and use "*R*" to indictae a required field, "*E*" to indicate an error, you could change the control(s) background colour to indicate the same information. You could set a control button to disabled on error, so they cannot save the record unless the condition(s) are met

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by healdem
    lost focus event should be used very carefully and IMHV even rarer have a message box prompting and Requiring user input, and virtually never use set focus methods.
    Agreed. I prefer record-level validation, but fadone was rather specific in his/her requirements.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    concur: fodone request was explicit.

    no need to be defensive, or develop into a flame war

    In my view the design stylistic fadone requested, and provided would, if poorly implemented, could lead to problems, which by a different approach would not occur.

    Mebbe I was wearing a politicians hat, giving the anwer I though fadone required, rather than the answer the question actually asked, if so - guilty yer 'onner

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... And here's my 2 cents worth: I don't like putting control level validation in my forms. Since the user has to click the "Save" button in order to save anything, I have a validation routine I run that checks every dependency and requirement and pops a messagebox with ALL of the things missing or incorrect ...

    As for Fadone's example: WHAT if I don't know the dates yet (or better yet: I know the ending but not when it begins)???? Can't I fill in the rest of the info and then come back to the date(s)?
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Aug 2012
    Posts
    19
    I need help. The Cursor is going to next field if I put wrong value. My code is as under. The field type is Number

    Private Sub RollNo_BeforeUpdate(Cancel As Integer)
    If (RollNo.Value > 1) Or (RollNo.Value < 22) Then
    MsgBox "Roll No is Wrong, Please Type correct Roll No"
    Cancell = True
    End If
    End Sub

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try putting the code in the after update event of the control

    your if statement will always evaluate true assuming there is a numeric value in it

    I don't know what you are trying to do with cancell = true, it suggest to me you are not forcing explicit variable decalration (use option explicit in every form, report and code module. change the setting in your installation of Access to force variable declaration. this will save you chasing subtle errors because variabel aren't declared
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •