simple coding question

[AnOddExperiment]

Hi everyone, still a newbie coder, but I was wondering if you could look at this and tell me why it returns an error:

Dim ctl As Control
    Dim bolComplete As Boolean
    bolComplete = True
    For Each ctl In Controls
        If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
            If ctl.Value = "" or ctl.Value is Null or ctl.Value is Nothing Then
                bolComplete = False
            End If
        End If
    Next

I'm trying to write a bit of code that will not allow the user to move to the next part of the form without first completing the entire form. The part I'm having trouble with defining what null, nothing, and an empty string means in terms of VBA. Thanks so much!

 

[GaryPanic]

Huh.. why not use the cell values and set them to allow null - false ...

 

[Darth Vodka]

Hi everyone, still a newbie coder, but I was wondering if you could look at this and tell me why it returns an error:

Dim ctl As Control
    Dim bolComplete As Boolean
    bolComplete = True
    For Each ctl In Controls
        If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
            If ctl.Value = "" or ctl.Value is Null or ctl.Value is Nothing Then
                bolComplete = False
            End If
        End If
    Next

I'm trying to write a bit of code that will not allow the user to move to the next part of the form without first completing the entire form. The part I'm having trouble with defining what null, nothing, and an empty string means in terms of VBA. Thanks so much!

try if nz(ctl.Value,"")=""

nothing is an empty object, not relevant to checking the value of something

and TypeOf, try ctl.ControlType=acTextBox type of thing

and stating the error might help us

 

[missinglinq]

Also, the proper syntax in VBA for

ctl.Value is Null

is

IsNull(ctl.Value)

 

[AnOddExperiment]

try if nz(ctl.Value,"")=""

nothing is an empty object, not relevant to checking the value of something

and TypeOf, try ctl.ControlType=acTextBox type of thing

and stating the error might help us

Thanks Darth,

The error is runtime error 424 object required, and it goes away if I remove the portion of "or ctl.Value is Null or ctl.Value is Nothing" from the if criteria. If I do that, however, the code does not fire the way I want it to. It simply executes the rest of the code as if nothing happened. Here, let me show you the rest of the code.

Dim ctl As Control
    Dim bolComplete As Boolean
    bolComplete = True
    For Each ctl In Controls
        If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
            If ctl.Value = "" Or ctl.Value Is Null Or ctl.Value Is Nothing Then
                bolComplete = False
            End If
        End If
    Next
        
    Dim SQLDel As String
    Dim SQLApnd As String
    If bolComplete = False Then
        MsgBox "Please fill out all portions of this form."
        Exit Sub
    Else
blah blah blah

There is a bunch of logic written under that last 'else' (which works fine) which I really need to not fire if the form is not completed, to answer your question Gary, and this is tied to the on click event of an action button. I'm not familiar with the sytax of the code you referenced, Darth. How would I substitute it into the code I wrote? I'm still a little fuzzy on what the difference is between null, nothing, and an empty string if you could elaborate a bit.

 

[Darth Vodka]

How would I substitute it into the code I wrote?

something like

Sub TestControlsComplete()
    Dim ctl As Control
    
    Dim AreControlsComplete As Boolean
    AreControlsComplete = True
    
    For Each ctl In Forms("frm_Reports").Controls
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
            If Nz(ctl.Value, "") = "" Then
                AreControlsComplete = False
                Exit For
            End If
        End If
    Next
    
    MsgBox AreControlsComplete

End Sub

I'm still a little fuzzy on what the difference is between null, nothing, and an empty string if you could elaborate a bit.

nothing is an empty object
null is an empty variable
"" is an empty string, a string of zero length