Hiding a Tab in a Tab Control

[Guest]

Hello:

I'd like to know if it is possible to hide a tab control based page upon a
value in field in another table. I have a tab control where I want to hide
pages based upon whether a check field in another table is checked no.

How would I use VBA to do this?

Thanks
Brennan

 

[Graham Mandeno]

Hi Brennan

This will hide a tab page:

Me.TabControlName.Pages(PageNameOrIndexNumber).Visible=False

So, instead of False, substitute a boolean expression based on the value you
want to test. For example:

(SomeValue = "AdminUser")

Then, if the expression is True, the page will be displayed, otherwise it
will be hidden.

 

[Graeme Richardson]

Say the Page on your TablControl is called Page2.

You can hide the page with:

Page2.Visible = False

To determine If the page is checked then we'll use the DLookup function
(more efficient to use DAO or ADO code - but walk before running)
Substitute for your scenario.

showPage = DLookup("checkField", "myTable", "PrimaryKey = 1")

Putting it all together we have (using the Form_Load event)

Private Sub Form_Load()
Page2.Visible = DLookup("checkField", "myTable", "PrimaryKey = 1")
End Sub

HTH, Graeme

 

[Sandra Daigle]

Sure - you have to run the same code in the Afterupdate event of the
checkbox and in the Current Event of the form (assuming you want to
show/hide the page when you navigate to existing records). I recommend
putting the code into a separate procedure and calling it from each event.
Note that the following code is referring to the 2nd tab page. The pages
property is indexed starting with 0. Here's the code:

Private Sub Check17_AfterUpdate()
TogglePage
End Sub

Private Sub TogglePAge()
If Me.Check17 = True Then
Me.TabCtl9.Pages(1).Visible = False
Else
Me.TabCtl9.Pages(1).Visible = True
End If

End Sub


This could also be written this way:

Private Sub TogglePage()
Me.TabCtl9.Pages(1).Visible = Me.Check17
end sub

 

[Guest]

Thanks

This looks simple enough. The table this event will be referencing will be
somewhat large. What are the advantages to using DAO or ADO and how would I
learn more about it?

Thanks again

 

[Graeme Richardson]

If the table containsa very large number of recorss then the DLookup may
take a moment to return the value - try it in the immediate window.

First add a reference to Microsoft DAO 3.x Object Library (if not already
added).

Private Sub Form_Load()
Page2.Visible = showPage()
End Sub

Private Function showPage() As Boolean
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
' Get handle to database
Set db = DBEngine.Workspaces(0).Databases(0) ' Handle to database
' SQL string / query
strSQL = "SELECT checkField FROM myTable WHERE PrimaryKey = 1"
' Open the query in code so we can read it
Set rst = db.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
' Our query return ed at least one record
rst.MoveFirst ' Move to first record
showPage = rst("checkField")
End If
End Function

Graeme.

 

[Rick Brandt]

If the table containsa very large number of recorss then the DLookup
may take a moment to return the value - try it in the immediate
window.

The only "published" article I have ever seen found no performance advantage to
a Recordset over DLookup(). In fact Dlookup was found to be slower only when
compared to Seek which isn't used much since it doesn't work against linked
tables.

The performance problems with DLookup are present when used in queries or loops
due to the repeated overhead (not having to do with the actual data retrieval).
It is that overhead which can be reduced to a one-time hit instead of a
per-iteration with different methods and even then only if the different method
is created such that it doesn't cause the same per-iteration overhead.