Checking controls on a form... (1 Viewer)

[DALeffler]

An easier way to check txtbxs and cmbobxs on a form for null or zero without going 5(?) deep?

Function ChkFlds(FormName As Form) As String
'Function ChkFlds checks each textbox and combobox control on the passed form object for
'null or zero values (zero values where applicable) and returns the control name with the
'lowest tab index of any control found on the form that's either null or zero...

Dim Msg, Title, Style
Dim LwstTbIndx As Single

LwstTbIndx = 1000000 'nobody puts a million controls on a form, right?

For Each ctl In FormName
    If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
        If IsNull(ctl) Then GoTo lbl1
            If ctl.Format = "General Number" Then
                If ctl = 0 Then
lbl1:               If ctl.TabIndex < LwstTbIndx Then
                        LwstTbIndx = ctl.TabIndex
                        ChkFlds = ctl.Name
                    End If
                End If
            End If
    End If
Next ctl

If Len(ChkFlds) <> 0 Then
    Msg = "At least one of the input fields on the form is blank or Zero." & (Chr(13) & Chr(10)) & _
          "You must fill in all the input fields (or select an item from a" & (Chr(13) & Chr(10)) & _
          "drop down list) before clicking on the 'Import' button."
    Title = "Incomplete Form..."
    Style = vbOKOnly
    MsgBox Msg, Style, Title
End If

End Function

Whew!

Thnks,

Doug.

 

[AlanS]

First, it's not good practice to use a GoTo to branch into or out of a control structure like If Then ... End If. Also, since TabIndex is an Integer or Long property, it's probably not a good idea to use a Single variable to track it - I would declare LwstTbIndx as Long, and initialize it at whatever is the maximum number of controls that can be added to a form over its lifetime in your version of Access (754 in Access 97), since the maximum TabIndex value for a control would be one less than the maximum number of controls.

Try this for your For loop:

For Each ctl In FormName
If ctl.ControlType = acTextBox Or _
ctl.ControlType = acComboBox Then
If (IsNull(ctl) Or ((ctl.Format = "General Number") _
And (ctl = 0))) And (ctl.TabIndex < LwstTbIndx) Then
LwstTbIndx = ctl.TabIndex
ChkFlds = ctl.Name
End If
End If
Next ctl

 

[The_Doc_Man]

At least one way to simplify is to change the IF to SELECT CASE

For each ctlObj in me.From loop

select case ctlObj.ControlType

Case acTextBox,acComboBox,acListBox, etc. etc.

if Len(Nz(ctlObj.Value,"")) = 0 and ctlObj.Format=whatever THEN
... whatever you were going to do
end if

case else
end select

end loop

 

[DALeffler]

Here's what I finally came up with:

Function ChkFlds(FormName As Form) As String
'Function ChkFlds checks each textbox and combobox control on the passed form object for
'null or zero values (zero values where applicable) and returns the control name with the
'lowest tab index of any control found on the form that's either null or zero...

Dim Msg, Title, Style
Dim LwstTbIndx As Long
Dim NllFlag As Boolean

LwstTbIndx = 753 '754 max number of controls/sections allowed

For Each ctl In FormName
NllFlag = False
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
    If IsNull(ctl) Then
        NllFlag = True                      'any null field needs to be filled by user
    End If

'Note: combining the 2nd and 3rd if statements in the next block results in a
'Type Mismatch error for all controls that aren't formatted as a number.

    If Not NllFlag Then                         'if the field is not null,
        If ctl.Format = "General Number" Then   'is the field numeric?
            If ctl = 0 Then                     'yes - check for 0
                NllFlag = True                  'field needs to be checked by user
            End If
        End If
    End If

    If NllFlag And ctl.TabIndex < LwstTbIndx Then 'field requires user & lowest tab index?
        LwstTbIndx = ctl.TabIndex                 'yes -
        ChkFlds = ctl.Name                        'set return variable
    End If
End If

Next ctl

If Len(ChkFlds) <> 0 Then
    Msg = "At least one of the input fields on the form is blank or Zero." & _
(Chr(13) & Chr(10)) & _
          "You must fill in all the input fields (or select an item from a" & _
(Chr(13) & Chr(10)) & _
          "drop down list) before clicking on the 'Import' button."
    Title = "Incomplete Form..."
    Style = vbOKOnly
    MsgBox Msg, Style, Title
End If

End Function

The hardest part about this whole problem for me was learning that anytime I had the code "ctl = 0" anywhere (like, "if ctl <> null and ctl = 0 then"), it would generate a type mismatch error any time the loop was in an iteration for a control that wasn't formatted to a numeric value.

Thanks to AlanS and The_Doc_Man for the input -

Doug.

(tried to get page width smaller wid' edit)