VBA to ckeck null value in bound or unbound controls

[aman]

Hi ALl

I am writing the following code that will check if any of the controls is left blank when the Checkbox is clicked . If the control value is null then display an error message and Exit sub. The following code works fine for Unbound control and the bound textboxes but there are bound comboboxes and it doesn't check if they are blank.

 Private Function CheckIfEmpty(controlname, controlvalue, controltext) As Boolean
   
    CheckIfEmpty = False 'Default Value
    
    'ControlName.SetFocus
    If controlvalue = "" Or IsNull(controlvalue) Then
        CheckIfEmpty = True
        MsgBox "A valid value is required for " & controltext, vbCritical, controltext & " Required"
        controlname.SetFocus
    
    End If
End Function
  
 Private Sub ChkConfirm_Click()
If CheckIfEmpty(cboAgent, cboAgent, "Agent Name") Then Exit Sub
If CheckIfEmpty(cboAuditor, cboAuditor, "Auditor Name") Then Exit Sub
If CheckIfEmpty(txtVerintID, txtVerintID, "Verint ID") Then Exit Sub
If CheckIfEmpty(cboCallType, cboCallType, "Call Type") Then Exit Sub
If CheckIfEmpty(cboDirection, cboDirection, "Call Direction") Then Exit Sub
If CheckIfEmpty(CboChannel, CboChannel, "Channel") Then Exit Sub
If CheckIfEmpty(txtCallDate, txtCallDate, "Date of Call") Then Exit Sub
If CheckIfEmpty(txtObs, txtObs, "Date of Observation") Then Exit Sub
If CheckIfEmpty(txtdtfedback, txtdtfedback, "Date Feedback") Then Exit Sub
End sub

Any help will be much appreciated.

Thanks

 

[Minty]

You may need to refer to the controls value using Me.YourControlName when calling the function.

 

[The_Doc_Man]

If both of those subroutines are in the class module for that form, you might not need the Me.controlname, but it is never wrong, so I'm with Minty at least on general principles.

The problem with checking combo boxes is that if you want to see if the combobox has a selection (i.e. not blank), that isn't the right test. To test for a combo box to have a selection, you check for the cbobox.ListIndex to be -1 (no selection) or some number from 0 to n-1, where you have n entries in the .Rowsource table/query.

