sethiddenattribute - Tables

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.

I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?

"Klatuu" wrote:

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.

The code was not modified. I just showed the lines I commented to create the
error. Remove the comment marks from these lines:


' If Left(strTblName, 4) "Msys" Then
Should be
If Left(strTblName, 4) "Msys" Then

' End If
Should be
End If


--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?

"Klatuu" wrote:

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.

I'm feeling a guilty on taking up your time on this but for the life of me I
just can't see to get this working. If I can indulge your time one more
time. Here is my code. Unless I'm blatantly missing something can you
preview?

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing

End Sub

Thx

"Klatuu" wrote:

The code was not modified. I just showed the lines I commented to create the
error. Remove the comment marks from these lines:


' If Left(strTblName, 4) "Msys" Then
Should be
If Left(strTblName, 4) "Msys" Then

' End If
Should be
End If


--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?

"Klatuu" wrote:

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.

I wrote it to be called. the argument name was blnHide. Since you are using
it in a different place, you need to use an explicit True.

Change this line:
Application.SetHiddenAttribute acTable, strTblName, blnHide
To
Application.SetHiddenAttribute acTable, strTblName, True
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm feeling a guilty on taking up your time on this but for the life of me I
just can't see to get this working. If I can indulge your time one more
time. Here is my code. Unless I'm blatantly missing something can you
preview?

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing

End Sub

Thx

"Klatuu" wrote:

The code was not modified. I just showed the lines I commented to create the
error. Remove the comment marks from these lines:


' If Left(strTblName, 4) "Msys" Then
Should be
If Left(strTblName, 4) "Msys" Then

' End If
Should be
End If


--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?

"Klatuu" wrote:

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.

Well, not sure what cloud of typos is looming over my head but this code just
doesn't seem to work on my end. I have made the change from "blnHide" to
"True" (without quotes) but still get the same error as before.

"Klatuu" wrote:

I wrote it to be called. the argument name was blnHide. Since you are using
it in a different place, you need to use an explicit True.

Change this line:
Application.SetHiddenAttribute acTable, strTblName, blnHide
To
Application.SetHiddenAttribute acTable, strTblName, True
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm feeling a guilty on taking up your time on this but for the life of me I
just can't see to get this working. If I can indulge your time one more
time. Here is my code. Unless I'm blatantly missing something can you
preview?

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing

End Sub

Thx

"Klatuu" wrote:

The code was not modified. I just showed the lines I commented to create the
error. Remove the comment marks from these lines:


' If Left(strTblName, 4) "Msys" Then
Should be
If Left(strTblName, 4) "Msys" Then

' End If
Should be
End If


--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?

"Klatuu" wrote:

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.

Post the code as it is now. There is no reason it should not work.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Well, not sure what cloud of typos is looming over my head but this code just
doesn't seem to work on my end. I have made the change from "blnHide" to
"True" (without quotes) but still get the same error as before.

"Klatuu" wrote:

I wrote it to be called. the argument name was blnHide. Since you are using
it in a different place, you need to use an explicit True.

Change this line:
Application.SetHiddenAttribute acTable, strTblName, blnHide
To
Application.SetHiddenAttribute acTable, strTblName, True
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm feeling a guilty on taking up your time on this but for the life of me I
just can't see to get this working. If I can indulge your time one more
time. Here is my code. Unless I'm blatantly missing something can you
preview?

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing

End Sub

Thx

"Klatuu" wrote:

The code was not modified. I just showed the lines I commented to create the
error. Remove the comment marks from these lines:


' If Left(strTblName, 4) "Msys" Then
Should be
If Left(strTblName, 4) "Msys" Then

' End If
Should be
End If


--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?

"Klatuu" wrote:

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, True
End If
Next tbl
Set tbl = Nothing

End Sub

"Klatuu" wrote:

Post the code as it is now. There is no reason it should not work.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Well, not sure what cloud of typos is looming over my head but this code just
doesn't seem to work on my end. I have made the change from "blnHide" to
"True" (without quotes) but still get the same error as before.

"Klatuu" wrote:

I wrote it to be called. the argument name was blnHide. Since you are using
it in a different place, you need to use an explicit True.

Change this line:
Application.SetHiddenAttribute acTable, strTblName, blnHide
To
Application.SetHiddenAttribute acTable, strTblName, True
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm feeling a guilty on taking up your time on this but for the life of me I
just can't see to get this working. If I can indulge your time one more
time. Here is my code. Unless I'm blatantly missing something can you
preview?

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing

End Sub

Thx

"Klatuu" wrote:

The code was not modified. I just showed the lines I commented to create the
error. Remove the comment marks from these lines:


' If Left(strTblName, 4) "Msys" Then
Should be
If Left(strTblName, 4) "Msys" Then

' End If
Should be
End If


--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?

"Klatuu" wrote:

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.

The code looks fine.
What is not working?
Are you getting an error?
If so, what is the error and on which line does the error occur?
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, True
End If
Next tbl
Set tbl = Nothing

End Sub

"Klatuu" wrote:

