Validation

[Beany]

Hi,

i have a form that requires validation. i am using the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.number) Then
MsgBox "number is required", vbOKOnly
Cancel = True
Me.number.SetFocus
End If


i have repeated the above code for other fields.........

The problem i have is, if i click 'x' and before it allows me to exit, it brings up the validations, one by one (so annoying).

Is there any way THAT it can show all the validations at once??? in one message????

THANKS

 

[jeremypaule]

I use this code and it seems to work well for me...

If Me.Name & "" = "" Then
MsgBox "Name is required"
Exit Sub
End If

 

[boblarson]

Beany:

The other way is to do something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim blnValidation As Boolean
Dim strValidate as String

strValidate = "These item(s) were not filled out and are required:" & vbCrLf

If IsNull(Me.number) or Me.number = "" Then
blnValidate = True
strValidate = strValidate & "Number" & vbCrLf
End If

If IsNull(Me.TextBox2) Or Me.TextBox2 = "" Then
blnValidate = True
strValidate = strValidate & "TextBox2"
End If

If blnValidate Then
MsgBox strValidate
Cancel = True
End If

End Sub

 

[gemma-the-husky]

Private Sub Form_BeforeUpdate(Cancel As Integer)

in your code after setting cancel=true (more properly cancel=vbcancel incidentally), you should EXIT the before update event. this stops the rest of the code running - is that what you mean?


If IsNull(Me.number) Then
MsgBox "number is required", vbOKOnly
Cancel = True
Me.number.SetFocus
'AND NOW
EXIT SUB
End If

 

[Beany]

BOb ur idea worked, thanks

just a quick question, i get one message now stating all the missing fields. In the message how do i space out the text and put spaces between field names:

for example:

instead of:

These item(s) were not filled out and are required:

Numbercost_centreusernamepukcodeimei


i would like


These item(s) were not filled out and are required:

Number
Cost_Centre
Username
Imei
etc
etc


how can i do this????????

 

[jwhite]

In each test where you build the strValidate, put & vbCrLf at the end, with the exception of the last test.

 

[boblarson]

fdcusa is correct. I put that on my first line, but forgot to include it on the string in my second test.

 

[Beany]

thanxs peep