Solved: Access VBA not executing properly

[pincivma]

Hi Folks

When I execute the code (see the bottom of this page), I get an error on the line

If Ctrl = "" Then

What I want to do is this: Once my form is opened, I want all the controls in the form that are empty, (i.e. there is no text or number in them) to disappear. I tried the following code but no luck.

I get an error that says " Run time error; Object doesn't support this property or method"

Can someone figure this out??

Thanks,

Mario



Private Sub Form_Load()
Dim Ctrl As Control
Dim Frm As Form
Set Frm = Forms![Employee Summary]
For Each Ctrl In Frm.Controls
'If the control is empty (i.e. has no text or numbers)
If Ctrl = "" Then
Ctrl.Visible = False
Else
Ctrl.Visible = True
End If
Next
End Sub

[mdmackillop]

Hi Mario,
Welcome to VBAX.
If you select your code and click the VBA button, it formats your code as shown, making it more readable.

I'm not an Access expert, but I do know that there are differences between empty strings, null values and Nothing. In this case, try

If IsNull(Ctrl) Then

Regards
MD

[pincivma]

Hi mdmackillop

Thanks, the code now works perfectly. However, it only deletes those controls that are empty. It does not delete the controls that have a $0.00 in them. How do you modify the code to include this. I have tried

If Ctrl = 0.00 then
code goes here

But once again I get an error. Any suggestions??

Mario

[mdmackillop]

Private Sub Form_Load()
Dim Ctrl As Control
Dim Frm As Form
Set Frm = Forms![form1]
On Error Resume Next
For Each Ctrl In Frm.Controls
If IsNull(Ctrl) Then Ctrl.Visible = False
If Ctrl = 0 Then Ctrl.Visible = False
Next
End Sub

[pincivma]

Hi mdmackillop

I tried your code and it works well. I have one question for you. I also tried the code (see below) and it did not work. But then I took out the 2 End If statements and used only one line for the code like you suggested here.

If IsNull(Ctrl) Then Ctrl.Visible = False
If Ctrl = 0 Then Ctrl.Visible = False

With the above lines the code works great. Why not the way I have it below?? To me they seem the same.




Private Sub DeletesAllEmptyCtrlsAndCtrlsWithzeros_Click()

Dim Ctrl As Control
Dim Frm As Form
Set Frm = Forms![MainFormByDate]
On Error Resume Next
For Each Ctrl In Frm.Controls
If IsNull(Ctrl) Then
Ctrl.Visible = False
If Ctrl = 0 Then
Ctrl.Visible = False
End If
End If
Next
End Sub

Mario

[mdmackillop]

Your If IsNull(Ctrl) prevents the test for ctrl = 0 from occurring. You need an Else statement. When writing code, make sure that it is properly indented. In this case, the indenting shows that the 0 test is dependent upon a successful Null value, and these are mutually exclusive.


For Each Ctrl In Frm.Controls
If IsNull(Ctrl) Then
Ctrl.Visible = False
Else
If Ctrl = 0 Then
Ctrl.Visible = False
End If
End If
Next

[pincivma]

Hi mdmackillop

Thanks a million!! with your help, I now understand where I went wrong.

Mario

[geekgirlau]

Hi Mario,

If you are just testing for either "" or 0, I would recommend the following:


For Each Ctrl In Frm.Controls
If nz(Ctrl,"") Then
Ctrl.Visible = False
End If
Next Ctrl


Given that you are testing for both, it's probably no quicker. In Access, I find that I use NZ constantly - you are telling the database to treat Null and "" as the same value, so essentially you only have to perform a single check.

[pincivma]

Hi geekgirlau

I did try your code but it deleted everything on my form, including controls with text in it. How come??

Mario

[geekgirlau]

Hi Mario,

Sorry - typo:


For Each Ctrl In Frm.Controls
If nz(Ctrl,"")="" Then
Ctrl.Visible = False
End If
Next Ctrl


By the way, I wasn't advocating using this code in place of MD's sample. MD's code will cope with Null and zero, which this code won't. I merely wished to point out another option that could be usefull in the future.

[pincivma]

Hi geekgirlau

Thanks for the correction. I can see many situations where code could be very useful. Thanks again for your input.

Mario