Checking if control is blank doesn't work with if ctl.value= ""

[Margarita]

Hello,
On my form, I designated the textboxes and comboboxes which the user has to fill in in order to save a record by making their back color yellow. In the on click event of the Save button, I want to check if any of the yellow controls are blank:

Dim ctl As Control
For Each ctl In Me.Controls
If Not ctl.Name = "SaveInvoiceRecord_button" And Not ctl.Name = "Box95" Then
If ctl.BackColor = 10092543 And Len(ctl.Value < 1) Then
MsgBox "Please fill in all the required fields."
GoTo endsaving
End If
End If
Next ctl

Note: the first If statement excludes 2 controls that should not be checked because they don't support either the back color property or the value property. When I try to hit save, I get error 438: object doesn't support this property or method. The line

If ctl.BackColor = 10092543 And Len(ctl.Value < 1) Then

is highlighted. All of the controls are either text boxes or comboboxes. I tried just the backcolor criteria in that line and that works. It has to be the value criteria. I also tried the following, none of which work:
if ctl.value= ""
if ctl= "'
if ctl.value= vbnullstring
if ctl= nullstring
if len(ctl.value)=0

Can anyone tell me why my textbox and combobox controls aren't supporting this property?

Thank you.

 

[AccessMSSQL]

Try filtering your ctls by using this:


If TypeOf ctl is TextBox or TypeOf ctl is combobox

instead of excluding them by name.

 

[Margarita]

You got the <1 in the wrong place but in order to handle possible nulls it should be:

Len(ctl.Value & vbNullString) < 1

Hi Bob, I just did what you said and it's still giving the error. Here is the exact code:

Dim ctl As Control
For Each ctl In Me.Controls
If Not ctl.Name = "SaveInvoiceRecord_button" And Not ctl.Name = "Box95" Then
If ctl.BackColor = 10092543 And Len(ctl.Value & vbNullString) < 1 Then
MsgBox "Please fill in all the required fields."
GoTo endsaving
End If
End If
Next ctl

Thank you!

 

[Margarita]

Try filtering your ctls by using this:


If TypeOf ctl is TextBox or TypeOf ctl is combobox

instead of excluding them by name.

I did not know about TypeOf. Thank you!

 

[Margarita]

But if there are more and you only want certain ones, another good way is to use the TAG property of the controls and then you only have to do one check

If ctl.Tag = "DoSomething" Then
' do your stuff here
End If

Thank you for the suggestion of using tags. I got it to work this way:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "Required" Then
If Len(ctl.Value & vbNullString) < 1 Then
MsgBox "Please fill in all the required fields."
GoTo endsaving
End If
End If
Next ctl

Thank you!!

 

[AccessMSSQL]

Try using if ctl.text = ""
Correction....oops. I meant ctl.value = "". This works for me for both combobox and textbox. I think you are picking up other controls in your code.