Invalid use of Null - Form control values

[misterpox]

Using Access 2000, OS = Win2KPro

The answer to this is probably simple, but I'm stuck on figuring this out . I have a form with several textbox controls in it that allow a user to input values. Prior to allowing a user to submit their entries in the form to a table, I want to check that they have entered something in at least one of the textboxes. So I wrote the following to loop through all of the textbox controls to check their values. I'm testing tihs code with all of the textbox fields empty on the form.

But I'm getting, "Invalid use of Null", and, "Object Required", errors when I attempt to get the control's value. Does anyone know what's wrong here? I have 'ctl2.Value' as a watch expression and the value in the Watches windows shows as Null when the textbox is empty.

Code: (showing only the part to check for empty entries)
---------------------------------------------------------------------------------
Option Compare Database

Private Sub cmd_AddNewRecord_Click()
On Error GoTo Err_cmd_AddNewRecord_Click

Dim mydb As Database, myrset As Recordset, Numworked, i As Integer
Dim ctl As Control, currentform As Form, ctlvalue As String
Dim ctl2 As TextBox

Set currentform = Forms!MyCurrentForm
i = 0 ' i is counter for number of controls, used to check other stuff later
For Each ctl2 In currentform
MsgBox ctl2.ControlName
MsgBox ctl2.Value 'Get invalid use of Null at this line
'Get Object Required error in IF statement
If Trim(ctl2.Value) = "" Or ctl2.Value Is Null Then
i = i + 1
End If
Next

MsgBox i

Exit_cmd_AddNewRecord_Click:
Exit Sub
Err_cmd_AddNewRecord_Click:
MsgBox err.Description
Resume Exit_cmd_AddNewRecord_Click

End Sub

 

[Norie]

Is ctl2 actually being set to the controls?

Should it not be

For Each ctl2 In currentform.Controls

BTW I wouldn't type ctl2 as Textbox just use Control.

 

[misterpox]

Norie,

I set ctl2 as TexBox because If I only set it to Control, the For loop is evaluating every control, including labels, etc. I am only interested in the textbox values, so I thought that declaration would limit the control selections. I get the same errors even if I declare it as Control though.

I changed the for loop to what's below. I still get Invalid use of Null at the "MsgBox ctl2.Value" statement, and if I comment that out, get an 'Object Required' error at the if statment, ugh. Any other ideas?

-------------------------------------------------------------------
Dim ctl As Control, currentform As Form, ctlvalue As Variant
Dim ctl2 As TextBox
Set currentform = Forms!AddRecords_DataTable
i = 0
For Each ctl2 In currentform.Controls
MsgBox ctl2.ControlName
ctl2.SetFocus
ctlvalue = ctl2.Value
MsgBox ctl2.Value
If ctl2.Value = "" Or ctl2.Value Is Null Then
i = i + 1
End If
Next

 

[Norie]

I set ctl2 as TexBox because If I only set it to Control, the For loop is evaluating every control, including labels, etc. I am only interested in the textbox values, so I thought that declaration would limit the control selections

Setting it to TextBox will not restrict it.

Try this:

Dim ctl As Control, currentform As Form, ctlvalue As Variant


    Set currentform = Forms!AddRecords_DataTable
    
    I = 0
    
    For Each ctl In currentform.Controls
    
      If TypeOf ctl Is TextBox Then
                MsgBox ctl.ControlName
                
                ctl.SetFocus
                ctlvalue = ctl.Value
                'MsgBox ctl2.Value
            If ctl.Value = "" Or IsNull(ctl.Value) Then
                I = I + 1
            End If
        End If
    Next

 

[misterpox]

Norie,

Thank you!! IsNull is what I needed (I feel like a knucklehead for not thinking of that), and the TypeOf check on the control works beautifully. Thank you very much for your help.