Adding validation, if cell not complete prompt messge?? help??

[bsnapool]

Hi All

I was wondering if somebody could help me, I have a form and I would like to add validation if a text box is null, not entered, a message to say that this is a compulsory field?

Any suggestions or pointers in the right direction?

Thanks in advance

Andrew

 

[Matt Greatorex]

There are probably plenty of ways of doing this, but you could try something like:

If [Forms]![[I]field name[/I]] = "" or IsNull([Forms]![[I]field name[/I]]) Then
       Msgbox [I]field name[/I] & " must be completed"
       [Forms]![[I]field name[/I]].SetFocus
End If

The above would notify the user that the field couldn't be blank and set the cursor back to it.

This could go in a function and be called whenever someone exits the field in question, tries to close the form, clicks on 'Save', or whenever else you might think appropriate.

Hope it helps.

 

[bsnapool]

Matt

Thanks for your reply.

Were would this code go?

 

[Matt Greatorex]

Create a module, call it whatever you like, then put this code into a new function within it. You can then call the function and pass in the name of the form and the field in question, each time you need to.

That way, if you need to do something similar for various fields on various forms you only need to write it once.

I would probably call it from a few places, to be safe:

The 'On Lost Focus' event of the field
The 'On Close' event of the form itself
The 'On Click' event of a 'Save' button (if one exists).
etc.

 

[Rich]

Any validation code belongs in the BeforeUpdate event of the form

 

[bsnapool]

What happens if another events is already in there? I have the follwoing code in there now:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo BeforeUpdate_Err

    ' Set bound controls to system date and time.
    dateModified = Date
    TimeModified = Time()
    UserID = Environ("username")

BeforeUpdate_End:
    Exit Sub


BeforeUpdate_Err:
    MsgBox Err.Description, vbCritical & vbOKOnly, _
        "Error Number " & Err.Number & " Occurred"
    Resume BeforeUpdate_End
End Sub

 

[gemma-the-husky]

just put some more code in there (at the top, before you set the date time etc)

eg

if nz(problemfield,vbnullstring) = vbnullstring then
call msgbox("you must put something in such and such",vbexclamation)
cancel=vbcancel
exit sub
end if

nz sets a null to be a blank string, so you only need 1 test
cancel=vbcancel forces the update to fail
and you don't need to carry on, so you can exit now.

 

[bsnapool]

Gemma

This is spot on!!

Another addition, would it be possible to highlight that field to show the compulsary field?

 

[gemma-the-husky]

you can do things with say

savcolor = badfield.backcolor (save the current color)
badfield.backcolor = vbyellow (set a different color)

however you then need to reset it after its done with, (say in the afterupdate event) with

badfield.backcolor = savcolor

which is a bit more messing about