locked
Make Textbox Invisible If Field is Null RRS feed

  • Question

  • I am trying to make a a textbox invisible if one of my other fields is Null. I am basically trying to show one field on a report if there is a value, if there is not a value I would like to show a different field.

    Here is the code I am using.

     

    Private Sub Report_Load()
    If Len(Me.Text36 & "") = 0 Then
        InstFull.Visible = True
    Else
        InstFull.Visible = False
    End If

    End Sub

     

    I cannot figure out why this is not working.  Please help!!

    Thanks

    Wednesday, November 30, 2011 3:53 PM

Answers

  • Try this - I tend to overkill and use both because it hurts my brain too much sometimes to figure out whether a result is null or zero-length.

    IF IsNull(Me.Text36) = True OR Me.Text36 = "" THEN

    Me.InstFull.Visible = False

    ELSE

    Me.InstFull.Visible = True

    END IF

    I noticed in your reply above you set the Visible property to True when I think you meant false - don't display when "blank".

    As Mr. Mosca noted, this needs to be in the OnFormat property of the section in which the fields have been placed.

    Ross

    • Marked as answer by AndreMarley Thursday, December 1, 2011 4:09 AM
    Wednesday, November 30, 2011 6:02 PM

All replies

  • Try IsNULL(Me.Text36) = True

     

    Wednesday, November 30, 2011 4:09 PM
  • Where is the InstFull textbox? You need to use the Format event of the section it is in, not the report's Load event.
    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals
    Wednesday, November 30, 2011 4:10 PM
  • If IsNull(Me.Text36)=True Then

    Me.InstFull.Visible=False

    ElseIf IsNull(Me.Text36)=False Then

    Me.InstFull.Visible=True

    End If

    Wednesday, November 30, 2011 4:12 PM
  • The InstFull textbox is located in the detail section of the report.  I tried to move the code to the OnFormat event and I still am having the same problem.
    Wednesday, November 30, 2011 4:26 PM
  • Thanks everyone for your suggestions.

     

    I moved my code to the OnFormat event as Bill suggested and changed my code as shown below but it is makeing the InstFull field visible at all times even when the Text36 field is null.  Here is the code:

     

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If IsNull(Me.Text36) = True Then

    Me.InstFull.Visible = True

    ElseIf IsNull(Me.Text36) = False Then

    Me.InstFull.Visible = False

    End If
    End Sub

     

    Thanks

    Wednesday, November 30, 2011 4:28 PM
  • Try this - I tend to overkill and use both because it hurts my brain too much sometimes to figure out whether a result is null or zero-length.

    IF IsNull(Me.Text36) = True OR Me.Text36 = "" THEN

    Me.InstFull.Visible = False

    ELSE

    Me.InstFull.Visible = True

    END IF

    I noticed in your reply above you set the Visible property to True when I think you meant false - don't display when "blank".

    As Mr. Mosca noted, this needs to be in the OnFormat property of the section in which the fields have been placed.

    Ross

    • Marked as answer by AndreMarley Thursday, December 1, 2011 4:09 AM
    Wednesday, November 30, 2011 6:02 PM
  • Worked perfect.  Thanks to all!
    Thursday, December 1, 2011 4:09 AM
  • I would like to recreate this on a form instead of a report.  Could you help me out and tell me what event the code should be executed?

     

    Thanks

    Thursday, December 1, 2011 5:58 AM
  • This depends on what the purpose of your form is and how it's situated. 

    For example, I have an Employee form which is refreshed based on a selection from a combo box.  Any fields which have conditional formatting or triggers have their code placed in the OnCurrent event of the form.

    Then differently, I use this code in a Licensing form which is opened from the Employee form.  The Licensing form is bound to the Employee form by EmployeeID and therefore does not need to refresh based on a selection (there is only one).  Thus, my "visibility" formatting code is place in the OnOpen event of the License form.

    The code itself does not need to change provided the fields are named the same.

    HTH

    Ross

    Thursday, December 1, 2011 3:56 PM
  • Try this - I tend to overkill and use both because it hurts my brain too much sometimes to figure out whether a result is null or zero-length.

    IF IsNull(Me.Text36) = True OR Me.Text36 = "" THEN

    Me.InstFull.Visible = False

    ELSE

    Me.InstFull.Visible = True

    END IF

    I noticed in your reply above you set the Visible property to True when I think you meant false - don't display when "blank".

    As Mr. Mosca noted, this needs to be in the OnFormat property of the section in which the fields have been placed.

    Ross

    Which could be further refined to:

    InstFull.Visible = NOT (IsNull(Text36) Or Text36 = "")
    




    Eric Williams
    Thursday, December 1, 2011 5:46 PM
  • Basically I have a continuous form that list legal documents.  Each legal document is numbered by either a Book Volume/Page or an Instrument Number.  On each record on the continous form I am wanting to show the Book Volume & Page unless it is Null or a zero length value then I would like it to show the Instrument Number.  I have both text boxes stacked on top of each other so no matter which one it is, it shows the document number in the same location on the form. 

    I am still unsure which event should trigger the code on the continuous form.

    Any help would be greatly appreciated!!

    Thanks.

    Friday, December 2, 2011 5:46 AM
  • In this instance you don't need two text boxes.  Just use one text, unbound, and put the following in the control source (obviously, correct my field names):

    =IIF(IsNull([BookVolumePage]), [InstrumentNumber], [BookVolumePage])

    If you get results with a blank field but you know for a fact there is an instrument number, then you will have to embed another IIF() into the FALSE condition to test for the zero-length string.  The above assumes there is always an Instrument Number. If both fields are null, your text box will appear empty.

    Friday, December 2, 2011 5:06 PM
  • Try this - I tend to overkill and use both because it hurts my brain too much sometimes to figure out whether a result is null or zero-length.

    IF IsNull(Me.Text36) = True OR Me.Text36 = "" THEN

    Me.InstFull.Visible = False

    ELSE

    Me.InstFull.Visible = True

    END IF

    I noticed in your reply above you set the Visible property to True when I think you meant false - don't display when "blank".

    As Mr. Mosca noted, this needs to be in the OnFormat property of the section in which the fields have been placed.

    Ross

    Which could be further refined to:

    InstFull.Visible = NOT (IsNull(Text36) Or Text36 = "")
    




    Eric Williams

    I've always used this method:

    InstFull.Visible = NOT (Len(Me.Text36 & vbNullString) = 0)
    




    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals
    Friday, December 2, 2011 6:39 PM
  • Thanks VBallRef!!  That worked perfectly!  Thanks everybody for your input.
    Saturday, December 3, 2011 2:43 AM