Check for required when leave the field

[Rhubie]

How can I check for a field being required when I leave the field rather than when I save the record?

[Bob Fitz]

With code in the On Exit event.

  If IsNull(Me.ActiveControl) Then
    Cancel = True
  End If

This would keep the focus on the control until the user made an entry. But IMHO it would be better to do any validation checks in the forms Before Update event.

[Missinglinq]

And if the user simply ignores the Control, i.e. never tabs into it? As Bob said, you really have to do this kind of validation in the Form_BeforeUpdate event, in order to insure that the Control is populated!

Linq ;0)>

[Rhubie]

Thank you - if I do the validation in the Before Update event - then it doesn't check until I save the record - is that correct?

Would I use the same code in the Before Update Event?

[Bob Fitz]

The code would be something like:

If IsNull(Me.NameOfControlBeingChecked) Then
    Cancel = True
  End If

[Rhubie]

that would be in the before update event - would that check it before saving - wouldn't that work the same as having the required fields in the table?

[Bob Fitz]

I think the simple answer to that is yes, yes and yes.

[Missinglinq]

Yes it would, except that using Required at the Table-level will only catch cases where the Field is Null, I believe, while something like

If Nz(Me.NameOfControlBeingChecked,"") = "" Then
   Cancel = True
   NameOfControlBeingChecked.SetFocus
End If

will catch Nulls and Zero-Length Strings, cancel the update, then move back to the offending Control so that it can be populated.

If you're doing more than one Validation, you need to add a line, to each of the Validations, so that it evaluates one Control, returns to the offending one, and gives the user time to correct it before evaluating the next one.

If Nz(Me.NameOfControlBeingChecked,"") = "" Then
   Cancel = True
   NameOfControlBeingChecked.SetFocus
   Exit Sub
End If

Most experienced developers prefer to do this kind of thing thru code, at the Form-level, rather than depending on doing things at Table-level, because they feel it gives them better control over things, especially things like the warning Messageboxes.

Setting the Field named DME_Prov as Required, and leaving it blank, when going to save the Form, will pop a message that

'the Field named DME_Prov is Required and as a result the Record cannot be saved'

Chances are that the users will have no idea which Control holds DME_Prov. Even if labeled, the Control that holds the DME_Prov Field will probably have a Caption that says something like 'Durable Medical Equipment Provider;' something that makes sense to the user. The Access generated message will mean nothing to the end user, citing the DME_Prov Field as the problem, but a custom Messagebox can give the user a name that they will recognize and know where to go to correct their oversight.

And as stated before, in order to insure that a Control is not left empty, the warning has to come just before the Record is Saved.

Linq ;0)>

[Rhubie]

I am not an experience developer as you can see, but I am learning - how would I do this at the form level?

[Bob Fitz]

Missinglinq has given you the required code (below). This would go in the Form's Before Update event. Change the red type to the name of the control that you want to check. The code can be repeated if you need to check other text boxes.

If Nz(Me.NameOfControlBeingChecked,"") = "" Then
   Cancel = True
   NameOfControlBeingChecked.SetFocus
   Exit Sub
End If

[Rhubie]

Thank you - if I want to add a message text to the field - how would I do that - what does Me. - would Me. be the table name?
I have 3 fields - so I would put the code 3 times?

[Bob Fitz]

If Nz(Me.NameOfControlBeingChecked1,"") = "" Then
   Cancel = True
   Msgbox "Please enter data",vbOKOnly,"Data Required"
   NameOfControlBeingChecked1.SetFocus
   Exit Sub
End If

I have 3 fields - so I would put the code 3 times?

Yes

Me. Is used to refer to the current form, the one in which the code is written. You would not be able to use Me. in a general module.

[Rhubie]

Ok - the name of the form versus the name of the table - thank you - I have it working - I will need to take an online course for VB I think.

[Bob Fitz]

You can also use Me in a report module.
The link below will give you more information.
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx