How do you hide "TextBox" labels on a form if record is null. 1

[Guest_imported]

Is there a way to hide "text box labels" on a form if that "text box record" is null? If there's no record then I don't want a label showing up on the form. I'll even settle for a color change (light gray). I know this is possible but I just can't figure out how it's done.
The fields are hyperlinks if thats any help....

I am using Access 2000 on Windows 2000.

Big fan of Tek-Tips...

Thanks
Mark

 

[BoxHead]

I'm using '97, but I would be surprised if they've changed this.

On the form's current event, you would use something like

  If Nz(PrpCity, "")= "" Then
    lblPrpCity.Visible = False
  Else
    lblPrpCity.Visible = True
  End If

If the user could be deleting or entering data in the textbox, you'd probably want to run the same code on the textbox's AfterUpdate.


HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[CHMPB]

Ok, one thing I am not understanding... The comments in
Your script:
Nz(PrpCity,"&quot=""
lblPrpCity

Is this where I add my field names and labels?
My script:
Nz(label22,"&quot=""
lblLabel22

Do I need to add the "lbl" to the beginning of my label name? i.e. lblLabel22

My Label name is Label22
and the TextBox name is SqlBatch
and the Form name is frmAuthsRpt

Would you be so kind as to rewrite the above code using my form names. This will help me to better understand your example and how I need to modify my forms properties.

Thanks for the quick response...
I really appreciate the help.

Mark

 

[BoxHead]

If Nz(SqlBatch, "&quot= "" Then
Label22.Visible = False
Else
Label22.Visible = True
End If



That should do it.

Look at Nz in Access Help. It checks for a null value in the field or control you specify. If the value is null, it treats it like the second value.

  Nz(TheFieldToCheckForNull, TheValueToUseInsteadOfNull)

You could also use

If IsNull(sqlBatch) Or sqlBatch = "" Then...

HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[CHMPB]

OK, This is what I have in my Forms
"Current Event" [Event Procedure]

Private Sub Form_Current()
If Nz(SqlBatch, "&quot = "" Then
Label22.Visible = False
Else
Label22.Visible = True
End If

End Sub


With this the labels gone completely. What am I doing wrong?

Thanks again

Mark

 

[CHMPB]

Hey John,

I figured out what I was doing wrong. The textbox name is SQL Batch (w/space) not SQLBatch. I added an underscore to replace the blank and presto, success.

Now, I have three others fields on the same form that I want to do this to.

In the following example, is this all I need to do to recreate this procedure? I just added a 1 to the end of the procedure name. i.e Form_Current1()

Private Sub Form_Current1()
If Nz(Report_link, "&quot = "" Then
Label1.Visible = False
Else
Label1.Visible = True
End If

End Sub

and so on.......

Thanks again.

 

[BoxHead]

C,

The Current event occurs whenever you go to a record on a form. Don't rename the event.

You can have many things happen on any single event. Stack the If...Then statements. Be sure to include the End If for each.

Private Sub Form_Current() 

If Nz(Sql_Batch, "") = "" Then
    Label22.Visible = False
  Else
    Label22.Visible = True
  End If

If Nz(Report_link, "") = "" Then
    Label1.Visible = False
  Else
    Label1.Visible = True
  End If

End Sub

When a record is first displayed in a form, the On Current event will occur and the above code will run.

HTH

John

Use what you have,
Learn what you can,
Create what you need.

 

[CHMPB]

Perfect...

John, you have been a big help.

Thanks again.

Mark

 

[BoxHead]

Always glad to help, Mark. John

Use what you have,
Learn what you can,
Create what you need.