Hide Label & Text Control When Field Empty

[elastreto]

Hello,

I have been trying, so far in vain, to hide the label and text control in a form when the text control is empty/null.

I have looked at past postings and tried the code below, however I am getting Run-Time Error 438, stating that the object doesn’t support this property or method.

I don't know if I am writing the code to the wrong place or if I have an error in the code itself. Any help is appreciated!!

(I am running Access 2002; The name of my label and text control are both "contractdate")

 
Private Sub Form_Current()
 
If IsNull(Me!contractdate) Then
    Me!contractdate.Visible = False
    Me!LabelName.Visible = False
Else
    Me!contractdate.Visible = True
    Me!LabelName.Visible = True
 
End If
 
End Sub

[Denburt]

Down and dirty but it should take care of your issue.

Option Compare Database
Option Explicit
Dim MyCnt As Integer
Private Sub Form_Current()
MyCnt = MyCnt + 1
If MyCnt <= 1 Then Exit Sub
 
If IsNull(Me!contractdate) Then
    Me!contractdate.Visible = False
    Me!LabelName.Visible = False
Else
    Me!contractdate.Visible = True
    Me!LabelName.Visible = True
End If
End Sub
 

[elastreto]

It worked. Thanks a million!

[Denburt]

You're welcome, glad I could help.

[missinglinq]

I'm confused as to why the OP's original code didn't work; it works just fine for me in ACC2000 and ACC2003. I'm also curious as to why adding a dummy variable (MyCnt) makes any difference.

Linq ;0)>

[Denburt]

I'm confused as to why the OP's original code didn't work; it works just fine for me in ACC2000 and ACC2003. I'm also curious as to why adding a dummy variable (MyCnt) makes any difference.

Linq ;0)>

According to the help file (my bolding for emphasis) "This event occurs both when a form is opened and whenever the focus leaves one record and moves to another. Microsoft Access runs the Current macro or event procedure before the first or next record is displayed."

So if the current event runs BEFORE the record is displayed how can you tell if the field is null or not? Looking at it another way and explaining the error when the form is first opened it runs the current event and no record is there so an error is raised. The Variable is declared for the form as a whole so it retains its value and you skip the current event for this statement only on the very first run after the form is opened, when the form is opened the variable is 0 the current event typically runs I think 3 times when first opened (depending on various circumstances) we only need it to run once for this portion of code. :)

Any questions?

[missinglinq]

"the current event runs BEFORE the record is displayed"

but not BEFORE the record is actually loaded and it's data available! Access Help is not noted for its clarity, or even for its accuracy in all cases! As I said, the code worked for me in ACC2000 and ACC2003, and similar code has been used since Access time began to do this very task. It's how conditional formatting was accomplished before ACC2000 came along.

The code does need to check for the presence of a new record and make the two controls visible if this is the case.

Private Sub Form_Current()
 If Not Me.NewRecord Then
  If IsNull(Me!ContractDate) Then
   Me!ContractDate.Visible = False
   Me!LabelName.Visible = False
  Else
   Me!ContractDate.Visible = True
   Me!LabelName.Visible = True
  End If
 Else
  Me!ContractDate.Visible = True
  Me!LabelName.Visible = True
 End If
 End Sub

The name of my label and text control are both "contractdate")

My guess is that the original error cited might possibly have to do with this statement by the OP. If he started out this code trying to hide the label first, and its name was ContractDate, as he stated, it would raise that very error, because a label cannot be Null. The label's name was changed, you'll note, to LabelName before he posted his code.

[Denburt]

My apologies for not replying sooner, as you indicated with code posted it should work as you stated. I know that I have had issues in the past and I used the work around that I posted but for this situation I can't seem to see where my thoughts were, now that I review it I am thinking possible ADD or just idiocy (regarding my last post). ;)

In this case I do believe your method would be the approach needed to accomplish his task as requested.

Thanks missinglinq for your clarifications.

[missinglinq]

As I said, Access Help is not noted for its clarity, or even for its accuracy! You have to remember this at all times! My guess is that the most junior geeks on Billy Bob Gate's development teams are always given the job of documentation! And, of course, many of the events have names that are counter-intuitive! Common sense would tell you that the AfterUpdate event would occur after the update, but of course, that's not true! You can change data, for instance by formatting it, in this event before the record is actually updated! And when the LostFocus event fires, the control hasn't lost focus! Just try to set the focus back on a control thru its LostFocus event; can't be done! And like the Everyready Bunny, the list goes on and on! My approach towards Help is basically the same as that used by our intelligence services, back before politicians took them over; Trust and Verify!

And don't feel bad about not being able to understand what you were thinking about in a post from over a month ago; we've all been there! That's the very reason why documentation is the 4th, 5th and 6th most important thing in software development, right behind backing things up, which is 1st, 2nd and 3rd!

Later!

Linq ;0)>