Freezing columns in a continuous subform

[Bill Murphy]

I have a continuous tabular form as a subform. Is it possible to freeze
several leftmost columns, like in Excel, so that they will not scroll off
the screen when the user scrolls the form horizontally to the right?

Bill

 

[Wayne Morgan]

This can be done in datasheet view, but I don't know of any way to do it in
continuous form view.

 

[TC]

You could maybe fake it by having >two< subforms, side by side, with the
"frozen" columns in the leftmnost one (with no horizontal scrollbar required
or displayed), & the other columns in the rightmost one (with a scrollbar
required & displayed). But you'd have to write code to keep them
synchronized, and that would maybe not be trivial.

HTH,
TC

 

[Bill Murphy]

I substituted a datasheet as my subform, and called the following from the
subform's load event:

Sub CheckFrozen(strQueryName As String)
Dim dbs As Object
Dim qdf As Object
Dim prp As Variant
Const DB_Integer As Integer = 5
Const conPropertyNotFound = 3270 ' Property not found error.
Set dbs = CurrentDb ' Get current database.
Set qdf = dbs.QueryDefs(strQueryName) ' Get object for query.

On Error GoTo Frozen_Err

If qdf.Properties("FrozenColumns") <> 5 Then ' Check property.

qdf.Properties("FrozenColumns") = 5
qdf.Properties.Refresh

End If

Frozen_Bye:
Exit Sub

Frozen_Err:
If Err = conPropertyNotFound Then ' Property not in collection.
Set prp = qdf.CreateProperty("FrozenColumns", DB_Integer, 1)
qdf.Properties.Append prp
Resume Frozen_Bye
End If

End Sub

SubCheckFrozen is based upon the code found in the Visual Basic Help file.

Unfortunately, when the subform opens, no columns are frozen. Any thoughts
on how to make this work?

Bill

 

[Stephen Lebans]

Here is a previous post of mine on this subject.

From: Stephen Lebans
([email protected])
Subject: Re: Freeze Column?
View: Complete Thread (3 articles)
Original Format
Newsgroups: microsoft.public.access.forms,
microsoft.public.access.formscoding,
microsoft.public.access.modulesdaovba,
microsoft.public.office.access.forms,
microsoft.public.office.access.formscoding,
microsoft.public.office.developer.vba
Date: 2003-12-24 11:53:00 PST


For programmatic control for forms in Datasheet view my standard
response is:

1) Michael Kaplan has an excellent article on manipulating Datasheets
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart
01/html/sa01b1.asp

2) An API solution is here:
http://www.lebans.com/autocolumnwidth.htm
The AutoSizing project also contains a method to FREEZE the
ColumnWidths.




--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

 

[Bill Murphy]

Thanks Stephen. In Michael Kaplan's article I found a tip which led me to
the answer. Here's some sample code that I am using in the load event of a
data sheet subform which works well:

' Freeze each column needed

Me![Project].SetFocus
DoCmd.RunCommand (acCmdFreezeColumn)

Me![Lender#].SetFocus
DoCmd.RunCommand (acCmdFreezeColumn)

Me![Loan#].SetFocus
DoCmd.RunCommand (acCmdFreezeColumn)

All three columns will be frozen at the left of the data sheet, and the user
can scroll horizontally for all other columns in the data sheet.

Bill