Require Non Null From Lost Focus

[ddmtn546]

How would one write the code to require an entry or "Non Null" (validation check, so to speak), for a field using the lost focus feature for that field? Thanks, in advance.

 

[ancb]

ddmtn546,

If I understand your right, you want a message or some sort of flag to tell you if a field or control is null? If this is the case maybe this code will get your started.

******************************
Private Sub Text309_LostFocus()
If IsNull(Text309.Value) Then
MsgBox "This needs data!", vbOKOnly
Else
End If
End Sub

*******************************

Good luck!

Later

 

[Eupher]

If the form is bound to a table, you can edit the table's structure and set the "required" property of the field to "yes". Then Access will bark if the user attempts to save the record without making an entry in the field. If you want to control it on the form, you could create an event procedure on the On Lost Focus event, and put in code that looks something like this:

If IsNull(Me!txtMyTextbox) Then
     MsgBox "Sorry, you may not leave this field blank."
     Me!txtMyTextbox.SetFocus
End If

However, I urge caution using this approach. This can be quite annoying to the user if, for instance, the user wishes to enter info in other fields, then come back to that field; of if the user wishes to simply close or cancel out of the entire record - then they're stuck! They must enter something, even if it's wrong data.

A better choice might be to create an event procedure in the form's Before Update event with code like this:

If IsNull(Me!txtMyTextbox) Then
    MsgBox "You may not leave this field blank."
    Me!txtMyTextbox.SetFocus
    Cancel = True
End If

HTH...

Ken S.

 

[bgv]

The last approach (a Form Before Update event) does not seem to solve the complete problem. If you click on the MsgBox a second time after ignoring the firing of the first event, you can still enter a record in the table via the form with blank fields.

I have tried to tighten up a data entry form with a Before Update event, field Lost Focus events, a Before Insert event, a On Dirty event, a Current event, and a After Insert event. I have tried single events of the types above by themselves as well as in combinations. All still allow a record to be entered into the table if you just keep ignoring the MsgBox, and you can eventually get an invalid record.

The field SetFocus call also does not seem to place the focus back on the null field either.

Who has the Magic Beans that can make invalid entries in a form absolutely prohibit invalid entries?

 

[Eupher]

bgv,
What version of Access are you using? I'm using Access XP. I have tried repeateadly to reproduce the behavior you describe, and I can't. To test it, I have a little form bound to a table, with 5 fields in it. I used the code I posted earlier to prevent leaving a certain field blank, and it works perfectly every time. No matter how I try to leave the record, whether via Tab, Enter, Navigation Buttons, or the Close button, the BeforeUpdate event fires every time and dumps me back into the null field. The only exception is clicking the close button. After responding to the initial MsgBox, a "You can't save this record at this time" message appears, and clicking Yes closes the form without saving, clicking No dumps me back into the null field.

I *have* witnessed such behavior on occasion (at least the setfocus part) when using the BeforeUpdate event of a field, but not on the form BeforeUpdate. I'm stumped...

Ken S.

 

[bgv]

Access 2002 (10.3409.3501) SP-1