You can also shorten that call and leave it totally unambiguous. You name the control twice, but you don't need it twice. Hint: Control type-names are visible in the Object Browser under acControlType.

 Private Function CheckIfEmpty(MyCtl as Access.Control, MyCtlName as String) As Boolean

    Dim CIE as Boolean

    Select Case MyCtl.ControlType
      Case acTextBox
           CIE = ( ( MyCtl = "" ) or ( IsNull( MyCtl ) )
      Case acComboBox
           CIE = MyCtl.ListIndex = -1
      Case acListBox                         'presumes ListBox allows multi-select
            CIE = MyCtl.Selected.Count = 0
      Case else
            CIE = False
    End Select    

    If CIE Then
        MyCtl.SetFocus
        MsgBox "A value or selection is required for " & MyCtlName, vbOKOnly + vbCritical, "Value Required"
    End If

    CheckIfEmpty = CIE

End Function

The way I wrote this is slightly misleading in this way: You need only have as many different control types in that select ladder as are actually represented on the form. I.e. if you didn't have any list boxes, you wouldn't need to test it, so you could leave the code out. On the other hand, if you wanted to generalize, then understand another factor: Some controls cannot be null. For instance, I think an OptionGroup ALWAYS has a value, never a null - and the corresponding option button always has a constant value, which is sort of useless to test. Then there are the Rectangle and Line controls, which don't have a value at all. Which is why you have a catch-all in the Case Else rung of that dispatch ladder.

 

[Galaxiom]

One trick to simplify code is call the function in a loop through the Controls collection. The controltext parameter can be stored in the Tag property of the control and read directly from inside the function when you use the technique Doc shows by passing the control object itself instead of the name.

 

[aman]

Hello, I am getting invalid use of null runtime error when I leave a control blank??

If both of those subroutines are in the class module for that form, you might not need the Me.controlname, but it is never wrong, so I'm with Minty at least on general principles.

The problem with checking combo boxes is that if you want to see if the combobox has a selection (i.e. not blank), that isn't the right test. To test for a combo box to have a selection, you check for the cbobox.ListIndex to be -1 (no selection) or some number from 0 to n-1, where you have n entries in the .Rowsource table/query.

You can also shorten that call and leave it totally unambiguous. You name the control twice, but you don't need it twice. Hint: Control type-names are visible in the Object Browser under acControlType.

 Private Function CheckIfEmpty(MyCtl as Access.Control, MyCtlName as String) As Boolean

    Dim CIE as Boolean

    Select Case MyCtl.ControlType
      Case acTextBox
           CIE = ( ( MyCtl = "" ) or ( IsNull( MyCtl ) )
      Case acComboBox
           CIE = MyCtl.ListIndex = -1
      Case acListBox                         'presumes ListBox allows multi-select
            CIE = MyCtl.Selected.Count = 0
      Case else
            CIE = False
    End Select    

    If CIE Then
        MyCtl.SetFocus
        MsgBox "A value or selection is required for " & MyCtlName, vbOKOnly + vbCritical, "Value Required"
    End If

    CheckIfEmpty = CIE

End Function

The way I wrote this is slightly misleading in this way: You need only have as many different control types in that select ladder as are actually represented on the form. I.e. if you didn't have any list boxes, you wouldn't need to test it, so you could leave the code out. On the other hand, if you wanted to generalize, then understand another factor: Some controls cannot be null. For instance, I think an OptionGroup ALWAYS has a value, never a null - and the corresponding option button always has a constant value, which is sort of useless to test. Then there are the Rectangle and Line controls, which don't have a value at all. Which is why you have a catch-all in the Case Else rung of that dispatch ladder.

 

[aman]

I have written the following code and its giving me invalid use of null runime error. I only have comboboxes and textboxes on the form that I need to check.

 Private Sub ChkConfirm_Click()
If CheckIfEmpty(cboAgent, cboAgent, "Agent") Then Exit Sub
If CheckIfEmpty(cboAuditor, cboAuditor, "Auditor") Then Exit Sub
If CheckIfEmpty(txtVerintID, txtVerintID, "Verint ID") Then Exit Sub
If CheckIfEmpty(cboCallType, cboCallType, "Call Type") Then Exit Sub
If CheckIfEmpty(cboDirection, cboDirection, "Call Direction") Then Exit Sub
If CheckIfEmpty(cboAuditpurpose, cboAuditpurpose, "Activity") Then Exit Sub
If CheckIfEmpty(cboObjectiveID, cboObjectiveID, "Objective") Then Exit Sub
If CheckIfEmpty(cboSubTypeID, cboSubTypeID, "Call Sub Type") Then Exit Sub
If CheckIfEmpty(CboChannel, CboChannel, "Channel") Then Exit Sub
If CheckIfEmpty(txtCallDate, txtCallDate, "Date of Call") Then Exit Sub
If CheckIfEmpty(txtObs, txtObs, "Date of Observation") Then Exit Sub
If CheckIfEmpty(txtdtfedback, txtdtfedback, "Date Feedback") Then Exit Sub
 end sub

 Private Function CheckIfEmpty(MyCtl As Access.Control, MyCtlName As String, controltext As String) As Boolean
     Dim CIE As Boolean
     Select Case MyCtl.ControlType
      Case acTextBox
           CIE = MyCtl = "" Or IsNull(MyCtl)
      Case acComboBox
           CIE = MyCtl.ListIndex = -1
      Case Else
            CIE = False
    End Select
     If CIE Then
        MyCtl.SetFocus
        MsgBox "A value or selection is required for " & controltext, vbOKOnly + vbCritical, "Value Required"
    End If
     CheckIfEmpty = CIE
 End Function

 

[Frothingslosh]

What happens if you replace

CIE = MyCtl = "" Or IsNull(MyCtl)

with

CIE = (Nz(MyCtl, "") = "")

?

Also, it's good to let us know which line gets highlighted when you click 'Debug' after getting the error message.

 

[aman]

Still the same error message. It comes up for all the controls which are left blank.

 Private Sub ChkConfirm_Click()
If CheckIfEmpty(cboAgent, cboAgent, "Agent") Then Exit Sub
If CheckIfEmpty(cboAuditor, cboAuditor, "Auditor") Then Exit Sub
If CheckIfEmpty(txtVerintID, txtVerintID, "Verint ID") Then Exit Sub
If CheckIfEmpty(cboCallType, cboCallType, "Call Type") Then Exit Sub
If CheckIfEmpty(cboDirection, cboDirection, "Call Direction") Then Exit Sub
If CheckIfEmpty(cboAuditpurpose, cboAuditpurpose, "Activity") Then Exit Sub
If CheckIfEmpty(cboObjectiveID, cboObjectiveID, "Objective") Then Exit Sub
If CheckIfEmpty(cboSubTypeID, cboSubTypeID, "Call Sub Type") Then Exit Sub
If CheckIfEmpty(CboChannel, CboChannel, "Channel") Then Exit Sub
[COLOR=yellowgreen]If CheckIfEmpty(txtCallDate, txtCallDate, "Date of Call") Then Exit Sub
[/COLOR]If CheckIfEmpty(txtObs, txtObs, "Date of Observation") Then Exit Sub
If CheckIfEmpty(txtdtfedback, txtdtfedback, "Date Feedback") Then Exit Sub
end sub

 

[Frothingslosh]

Okay, which line in which procedure is throwing the error?

You might also try replacing the button's code with this:

Private Sub ChkConfirm_Click()
  
Dim ctrl As Control
Dim CIE As Boolean
  
     For Each ctrl In Me.Controls
        Select Case ctrl.ControlType
            Case acTextBox
                CIE = (Nz(ctrl.Value, "") = "")
            Case acComboBox
                CIE = (ctrl.ListIndex = -1)
            Case Else
                CIE = False
        End Select
        If CIE Then
            MsgBox "A value or selection is required for " & ctrl.Controls.Item(0).Caption, vbOKOnly + vbCritical, "Value Required"
            Exit For
        End If
    Next ctrl
End Sub

Please note that if your labels aren't actually linked to their controls, then ctrl.Controls.Item(0).Caption will throw an error. In that case, I'd either link them up or else put the value you're using as 'controltext' into each control's Tag property and then reference ctrl.Tag instead.

Other than that, I have tested this code and it works fine on my end.

Edit: This is just a quick and dirty check. A better way to go about it would be to have the system flag ALL the controls that have been left blank, and then report them all in one shot. There are any number of ways to do that, from bitwise comparison to flags to arrays to an ongoing string variable.

 

[aman]

The error is coming up on the Date textboxes if they are left blank. How can I get rid of the error message "invalid use of null". Thanks

 

[jdraw]

How can I get rid of the error message "invalid use of null"

You could try something along this line

If IsNull(YourDateControl) then
 do something
else
'it isn't null so you could check for a valid date
   If IsDate(YourDateControl) then
  'it's a valid date so do something
   else
  'its  not valid  now What???
  end if
end if