Post the code as it is now. There is no reason it should not work.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Well, not sure what cloud of typos is looming over my head but this code just
doesn't seem to work on my end. I have made the change from "blnHide" to
"True" (without quotes) but still get the same error as before.

"Klatuu" wrote:

I wrote it to be called. the argument name was blnHide. Since you are using
it in a different place, you need to use an explicit True.

Change this line:
Application.SetHiddenAttribute acTable, strTblName, blnHide
To
Application.SetHiddenAttribute acTable, strTblName, True
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm feeling a guilty on taking up your time on this but for the life of me I
just can't see to get this working. If I can indulge your time one more
time. Here is my code. Unless I'm blatantly missing something can you
preview?

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing

End Sub

Thx

"Klatuu" wrote:

The code was not modified. I just showed the lines I commented to create the
error. Remove the comment marks from these lines:


' If Left(strTblName, 4) "Msys" Then
Should be
If Left(strTblName, 4) "Msys" Then

' End If
Should be
End If


--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?

"Klatuu" wrote:

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.

Run Time Error 2016: "You can't modify the attributes of Systems Tables"
Upon going into Debug, the vba line: "Application.SetHiddenAttribute
acTable, strTblName, True" is highlighted.

Any thoughts?

Thx.

"Klatuu" wrote:

The code looks fine.
What is not working?
Are you getting an error?
If so, what is the error and on which line does the error occur?
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, True
End If
Next tbl
Set tbl = Nothing

End Sub

"Klatuu" wrote:

Post the code as it is now. There is no reason it should not work.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Well, not sure what cloud of typos is looming over my head but this code just
doesn't seem to work on my end. I have made the change from "blnHide" to
"True" (without quotes) but still get the same error as before.

"Klatuu" wrote:

I wrote it to be called. the argument name was blnHide. Since you are using
it in a different place, you need to use an explicit True.

Change this line:
Application.SetHiddenAttribute acTable, strTblName, blnHide
To
Application.SetHiddenAttribute acTable, strTblName, True
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm feeling a guilty on taking up your time on this but for the life of me I
just can't see to get this working. If I can indulge your time one more
time. Here is my code. Unless I'm blatantly missing something can you
preview?

Private Sub Form_Open(Cancel As Integer)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing

End Sub

Thx

"Klatuu" wrote:

The code was not modified. I just showed the lines I commented to create the
error. Remove the comment marks from these lines:


' If Left(strTblName, 4) "Msys" Then
Should be
If Left(strTblName, 4) "Msys" Then

' End If
Should be
End If


--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I copy/pasted your modified code and unfortunately still getting a problem.
When highlighted the variable strTblName is showing "MSysAccessObjects".
Does this shed any light on the topic?

"Klatuu" wrote:

I was able to reproduce your problem. I commented out the lines as below:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
' If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
' End If
Next tbl
Set tbl = Nothing
End Sub

I did it from a front end with linked tables. If you don't have the lines
in to filter out system tables (those that start with Msys), you will get
that error. If you entered the code as written and you are still having the
error, when it goes to debug mode, check the value of the variable
strTblName.

Let me know what happens.

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I'm using 2003. Half of these tables are linked from a back end if that
makes any difference.

"Klatuu" wrote:

Public or private, makes no real difference. It is just how you use it.
I did not have that problem in testing.
What version of Access are you using. I tested it on 2003.
I have not used 2007, so I don't know if they have changed the system table
names or not.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Dave,
I thought that the private sub was the way to go on this for me. However,
when I paste this into the OnOpen section and reopen I get a Run Time Error:
"You can't modify the attributes on System Tables".
Where the highlighted line is: Application.SetHiddenAttribute acTable,
strTblName, blnHide.

Your thoughts?

Thx.

"Klatuu" wrote:

It only needs to be Public if you plan to call it from more than one place.
To make it a Public Sub, paste the code into a standard module. If you have
an existing standard module, you can put it there. If you don't, select
Modules from the database windwow, select new. Paste the code there, then
save the module and give it a new. DO NOT name it the same as the sub name.
Access will choak on that.

Then all you need to do to call it is:

Call ShowHideTables(True) 'Hide all database tables

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I guess I'm not familiar with using public subs. Can you get point me in the
right direction on this?

"Klatuu" wrote:

Personally, I wouldn't use it at all. There should be no reason to do that.
But, if you want to, make it private instead of public and put it in your
starup form's code module and call it from the form's Load event.
--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

I have a startup form upon opening. Would you suggest putting this code at
OnOpen or another area?

"Klatuu" wrote:

Use this:

Public Sub ShowHideTables(blnHide As Boolean)
Dim tbl As TableDef
Dim strTblName As String

For Each tbl In CurrentDb.TableDefs
strTblName = tbl.Name
If Left(strTblName, 4) "Msys" Then
Application.SetHiddenAttribute acTable, strTblName, blnHide
End If
Next tbl
Set tbl = Nothing
End Sub

--
Dave Hargis, Microsoft Access MVP


"briank" wrote:

Is there a way to hide all of the tables using sethiddenattribute without
itemizing each table? Perhaps a "hide all" command? Thx.