How to force a field not to be left blank

I have a form that requires an unbound field not to be null, but the data it contains doesn't have to be there in the first place, ie. I can't just make the data a required field in its table.

So I wrote a little bit of VBA to do it ...

Private Sub newlabel_LostFocus()
If IsNull([newlabel]) Then
MsgBox("Content Required")
Me!newlabel.SetFocus
End If
End Sub

This works OK, in that the message pops up under the right conditions (eg. if the user deletes existing content, or leaves a blank entry blank), but the focus moves on to the next field for some reason. I can correct this by adding a SendKeys backtab, but I don't like to use SendKeys and can't see what's wrong with the code.
pauldownhamAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MikeTooleConnect With a Mentor Commented:
Use the Before Update event on your text box to make sure that the user doesn't update it to Blank/Null:

Private Sub TheControl_BeforeUpdate(Cancel As Integer)
    If Nz(Me.TheControl, "") = "" Then
        MsgBox "Field must have a value"
        Cancel = True
    End If
End Sub

The focus will stay with the control till the user enters a value.
0
 
c_popCommented:
Hi,

Maybe you can use
IsNull([newlabel]) or IsEmpty([newlabel])
0
 
pauldownhamAuthor Commented:
works a treat ... thanks!
0
All Courses

From novice to tech pro — start learning today.