Hide Columns on Datasheet of Split form

[Snowflake68]

PLEASE HELP! This is my first post and I really hope someone can help me with this please, I spent two days trawling the internet and trying different methods to get this to work. Im not sure if its my lack of knowledge or whether its just MS Access that is at fault.:banghead:

I am pretty new to VBA and I am having the same problem of hiding and un-hiding columns on a datasheet of a split form. I am building a system that will be used in front of customer and therefore wish to hide columns that contain cost sensitive data. The same forms need to be able to show the Sales Rep the hidden columns simply by ticking a box. Basically a toggle on and off of hidden columns.

I have been playing around with the code below (which i found in another forum) to hide the Field called COO when I click the tick box called chkHIdeFields check box. This works on a Single form but not on a datasheet of a split form.

Private Sub chkHideFields_Click()
' Note: vbTrue = -1
If Me.chkHideFields = vbTrue Then
Me.COO.Visible = True
Else: Me.COO.Visible = False
End If
End Sub

I have been able to hide the column of my split form by using the ColumnHidden property of the On Load event just as a proof of concept that the ColumnHidden property actually works to hide a column, which it does. This is the code that im using to hide the column called COO.

Me.COO.ColumnHidden = True

However if I then set it to = False and then close and open the form, it doesn't unhide the column. the only way I can unhide the column is to do it from the Form view via the un-hide dialogue pop up box.

I have two issues here, the first one is getting the form to recognize when to hide and when to show the column of the datasheet on the split form and the second is to get the code to for the check box method.

Lastly once I get it working for 1 field I need to be able to define a list of 7 or 8 other fields all at the same time.

I have small access database containing everything Ive tried but it wont let me upload it on here so if someone can help me and would like my Access db please advise how I can supply this to you.

 

[vbaInet]

Sorry, I've just scanned through your post but from what I gather you want to hide a specific column? Add that same code in the Load event of your form and it will stay hidden. If on the other hand you want to hide it permanently, then best to not include it in your record source.

 

[vbaInet]

And a split form is not very customisable.

 

[Snowflake68]

Thanks for you reply. I need to provide an option to the End User that will show/hide a defined set of columns. Basically there are 7 columns which I need to toggle on and off at a click of a button. The form I have has to stay as a split form. I'm still very much a Damsel in distress over this and already had the customer on the phone this morning stressing to me how important it is to have this feature. :banghead:

 

[vbaInet]

The commands you use are:

docmd.RunCommand acCmdHideColumns
docmd.RunCommand acCmdUnhideColumns

The ColumnHidden and ColumnWidths properties will have no effect in a Split Form.

Why do you need to do this anyway? Your users can hide/unhide the columns themselves by right-clicking the field.

If you want greater control, use a subform instead of a split form.

 

[mjseim]

I don't know about the original poster but I can attest that this need persists with others as well, including myself.

While many may offer various form views and subforms as possible options the fact remains that Split Form view has many virtues.

Most significantly for me is the fact that filtering is fully then taken care of via the form itself. Users can single-filter, multi-filter, filter by contains, filter ">=", order the data ascending, order the data desending, etc.

All of this is possible WITHOUT ANY VBA or added controls to speak of.

Furthermore, it's then really easy for people to copy data out of the tool if they should need the ability to do so. Of course, it also opens the door for users to be able to paste data INTO the table with ease. Both of those options aren't quite possible with traditional Continuous Form view.

So, frankly, I really do support the original poster that this seems to be a frustration point with users out there. I can't seem to find a reliable answer. Who knows... perhaps it just doesn't exist and, indeed, Split Form + VBA isn't all that strong of a marriage.