none
How to keep subform datasheet columns hidden while expanding other columns? RRS feed

  • Question

  • Hey guys, me again! I have a subform with a datasheet. In this datasheet I have some of the columns hidden, but when I try to expand columns that are visible, sometimes I unhide hidden columns. How can I program these columns to stay hidden during the drag to expand process? Thanks in advance!
    Thursday, September 12, 2019 1:47 PM

Answers

  • Hi InnVis,

    I'm not sure how you want to trigger the events but indeed it is possible to hide datasheet view columns.

    Let's just say you want to trigger it at the Form's open event.

    DoCmd.SelectObject acForm, "MyFormNameHere", False    'False means not in Access Database Window
    DoCmd.GoToControl "MyDataSheetColumnOrFieldName"   'select your column or field to hide
    DoCmd.RunCommand acCmdHideColumns    'hide my column/field

    I assumed that your app has disabled the Access Menu because the user can still unhide the columns.

    "SelectObject" requires the Form to be running or open. "GoToControl" is to set focus to the Field/Column to hide it.

    To unhide, use "acCmdUnhideColumns".

    Basically a Macro code. 


    • Edited by AccessVandal Friday, September 13, 2019 5:01 AM typo
    • Marked as answer by InnVis Friday, September 13, 2019 3:57 PM
    Friday, September 13, 2019 4:58 AM

All replies

  • You can use VBA to set ColumnWidth to 0
    Thursday, September 12, 2019 3:31 PM
  • Yes, I can do this on an event like load, change, etc. The issue is, how do I make it stay at 0 while the user is trying to expand other columns. Sometimes if the column that is hidden is between fields that they are expanding, they end up opening the column from 0 and making it visible again.
    Thursday, September 12, 2019 4:38 PM
  • Is your datasheet subform based on a query?

    If so, and you don't want that field in the datasheet, remove it from the query or subform.

    Or if you need the field in the query, remove the check from the Show: 

    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.



    • Edited by DriveEV Thursday, September 12, 2019 6:10 PM
    • Marked as answer by InnVis Thursday, September 12, 2019 7:57 PM
    • Unmarked as answer by InnVis Thursday, September 12, 2019 7:59 PM
    Thursday, September 12, 2019 6:05 PM
  • If you hide it form the query, functions such as opening from it don't work.
    Thursday, September 12, 2019 7:59 PM
  • IMHO datasheet is a poor way of displaying your data...when you have continuous forms that you can do just about everything with great visuals ...why bother with datasheet...

    Just have your textboxes unbound/hidden and based on what you want to display just assign the controlsource on the fly and move them to your liking.... and of course make them visible...


    Thursday, September 12, 2019 8:26 PM
  • IMHO datasheet is a poor way of displaying your data...when you have continuous forms that you can do just about everything with great visuals ...why bother with datasheet...

    Hi InnVis,

    In line with John, I never use the datasheets.

    In a continuous form you can also play with the widths of the columns, though it takes a little more administration to shift the controls on the right side of the current accordingly.

    I use also some other techniques to display the values that take more place than the control gives. As I enter any control, its value is displayed in the StatusBar of the form, truncated to 255 characters if necessary.

    I can also click on any control to display its value in a Zoom-form where you can use almost the whole screen to display the control's value. In this Zoom-control you can have a multitude of text-processing functions of whatever you want.

    And this also shows the power of generalization: if you make it for one control, it is applicable to ALL controls.

    Imb.

    Thursday, September 12, 2019 10:59 PM
  • Hi InnVis,

    I'm not sure how you want to trigger the events but indeed it is possible to hide datasheet view columns.

    Let's just say you want to trigger it at the Form's open event.

    DoCmd.SelectObject acForm, "MyFormNameHere", False    'False means not in Access Database Window
    DoCmd.GoToControl "MyDataSheetColumnOrFieldName"   'select your column or field to hide
    DoCmd.RunCommand acCmdHideColumns    'hide my column/field

    I assumed that your app has disabled the Access Menu because the user can still unhide the columns.

    "SelectObject" requires the Form to be running or open. "GoToControl" is to set focus to the Field/Column to hide it.

    To unhide, use "acCmdUnhideColumns".

    Basically a Macro code. 


    • Edited by AccessVandal Friday, September 13, 2019 5:01 AM typo
    • Marked as answer by InnVis Friday, September 13, 2019 3:57 PM
    Friday, September 13, 2019 4:58 AM
  • Thanks AccessVandal, this worked flawlessly!
    Friday, September 13, 2019 3:57 PM