Freeze datasheet columns?

[geoB]

Assuming a table is open in datasheet view, read-only, can one freeze the three leftmost columns? I can freeze the leftmost with

DoCmd.RunCommand acCmdFreezeColumn

But three?

Thanks in advance.

George

 

[boblarson]

Check this out.

 

[geoB]

Thanks, Bob. But I'd skimmed that page already and didn't pick up a "how-to". Issuing the command three times doesn't freeze any more than the first column.

g

 

[boblarson]

Okay, here's how you do it.

You use:

Me.YourControlNameHere.SetFocus
DoCmd.RunCommand acCmdFreezeColumn

Me.YourSecondControlNameHere.SetFocus
DoCmd.RunCommand acCmdFreezeColumn

Me.YourThirdControlNameHere.SetFocus
DoCmd.RunCommand acCmdFreezeColumn

Above was tested and does work.

 

[ByteMyzer]

Hello, geoB,

Let me see if I can simplify this for you. Imagine that your datasheet form has the following fields: Field1, Field2, Field3, Field4, Field5. Let's say that you want to hide Field1, Field2 and Field3. Here's some code that you could use within your Form's Code Module, in the Open event, for Example:

[COLOR=navy]Private Sub[/COLOR] Form_Open(Cancel [COLOR=navy]As Integer[/COLOR])
 
    Me.Field1.ColumnHidden = [COLOR=navy]True[/COLOR]
    Me.Field2.ColumnHidden = [COLOR=navy]True[/COLOR]
    Me.Field3.ColumnHidden = [COLOR=navy]True[/COLOR]
 
[COLOR=navy]End Sub[/COLOR]

 

[boblarson]

Hello, geoB,

Let me see if I can simplify this for you. Imagine that your datasheet form has the following fields: Field1, Field2, Field3, Field4, Field5. Let's say that you want to hide Field1, Field2 and Field3. Here's some code that you could use within your Form's Code Module, in the Open event, for Example:

[COLOR=navy]Private Sub[/COLOR] Form_Open(Cancel [COLOR=navy]As Integer[/COLOR])
 
    Me.Field1.ColumnHidden = [COLOR=navy]True[/COLOR]
    Me.Field2.ColumnHidden = [COLOR=navy]True[/COLOR]
    Me.Field3.ColumnHidden = [COLOR=navy]True[/COLOR]
 
[COLOR=navy]End Sub[/COLOR]

Not hidden - frozen

 

[ByteMyzer]

Whoop, my bad, need more coffee!

 

[boblarson]

Whoop, my bad, need more coffee!

I hear you. I need something (I don't drink coffee - never have; can't stand the taste). But at the moment I'm feeling a little taking a nap (and I still have 4½ hours to work).

 

[boblarson]

In case this got lost in the shuffle:

Okay, here's how you do it.

You use:

Me.YourControlNameHere.SetFocus
DoCmd.RunCommand acCmdFreezeColumn

Me.YourSecondControlNameHere.SetFocus
DoCmd.RunCommand acCmdFreezeColumn

Me.YourThirdControlNameHere.SetFocus
DoCmd.RunCommand acCmdFreezeColumn

Above was tested and does work.

 

[geoB]

Either I'm thick or I wasn't clear. Here's the context in which I'm trying to freeze columns:

DoCmd.OpenTable "BB_Download", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdFreezeColumn

in which case any subsequent command beginning with Me. throws the error "Invalid use of Me keyword."

George

 

[boblarson]

Either I'm thick or I wasn't clear. Here's the context in which I'm trying to freeze columns:

DoCmd.OpenTable "BB_Download", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdFreezeColumn

in which case any subsequent command beginning with Me. throws the error "Invalid use of Me keyword."

George

You don't freeze it in the table. You freeze it on the form. What's this open table stuff?

 

[boblarson]

Assuming a table is open in datasheet view,

I see where the table came from. Don't use tables directly. Open a form in datasheet view with the table as the underlying recordsource. Then the code I gave you will work.

Users should not be given direct access to tables and I would say queries too, because you don't have much control over what they can do. USE FORMS.

 

[geoB]

I was wondering when the open table issue would come up.

The problem is that I need a generic solution for tables with identical first three fields but variable names and numbers for the remaining fields. This is because each installation of the application will have different contents for the same-named table. (This relates to a discussion in an earlier thread.)

I figure I can't build a form to show all the fields if I don't know their names. But I can open the table as read only. And I hope I'm smart enough to stay away from writing code to design then kill a form on the fly.

Problem unsolvable?

George

 

[boblarson]

Well, I think you're stuck at one field then.

 

[Kryst51]

Wow, perfect example of ajetrumpets sticky!

 

[georgedwilkinson]

The problem is quite solvable. Bob already gave the solution.

If all the field names are the same, put Bob's solution in a module and call it from your form. If not, modify Bob's solution to take control names as parameters. Problem solved.

 

[jenisys]

Boblarson is correct; however, in order to do this successfully, the columns in the range you want to freeze must be visible and wider then 0. So if you have some hidden columns, or columns set to 0 width, in the range of columns you want to freeze, you have to change their properties long enough to freeze them, then change them back after you are done freezing all columns.

Also, I have found that if the OnGotFocus event is fired in one of the columns you want to freeze, you have to set the OnGotFocus property to "" long enough to freeze the column, then set it back to [Event Procedure] (or whatever it is). Otherwise you will get an error when applying the freeze, and none of the subsequent columns will freeze.