Validation null fields in continuous forms

[JPaulo]

Hi all;

How to validation null fields in continuous forms, with this code by tag ???

Public Function valNullControl(frm As Form)
Dim ctl As Control
Dim minhaArray As String
Dim validacaoOk As Byte
validacaoOk = 0
For Each ctl In frm.Controls
If ctl.Tag <> Null Or ctl.Tag <> "" Then
ctl.BackColor = 16777215
End If
Next ctl
For Each ctl In frm.Controls
If ctl.Visible = True Then
If ctl.Tag <> Null Or ctl.Tag <> "" Then
If IsNull(ctl) = True Or ctl = "" Then
ctl.BackColor = 161616
validacaoOk = 1
minhaArray = minhaArray & ctl.Tag & ", "
End If
End If
End If
Next ctl
If validacaoOk = 1 Then MsgBox "Required field: " & minhaArray
End Function



it works perfect in simple form.

 

[JamesMcS]

It's because it's still the same control , even though it's repeated down the page so your "for each" statement will only work once. Try putting the if statement in the on current even of the form (I think that's the right one, you might have to go through each event and see which is the most appropriate)

 

[JPaulo]

Thanks for reponse;

But I wanted to validate the end of the completed form, a button.

Private Sub cmdValidation_Click()
Call valNullControl(Me)
End Sub

 

[JamesMcS]

Ah - in that case, I'd try a count of null values in the query the form is based on, if it's greater than 0, display the error message. But the "for each" part of the loop won't work as there's only ever one control, it's just displayed more than once.

 

[JPaulo]

Ok;

Any idea for this validation?

 

[JamesMcS]

Well, if it's just validation you're after, why don't you try putting a validation rule in the table's design? That way the user won't be able to leave the field blank (or whatever criteria you put in). That way we get around the whole continuous forms thing too....

 

[JamesMcS]

Or put the count SQL statement into an if statement in VBA:

If count_of_nulls >0 then
Display an error message
endif

With the form in continuous view I don't think you can just colour in the one text box.