Invisable Text Box

johnwiseman · Sep 1, 2004

Hi.

I have a form attached to a table with nine columns.

On the form I just want the text boxes to appear with information when there is information in the column. When there is no information in a column I wish the text box to be invisable.

Thanks in advance.

John

Ed2020 · Sep 1, 2004

I haven' tested this, but something along these lines should work:

Dim MyCtrl as Access.Control

For Each MyCtrl In Me.Controls
If TypeOf MyCtrl Is Access.TextBox And (IsNull(MyCtrl.Value) OR MyCtrl.Value="") Then
Me.Controls(MyCtrl.Name).Visible=False
Else
Me.Controls(MyCtrl.Name).Visible=True
End If
Next MyCtrl

Ed Metcalfe.

Please do not feed the trolls.....

hymn · Sep 1, 2004

Question
How do you fill the text boxes or put in data if they are not visible

Hope this helps
Hymn

Ed2020 · Sep 1, 2004

Hymn - Bound form?

Please do not feed the trolls.....

hymn · Sep 1, 2004

Ed2020 the Question wasn't for you but johnwiseman we answered at the same time. How do you edit or add data if the fields are hidden, just wondering

Hope this helps
Hymn

Ed2020 · Sep 2, 2004

Aha. Gotcha!

Ed.

Please do not feed the trolls.....

johnwiseman · Sep 2, 2004

Ed2020 and Hymn.

Thanks for your advice chaps.

The form in question is a display only from produced from a query. There is also a bound input form for adding the information.

AccessGuruCarl · Sep 2, 2004

johnwiseman,

Not sure if Ed2020's code works, looks like it should, But, I know this one does...
I use it in the unload event, but it can be used just about anywhere. I commented out what you don't need and added what you do need

Code:

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo MY_ERROR
Dim ctl As Control
'FYI:Use's forms TabOrder
    For Each ctl In Me.Controls
'To use with other controls - HighLight ControlType
'in the next line, then hit F1
        If ctl.ControlType = acTextBox Then
            If IsNull(ctl) Then[COLOR=green]
      '          MsgBox ctl.Name & " is a required field, " & vbCr & _
      '              "Please enter the required information to continue.", _
      '              vbInformation, "Required Field"
      '          ctl.BackColor = 13303807    'Light Yellow
      '          ' Make sure you set the lost focus event
      '          ' for each textbox to set them back to default.
      '              ' me.backcolor = vbwhite    'or  16777215
      '          ctl.SetFocus
      '          Cancel = True  
      '          Exit Sub[/color]
            [b][COLOR=blue]ctl.Visible = False[/color][/b]
            End If
        End If
    Next ctl
Exit Sub
MY_ERROR:
MsgBox Err.Number & vbCr & vbCr & Err.Description
End Sub

Somewhere I have a piece code that moves the 'visible' textbox's to replace the in-visible one's. Textbox's are listed vertically. If Textbox2 is in-visible, Textbox3 replaces Textbox2 position, and so on. Let me know if your interested in taking a look at it.

I'll have to test Ed's code, It looks interesting!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

AccessGuruCarl · Sep 2, 2004

In regards to Ed2020's Post.

I have tested this, see the reults in below sub.

Code:

Private Sub Command8_Click()
Dim MyCtrl As Access.Control
'Dim MyCtrl As Control 'Either Declare will work
[COLOR=blue]
'Ed2020's Orginal Post
[/color]

'THIS DOESN'T WORK
'1st Err  - Runtime : 438 - Object not supported
'2nd Err  - Compile Error - User Defined Type Not Defined
'For Each MyCtrl In Me.Controls
'    If TypeOf MyCtrl Is Access.TextBox And (IsNull(MyCtrl.value) Or MyCtrl.value = "") Then
'        Me.Controls(MyCtrl.Name).Visible = False
'    Else
'        Me.Controls(MyCtrl.Name).Visible = True
'    End If
'Next MyCtrl


'THIS WORKS
'For Each MyCtrl In Me.Controls
'  If MyCtrl.ControlType = acTextBox Then
'    If (IsNull(MyCtrl.value) Or MyCtrl.value = "") Then
'        Me.Controls(MyCtrl.Name).Visible = False
'    Else
'        Me.Controls(MyCtrl.Name).Visible = True
'    End If
'  End If
'Next MyCtrl

'THIS WORKS ALSO
For Each MyCtrl In Me.Controls
  If MyCtrl.ControlType = acTextBox Then
    If (IsNull(MyCtrl.value) Or MyCtrl.value = "" Or MyCtrl.value = " ") Then
        MyCtrl.Visible = False
    Else
        MyCtrl.Visible = True
    End If
  End If
Next MyCtrl

End Sub

johnwiseman,
You may want to use the last test. This check is better than checking for just null...

Ed2020,
I'm assuming your post was from memory, I would have left well alone, if at least the 'ControlType' was somewhere in your code. This at least get's them pointed in the right direction, if they 'try' to use 'thier included' help files. Good Try though, got me wondering!

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

TheAceMan1 · Sep 2, 2004

How are ya johnwiseman . . . . .

ohnwiseman said:
[blue]When there is no information [purple]in a column[/purple] I wish the [purple]text box to be invisable[/purple][/blue]

[blue]AccessGuruCarl & Ed2020[/blue] are close. They just test a single record instead of checking the whole field/column!

So try the following (checks all records in field/column):

Code:

[blue]   Dim ctl As Control, Criteria As String, Fld As String
   
   For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then
         Fld = "[" & ctl.Name & "]"
         Criteria = Fld & " Is Not Null"
         
         If IsNull(DLookup(Fld, "[purple][b]YourTableName[/b][/purple]", Criteria)) Then
            Me(ctl.Name).Visible = False
         Else
            Me(ctl.Name).Visible = True
         End If
      End If
   Next[/blue]

See Ya! . . . . . .

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Invisable Text Box

johnwiseman

MIS

Ed2020

Programmer

hymn

Technical User

Ed2020

Programmer

hymn

Technical User

Ed2020

Programmer

johnwiseman

MIS

AccessGuruCarl

Programmer

AccessGuruCarl

Programmer

TheAceMan1

Programmer

Similar threads

Part and Inventory Search

Sponsor