Check al controls onLoad event - Error 438

[Snowflake68]

My database is for creating sales quotes which can be retrieved to continue on where it was previously saved. So when I open the form to edit the quote I only want the controls enabled if they previous entered something in them and the others disabled until they then carry on selecting values which in turn will enable the other controls.

I have the following code that I am trying to use to check to see if all the combo boxes and text boxes on my form have a value and if they do enable them.

    Dim ctrl As Control
        For Each ctrl In Me.Controls
            If Not IsNull(ctl.Value) Then
                ctrl.Enabled = True
            End If
        Next

But i get a the runtime error 438 object required pointing at this line of code

            If Not IsNull(ctl.Value) Then

If possible, I also need to find away of enabling the next empty control after the very last enabled one so that they can continue on where they left off.

Can someone help please as I am no expert in VBA
Thanks

 

[arnelgp]

Inly check if the control is textbox, combo, checkbox etc. Dont include label, line etc

If typeof ctrl is textbox or typeof ctrl is combobox ... then
' ok to proceed

 

[isladogs]

Several possible causes
For example, your form may include controls that cannot be enabled/disabled.
Suggest you limit your code to combos & textboxes

Add another if line or combine with existing line

if me.ctrl.type = acTextbox or me.ctrl.type = actextbox then
....
End if

If it errors again, try

If Nz(ctrl.value,"")<>"" then

Note that I'm typing on my phone so typos possible

EDIT I see arnelgp was a bit faster replying

 

[missinglinq]

The particular error, here, is that you have

Dim ctrl As Control

and then, the line causing the error

If Not IsNull(ctl.Value) Then

Your variable is missing the r in the second instance, above.

Also, as has been pointed out, you need to limit your checking of Controls to those that can actually hold data, and hence can be Null.

Here's an example of what everyone is talking about:

Dim ctrl As Control

For Each ctrl In Me.Controls
    Select Case ctrl.ControlType
        Case acTextBox, acComboBox
            If Not IsNull(ctrl.Value) Then
                ctrl.Enabled = True
             End If
    End Select
Next ctrl

You should also note that by putting this code in the Form_Load event, it will only apply to the first Record that is displayed...it will not do this check on other Records as you move from one to another! For it to do this check on all Records, as you access them, it needs to be in the Form_Current event.

Linq ;0)>

 

[The_Doc_Man]

The "For Each" syntax touches EVERY CONTROL on a form including lines, rectangles, labels, and a few other types that do not have the .Value property. You cannot test .Value until you test what kind of control you just selected.

EDIT: Linq beat me to it. He posted essentially what I was going to post.

 

[Snowflake68]

I have tried everyone's suggestions and managed to get the code below to work. However it only works for combo boxes and not the text boxes. I think its because some of my text boxes aren't input boxes but I have calculations in the data source so I will check the input ones separately as I only have a few of them.

Dim ctrl As Control

For Each ctrl In Me.Controls
    Select Case ctrl.ControlType
'        Case acComboBox, acTextBox
        Case acComboBox
            If Not IsNull(ctrl.Value) Then
                ctrl.Enabled = True
             End If
    End Select
Next ctrl

Thank you to everyone for their help. I just need to find out how to enable the next control after the last enabled one. Any ideas?

 

[Snowflake68]

You should also note that by putting this code in the Form_Load event, it will only apply to the first Record that is displayed...it will not do this check on other Records as you move from one to another! For it to do this check on all Records, as you access them, it needs to be in the Form_Current event

The user does not move through any other records on this form so this is not an issue. But thanks for the information as it may come in handy in the future.

Also a BIG thank you for supplying me with the code and now works without errors.

 

[missinglinq]

Dim ctrl As Control

For Each ctrl In Me.Controls
    Select Case ctrl.ControlType
'        Case acComboBox, acTextBox
        [COLOR="Red"]Case acComboBox[/COLOR]
            If Not IsNull(ctrl.Value) Then
                ctrl.Enabled = True
             End If
    End Select
Next ctrl

The line in red was what was limiting your code to Comboboxes!

Linq ;0)>

 

[Snowflake68]

The line in red was what was limiting your code to Comboboxes!

Linq ;0)>

Yes thats correct, I want to limit it to just combo boxes so that I can deal with the text boxes separately because some of the text boxes are input ones but others contain calculations (which causes the error) hence needing to exclude them. However I have now used the Tag property to group the input text boxes together and then used the code below to check just the input text boxes.

'Check Text Boxes in Group T
Dim ctrlText As Control
For Each ctrlText In Me.Controls
    Select Case ctrlText.ControlType
        Case acTextBox
            If ctrlText.Tag = "GroupT" Then
                If Not IsNull(ctrlText.Value) Then
                    ctrlText.Enabled = True
                End If
            End If
    End Select
Next ctrlText

I still need to find a way of finding how to identify the last enabled control so that I can enable the next control ready for the user to continue with the quote. Not sure this is possible so they may just have to reselect from the very last enabled control to activate the after update event which will then enable the next control.

 

[jdraw]

I still need to find a way of finding how to identify the last enabled control

Just curious. How are the textboxes named?
You might try debug.print ctrlText.name & " " & ctrlText.enabled, at least for debugging/testing. You know the logic and readers do not, so stepping thru the code and seeing the debug output may give you some insight.

 

[Snowflake68]

Just curious. How are the textboxes named?
You might try debug.print ctrlText.name & " " & ctrlText.enabled, at least for debugging/testing. You know the logic and readers do not, so stepping thru the code and seeing the debug output may give you some insight.

I'm using the Tag property (see attached image) on the Text Boxes and just putting GroupT and then referencing that.


I have over 100 controls on the form so I dont really want to have to write code to check each one individually to see if it is enabled or not but I might have to if I cant find another way.

 

[jdraw]

If you step through some of your code and use the debug statement I suggested, you might get some ideas for determining the last enabled textbox -which is what I thought your issue was.

Perhaps I have misunderstood your issue and intent.