loop through all Non Blank textboxs on a form

[Micron]

OK - my recollection was the biggest issue. It's not ctl.Type - it's ctl.ControlType


However, I cannot get the expected result on the logical test AND <> 0 in conjunction with the controltype test unless I nest it as 2 If blocks. Thus the Select Case construct becomes decidedly more elegant. That is not all. There would be an issue if a check box was initially Null, as this cannot be compared to 0 or any other value. So the following seems to work:

Dim ctl As Control

On Error Resume Next

For Each ctl In Me.Controls
  Select Case ctl.ControlType
    Case acTextBox, acComboBox
      ctl.Enabled = Not IsNullEmpty(ctl)
    Case acCheckBox
      ctl.Enabled = Not Nz(ctl <> 0, False) 
  End Select
Next

It works for me if the check is Null, True or False, bound or not. Sorry - I usually warn about code that's untested, but in my code post I didn't declare that.

[gint32]

Thanks very much for your much appreciated efforts, As yes it seems to almost work , the only issue I with it is that the code greys out the blanks .
Unfortunately, it over my head as whats actually happening with the code you supplied, so I can't figure how to swap the code around, see below for what i am using.

I tried swapping one line for this ..

'If IsNull(ctl) Or ctl = "" Then IsNullEmpty = False

Private Sub Command6_Click()

'>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Dim ctl As Control

On Error Resume Next
Stop
For Each ctl In Me.Controls
  Select Case ctl.ControlType
    Case acTextBox, acComboBox
      ctl.Enabled = Not IsNullEmpty(ctl)
    Case acCheckBox
      ctl.Enabled = Not Nz(ctl <> 0, False)
  End Select
Next

End Sub
'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


Public Function IsNullEmpty(ctl As Control)
IsNullEmpty = False
If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True
'If IsNull(ctl) Or ctl = "" Then IsNullEmpty = False
End Function

[gint32]

OK - my recollection was the biggest issue. It's not ctl.Type - it's ctl.ControlType
However, I cannot get the expected result on the logical test AND <> 0 in conjunction with the controltype test unless I nest it as 2 If blocks. Thus the Select Case construct becomes decidedly more elegant. That is not all. There would be an issue if a check box was initially Null, as this cannot be compared to 0 or any other value. So the following seems to work:

Dim ctl As Control

On Error Resume Next

For Each ctl In Me.Controls
  Select Case ctl.ControlType
    Case acTextBox, acComboBox
      ctl.Enabled = Not IsNullEmpty(ctl)
    Case acCheckBox
      ctl.Enabled = Not Nz(ctl <> 0, False) 
  End Select
Next

It works for me if the check is Null, True or False, bound or not. Sorry - I usually warn about code that's untested, but in my code post I didn't declare that.

Many thanks for your suggestion Once Again.

Not that I fully understand how your code works. But, I amended the lines by removing the >>>.. Not..<<< in your suggested ,but now I am stumped as it now throws an error within the function..specifically at the following

If IsNull(ctl) Or ctl = ""

..stating that the expression You entered has a field, control, or property name that Microsoft access can't find. Doesn't make sense as nothing is hidden (for it not to find)...Hey, What I highly informative error message as always from M.S ...So my question is ....Can anyone suggest how to fix or advise/amend to display each controls name as it loops through them......thanks

[gint32]

Many thanks everyone who contributed to this issue, I finally got a working version going(although not the greatest of coding structure I must admit) in my part. So for a continuation of this please see my post on another most helpful forum at http://www.utteraccess.com/forum/ind...wtopic=2044083. I have also uploaded a working test solution for anyone who might be interested in using

[Micron]

this seems to work in you db copy

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Enabled = IsNullEmpty(ctl)
Case acCheckBox
ctl.Enabled = Nz(ctl <> 0, False)
End Select
Next

Reversing the function value probably would do the same thing. If you're happy with the current state, that's all that really matters I guess.
Good luck!

[ssanfu]

OK - my recollection was the biggest issue. It's not ctl.Type - it's ctl.ControlType

@Micron,
I can't believe that I also missed that.
As a matter of fact, I just wrote code similar about a week ago.

[Micron]

@Micron,
I can't believe that I also missed that.
As a matter of fact, I just wrote code similar about a week ago.

A sign of age more so than the ages? That's my excuse.

[ssanfu]

Agreed!!!!