How to hide column in datasheet (split form) using VBA?

[SyafiqIzzat]

I have created a split form in Access 2007; I have added a number of fields to this form.
Is there a way in VBA to hide or show selected fields (columns) in the datasheet? I deperately need help.

TIA

 

[John Big Booty]

Without testing, try setting the Field's Visible Property to False to hide it and True to show it. Something along the lines of;

Me.FieldName.Visible = False

 

[SyafiqIzzat]

Thanks for the quick reply but somehow the code doesn't work right. Sometimes, it works and sometimes it didn't.
My idea is actually to have a check box; when it is checked, then this field will be hide/unhide. Is it possible?

 

[John Big Booty]

Try something like the following in the On Click event of your Check Box;

If Me.CheckBoxName = True Then
     Me.ControlName.Visible = False
Else
     Me.ControlName.Visible = True
End If

You will also need that code in the Form's On Current event.

 

[pbaldy]

I haven't used a split form, but in a datasheet form you've historically needed the ColumnHidden property rather than the Visible property.

 

[John Big Booty]

I haven't used a split form, but in a datasheet form you've historically needed the ColumnHidden property rather than the Visible property.

I'll have to have a play with it when I get home and have access to '07

 

[Galaxiom]

These both work:

Me.controlname.ColumnWidth = 0
Me.controlname.ColumnHidden = True

The catch is they don't change until the form is saved and reopened.
You can do this with code.

DoCmd.RunCommand acCmdSave
DoCmd.Close
DoCmd.OpenForm "formname"

(Note the save fails if the form has no changes to save.)

You will also need to store the default widths for the columns somewhere and apply these in the same way when the column is restored and when the form is loaded. Not to mention getting back to where you were in the data.

 

[John Big Booty]

Not quiet the way I'd envisioned it might work and I suspect not quiet what the OP was looking for, but it is a solution

 

[Galaxiom]

Not quiet the way I'd envisioned it might work

Me either. I have never done a SplitForm before but knew the properties from the datasheet and tried them. I was a bit surprised when they didn't seem to work but I noticed they stuck after saving the form so I knew I was on the right track.

I would have thought maybe a Repaint would do it but no. Then I Googled and found the unhappy reality. I would call it a bug.

Yet another reason to avoid ever using split forms. Great actually, as I had thought recently that maybe I should give them a go. Nah!

 

[John Big Booty]

To be honest just having had a play with a split form, for the first time, I'm not quite sure I see the point of them

 

[Galaxiom]

Actually it confirms my suspicions that Split Forms were designed for protodevelopers so they could avoid using subforms. But like so many features Microsoft adds for "ease of use" they head the novice down the path to despair.

The real solution to the question in this thread is to reconstruct the form with a datasheet subform. The columnwidth and columnvisible properties work properly.

 

[SyafiqIzzat]

I tried the syntax but still nothing change. I don't know what's wrong but the column is not hidden.

 

[Galaxiom]

I tried the syntax but still nothing change. I don't know what's wrong but the column is not hidden.

Read the whole thread. The general problem is that split forms suck when it comes to controlling them via VBA.

None of the posters had ever used a split form before and after experimenting we would all strongly advise you (and any serious developers) to eschew the use of split forms because they don't work properly.

What is specifically wrong is that the new setting of the ColumnVisible and ColumnWidth properties (at least) in a split form are not live. The form must be saved and reloaded to adopt the new settings. This makes it next to usless in this regard since saving, closing and reopening a form is ridiculously clumsy.

Change your form to a use a datasheet subform instead. This design will respond to the settings immediately.

 

[jdare8728]

I just learned this and it works. You must have the form default view set to split form

DoCmd.RunCommand acCmdUnhideColumns