?
Solved

UnhideColumns in Access 97

Posted on 2004-04-20
10
Medium Priority
?
564 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 1500 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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

839 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