Solved

Hide Columns in Datasheet

Posted on 1998-05-22
13
475 Views
Last Modified: 2008-02-01
In Access 8.

Right clicking the title bar of a datasheet gives an unhide columns option which presents the user with a list of columns to be hidden or unhidden. When the datasheet is a subform however this option is not available. I have simulated it by creating a temporary table,loading it with the columns of the passed datasheet and creating a continuous form style dialog box bound to the temporary table. It's a pain to have to create the table though, does anyone have a better way?
0
Comment
Question by:Helicopter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 27

Expert Comment

by:aburr
ID: 1974279
Make the width of the columns you want hidden 0.
0
 
LVL 8

Author Comment

by:Helicopter
ID: 1974280
The problem isn't hiding the columns, this option is still available on Access' own pop up menu. The problem is Unhiding them after they are hidden as you can in a datasheet that isn't a subform....thanks for your response though
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1974281
If the only thing you want to do is prevent users from seeing some of the colums , create a query containing only the columns your want and base your form on this query.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:Helicopter
ID: 1974282
I want them to be able to hide and unhide a specific column as and when they choose. If you look at a datasheet and right click the title bar you will see a menu item "Unhide". This is what I am trying to achieve.
0
 

Expert Comment

by:nirmala
ID: 1974283
I have not done this, but it should be technically possible.

Have a command button on the main form, that users can click on.

This can list the controls in the subform, along with the columnhidden property. These can be displayed using a pop-up form. Users can then decide which controls should be hidden. On the close event of the pop-up form, you can go thru controls of the subform and change columnhidden property of those controls that user has selected.

While checking the controls collection, you may have to filter out the irrelevant ones. One easy way would be to name all relevant controls beginning with a particular series of letters.
0
 
LVL 8

Author Comment

by:Helicopter
ID: 1974284
This is more or less what I did originally....the problem comes when you need to make a generic dialog box which is dynamically created. I have about 10 of these datasheet forms and I was trying to avoid writing a specific dialog box for each based on their column heaing names. Also to avoid having to change anything should I need to add or remove fields for some reason. I really don't know why Microsoft chose to disable this particular function in subform datasheets. Thank you all for your responses
0
 
LVL 8

Author Comment

by:Helicopter
ID: 1974285
Sorry nirmala I just read your comment again. Yes using a list box is a possibility but it's not as satsfactory as the MS dialog somehow.
0
 

Expert Comment

by:oddeh
ID: 1974286
If you mark more than one column and then dbl-click in the border between any of them, every hidden column in the marked area will become visible again.
OddEH
0
 
LVL 8

Author Comment

by:Helicopter
ID: 1974287
This is true, but the problem is it requires the user to know where the column is that he is trying to unhide. If you have several hidden columns this becomes very hit and miss., which is why the dialog box is good because it lists all available columns and shows whether they are hidden or not. Clicking on a check box then toggles that state.

I suppose what I'm really after is either the API call to access this dialog box directly or some means of basing a query on a collection without storing the information in a temporary table. Thanks for the response though
0
 
LVL 1

Expert Comment

by:Arve
ID: 1974288
You must do something like this:
For each field in form
  field.ColumnHidden=false
next

Regards, Arve
0
 
LVL 8

Author Comment

by:Helicopter
ID: 1974289
Arve, that will hide/unhide all columns, which isn't exactly what I want to do. Currently the customer has this as a "next best" but he wants to be able to show and hide any column by selecting it from a list as you can from the menu in a table or datasheet when it's not a subform. As I said in my original post, I have found a work round but it means creating a temporay table an then immediately deleting it when my dialog is closed.

Thanks for responding though.
0
 
LVL 1

Accepted Solution

by:
Arve earned 150 total points
ID: 1974290
The, I can think of tw solutions:
1 find the hidden name of the form that displays hidden colums in formview (not subform-view) and open that form. I know that it is possible ti change the Shift-F2-form, so to get this form should be possible.
2. create a listbox-function that does what you want, th MS kb has an axample of a listbox-function with x'es that simulates a multiple noncontigous selection, and in that function incorporate the code I supplied above (probably all three lines :-)).

Her is a list-box function.
The subform name is table1
The main form is form1
The third form with only a listbox is form2
The listbox is list0
bound column is 1
row source type: VisKol
Function VisKol(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
    Dim intOffset As Integer
   
    Select Case code
        Case acLBInitialize             ' Initialize.
            VisKol = True
        Case acLBOpen                       ' Open.
            VisKol = Timer         ' Unique ID.
        Case acLBGetRowCount                ' Get rows.
            VisKol = Forms!Form1!Table1.Form.Count
        Case acLBGetColumnCount     ' Get columns.
            VisKol = 2
        Case acLBGetColumnWidth     ' Get column width.
            VisKol = -1                ' Use default width.
        Case acLBGetValue                   ' Get the data.
            Select Case col
            Case 0
                'Here we want name
                VisKol = Forms!Form1!Table1.Form.RecordsetClone.Fields(row).Name
            Case 1
                'Hidden?
                VisKol = Forms!Form1!Table1.Form(Forms!Form1!Table1.Form.RecordsetClone.Fields(row).Name).ColumnHidden
            End Select
    End Select
End Function
Private Sub List0_Click()
   
    Forms!Form1!Table1.Form(Me!List0).ColumnHidden = Not Forms!Form1!Table1.Form(Me!List0).ColumnHidden
    Me.Refresh
End Sub

Form2 must br made as a popup, or you can put the listbox in your main form.
The way I get the columnhidden-information is a bit long and can probably be shortened, but time is running out.

I really want your points!

Regards, Arve
0
 
LVL 8

Author Comment

by:Helicopter
ID: 1974291
Arve, I just spent most of the morning doing more or less the same thing, with the added refinement of creating an array of column headings which makes the routine generic. Still we were in the same ball park, so have the points if it means that much to you :)
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question