Results 1 to 10 of 10
  1. #1
    Bradex is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    101

    Error messages for mandatory fields not sequential

    I have a form with 10 fields. All of them are mandatory (Required = yes).


    Suppose, I leave field3 empty and try to move to the next field, I get an error message saying 'field9 is empty'.
    What I expect is 'field3 is empty'.
    How can I correct this?

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    How are you making the Fields Required? I ask, because setting them as Required, at the Table level, isn't going to pop any error when you skip filling out a given Control...it will only do so when you try to save the Record, either explicitly or by moving to another Record or by closing the Form or Access, itself. Which means that you appear to be using code to check each Control as you exit it.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Bradex is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    101
    You caught me! I was indeed saving the form when the error popped up. I am not using any code.
    But I am still puzzled. Why am I getting error for a lower field (field9) even when field3 is also required when I try to save?

    As you said, is there a code to check each control as I exit it?

    Thanks!

  4. #4
    Bradex is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    101
    I found that using the validation rule 'Is Not Null' partly suits my requirement.
    - In a pre-existing record, if I empty a filled field and press tab I get an error message.
    - But for a new record, if I leave the field empty and press tab, I do not get an error message!

    Validation rule works even when 'required' is set to 'no'.

    So is 'required' a redundant function? Could you explain the difference between these two?

    Thanks!

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The "required" property is set at the table level, and when set to "Yes" means only that the field it applies to must contain something, but it says nothing about what that something should look like. The "required" property will only generate an error when a record is saved, either new or updated.

    A validation rule (in a form) is applied to a field, and checks whether data entered is valid or not. It only does this check when you make changes to data in the field - just tabbing through a field (control) does not make any changes to it, so the validation check does not fire. Whether the field in the underlying table is required or not does not affect the validation rule.

    Validation rule works even when 'required' is set to 'no'.
    That's right. The validation rule is always applied to data that is entered into the control, right after it has been entered or changed. Pre-existing data that is in a control that you just tab through is not checked.

    In a pre-existing record, if I empty a filled field and press tab I get an error message.
    Yes, because you have made a change to the data (deleted it), the validation rule is applied and fails, so you get the error.

    But for a new record, if I leave the field empty and press tab, I do not get an error message!
    Because in this case you made no change to the data (you just tabbed through the control), so the validation rule is not applied. You would get an error when Access tried to save the record, because the Required = Yes would fail.

    So, while the Validation rule and the required property are essentially independant of each other, you use the two of them in combination, e.g. a number must be entered (Required = Yes) and it must be positive (validation rule).

  6. #6
    Bradex is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    101
    Nicely explained! Thanks!

  7. #7
    Bradex is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    101
    Also, I found that using validation rule (Is Not Null) and validation text allows you to give a custom message. This is similar to the 'Required' function (which will give a default message instead).


    I found that this code suits my need (it checks whether the control has been populated as I exit it pressing the TAB key and gives a custom error message):
    Code:
    Private Sub textbox_Exit(Cancel As Integer)
    If IsNull(textbox) Or textbox = "" Then
         MsgBox "You can not leave the field empty!"
         textbox.SetFocus
    End If
    End Sub

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    Bradex is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2011
    Posts
    101
    From what I gather, BeforeUpdate will give the error message only on saving the form while Exit will do it when I try to move to the next control.

    Thanks for the suggestion though!

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Sort of kinda... The BeforeUpdate event is the event you can access immediately before the field/control is updated/value'd.
    Glad you found a solution that works for you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-27-2016, 05:35 PM
  2. Check for mandatory fields before adding new record
    By ittechguy in forum Programming
    Replies: 1
    Last Post: 10-24-2015, 11:05 PM
  3. Query for non mandatory fields
    By pratim09 in forum Queries
    Replies: 2
    Last Post: 09-01-2011, 01:33 PM
  4. Custom messages to Access' default error messages.
    By evander in forum Programming
    Replies: 1
    Last Post: 06-26-2010, 02:06 AM
  5. Replies: 8
    Last Post: 03-29-2010, 10:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums