Solved

UnhideColumns in Access 97

Posted on 2004-04-20
10
536 Views
Last Modified: 2010-05-18
Im developing a fairly simple database for work in access 2000. The main problem I have is that the users only have access 97 on their machines (don’t ask why it’s a complete farce), so I have to convert the database to 97.
My problem is as follows, I have a form which contains a subform displaying data from a query as a datasheet. The user selects search criteria on the main form and the sub form displays the results ect. I also have a few buttons which allows users to Freeze/Unfreeze columns and one button which allows the user to select which columns to display, using the Access 'Unhide Columns' dialog box using the following code:
        Form_frmMainSearch.frmSubSearch.SetFocus
        DoCmd.RunCommand acCmdUnhideColumns
Note this code works fantastic in Access 2000, but when its executed on a 97 machine after conversion I get the following error:

   The command or action ‘UnhideColumns’ isn’t available now.
   You may be in a read-only database or an unconverted database from an earlier version of  .
   The type of object the action applies to isn’t currently selected or isn’t in the active view.
   Use only those commands and macro actions that are currently available for this database.

I know that the Unhide Columns dialog is available in 97; however, I’m not sure if I can access it programmatically like in 2000.

Any Ideas or will I have to create my own dialog?
0
Comment
Question by:cwhitby
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 10866972
Hi cwhitby,

just a thought of work around,
instead of hiding the column, why don't you move it to righ, away from the Form/SubForm visible area,
just make sure you don't give the user a Horizontal scroll bar.

Hope this helps

Jaffer
0
 

Author Comment

by:cwhitby
ID: 10867001
There are upwards of 30 columns in the subform, and it’s a user requirement to be able to hide (which they can do with a right mouse click). I could create a separate form with a list box to emulate the access dialog, but I don’t want too if there is a simple trick to make it work.

Thanks anyway.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 10867037
I am sorry, I am not familiar with Access 97,
but the work around I told, to the use would be it is hidden, as they won't see it.
just a thought anyway.

jaffer
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 65

Expert Comment

by:rockiroads
ID: 10867116
is it possible to set the individual column width to 0
0
 

Author Comment

by:cwhitby
ID: 10867124
Yes but that doesnt provide the flexibility thet the Unhide Dialog provides
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10867176
Hmm, try:

Dim ctl As Control

For Each ctl In Me.frmSubSearch.Controls
    If ctl.ControlType > 100 Then
        If ctl.ColumnHidden Then
            ctl.ColumnHidden = False
        End If
    End If
Next ctl

Nic;o)
0
 

Author Comment

by:cwhitby
ID: 10867214
Thanks nico, that does work very well, but if a user wanted to unhide a single column it would get quite annoying having to hide all of the cols that arent required every time.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 10867300
That's why I normally use in the form's OnOpen event:
Dim ctl As Control
Dim strMsg as string
For Each ctl In Me.frmSubSearch.Controls
    If ctl.ControlType > 100 Then
        If ctl.ColumnHidden Then
            strMsg = strMsg & " " & ctl.name
        End If
    End If
Next ctl

IF len(strMsg) > 0 then
    msgbox "Found hidden columns: " & strMsg
endif

So they know, even better perhaps to show the names on the form, saving also the OK click from the msgbox :-)

Nic;o)
0
 

Author Comment

by:cwhitby
ID: 10916264
Thanks nico, I ended up emulating the Access dialog with a new form and listbox, I used a variation of your code behind it and it works beautifully. Cheers.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10916401
Glad I could help, success with the application !

Nic;o)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

840 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