Verifying Required Forms (Please Help) :)

[Guest]

Scenario: I am using the code below to verify that particular fields in my
form are completed. This works fine but I want more. I have 2.5 questions
about this.

If IsNull(Field1) Or IsNull(Field2) Or IsNull(Field3) Or IsNull(Field4) Then
MsgBox "All required fields not completed.", vbInformation, "Required
Information"
Else
DoCmd.Close

Questions:
Q1: I would like to list which fields are incomplete. How do I do that (the
simplest way possible)? I have reviewed many posts and found 2 diff solutions
one which makes sense but doesn’t seem to work and the other is so foreign to
me that I can’t modify it if needed. More details regarding the 2 posted
solutions below.

Q2: One of my fields is a multi select list box, which apparently won’t work
with the “IsNull†parameter. How do I verify that at least one of the items
in the list box is selected?

Q2.5: How can I accomplish all of this checking and messaging in one string
of code?

All help is greatly appreciated. Thanks in advance.
Shel

Posted Solutions and Why I can’t seem to use them:
Solution1: (This is very straight forward I understand what it’s supposed to
do but I get the following error; Run-time error ‘438’: Object doesn’t
support this property or method)

Dim ctl as Control
Dim strMissedControls as String

For Each ctl in Me.Controls
If ctl.Value="" Then
strMissedControls=strMissedControls & vbCrLf & ctl.Name
End If
Next ctl

If strMissedControls <> "" Then
strMissedControls= "You have not completed the following fields:" &
strMissedControls
Cancel=True
End If


Solution2: (Difficult to understand, wouldn’t know how to modify it if
needed, and I don’t know how to apply it)
Public Function fnValidateForm(frmA As Form) As Boolean
Dim ctl As Control
fnValidateForm = True
For Each ctl In frmA.Controls
'value in the control is required
If InStr(1, ctl.Tag, "Required") &gt; 0 Then
' no value entered or value is null
' or zero for numeric fields
If (IsNull(ctl.Value)) Or (Len(ctl.Value) = 0) Then
ctl.SetFocus
MsgBox "Value required"
fnValidateForm = False
Exit For
End If
If InStr(1, ctl.Tag, "NumberRequired") > 0 Then
If ctl.Value = 0 Then
ctl.SetFocus
MsgBox "Value required"
fnValidateForm = False
Exit For
End If
End If
End If
Next
End Function
To use set the tag line for each control you require to AlphaRequired or
NumberRequired.
HTH

 

[Albert D. Kallal]

Here is the approch that I use, and it is clean, and can be re-used for each
form you make.

In the forms before update event, I go:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = MyVerify

End Sub


And, for each form, I thus write a routine called MyVerify. In your case
you have 4 fields. Letrs add field1, 2, and a few tohers.

Note the formnhat of

colField.Add "FieldName, error message text"


Private Function MyVerify() As Boolean

Dim colFields As New Collection

MyVerify = False

colFields.Add "Field1,Event code"
colFields.Add "Field2,Field2"
colFields.Add "EYear,Year"
colFields.Add "City,City"
colFields.Add "cboProvince,Province"

MyVerify = vfields(colFields)

If MyVerify = True Then
Exit Function
End If
' speical custom error checking goes here
Q2: One of my fields is a multi select list box, which apparently won't
work
with the "IsNull" parameter. How do I verify that at least one of the
items
in the list box is selected?
You do realize that multi-select can NOT be bound to a field...right????

If Me.List11.ItemsSelected.Count = 0 Then
MyVerify = True ' cancel = true
msgbox "please select somting in the list box",vbInformation
exit Function
end if

End If


End Function


Private Function vfields(colFields As Collection) As Boolean

Dim strErrorText As String
Dim strControl As String
Dim I As Integer

vfields = False

For I = 1 To colFields.Count
strControl = Split(colFields(I), ",")(0)
strErrorText = Split(colFields(I), ",")(1)
If IsNull(Me(strControl)) = True Then

MsgBox strErrorText & " is required", vbExclamation, AppName
Me(strControl).SetFocus
vfields = True
Exit Function
End If
Next I


End Function

So, the first part t of my-verify has all the fields you MUST fill out. The
beauty of this system is that the code also sends the cursor to the field
that was NOT filled out. And, you get a nice error message with very little
code. For those "special" cases...like a list box, or fields that are
dependent on "other" values..then you just start coding those exceptions at
the "end" of the very code. It is a clean approach, minimal code (at least
for you to write), and it works well.

 

[Albert D. Kallal]

do note, that I have:

MsgBox strErrorText & " is required", vbExclamation, AppName

You need to replace AppName with your own text (all my appcltions has a
public function called AppName that returns the name of the appction).

 

[Douglas J. Steele]

The reason solution 1 doesn't work is that not all controls have a Value
property. You need to check for the type of control.

Dim ctl as Control
Dim strMissedControls as String

For Each ctl in Me.Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
If Len(ctl.Value & "") > 0 Then
strMissedControls=strMissedControls & vbCrLf & ctl.Name
End If
ElseIf TypeOf ctl Is ListBox Then
If ctl.ItemsSelect.Count = 0 Then
strMissedControls=strMissedControls & vbCrLf & ctl.Name
End If End If
Next ctl

If Len(strMissedControls) > 0 Then
strMissedControls= "You have not completed the following fields:" &
strMissedControls
Cancel=True
End If


You may want to check for more than the 3 control types I've included.

 

[Guest]

Thanks Guys! I'll give it a whirl and see what happens.