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?
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?
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
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!
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!
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.
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.Validation rule works even when 'required' is set to 'no'.
Yes, because you have made a change to the data (deleted it), the validation rule is applied and fails, so you get the error.In a pre-existing record, if I empty a filled field and press tab I 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.But for a new record, if I leave the field empty and press tab, I do not get an error message!
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).
Nicely explained! Thanks!
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
You could research the Forms BeforeUpdateEvent to see if it could be used.
More info re BeforeUpdate of a control
Another link
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!
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.