Freeze Columns in a Form?

[Paul H]

I've created a form that I am displaying as a Continuous Form so I have limited real estate on which to display the data entry fields needed. This means the right side of the form may scroll offscreen. As a matter of fact this is likely to happen. Is there a way to lock the first 2 or 3 fields onscreen as the user tabs through the data entry fields? Are their other alternatives I should explore?

[rpeare]

I don't think you can lock items by column, someone probably has better knowledge than I do regarding datasheet type subforms (I design my own subforms to show what/where I want). You can still have a continuous form and design a form that shows everything on the space you have (assuming you don't have something huge) which may be an option for you.

[pbaldy]

To my knowledge it can't be done. The workarounds I've seen use 2 subforms kept in sync with each other. There are a couple here:

http://www.access-programmers.co.uk/...d.php?t=208193

[boblarson]

You CAN freeze columns in a datasheet however (I've done it for a project before).

See this article:
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[Paul H]

Thanks for these idea, I will look into both suggestions and see if I can adapt for my project. I'll come back once I've digested this material.

The bit I've seen of your suggestion Paul behaves very much like I would want my form to behave. I'll let you guys know what I come up with.

Paul

[Paul H]

Paul,

I found one that works on the UK site using two subforms and a module full of code I just plugged into my database. I'm happy with the way it behaves except for the way column widths work in datasheet view. I looked over some code that Bob pointed to that talks about this.
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Before I try to wade through it does anyone have a simple way of setting the column width property of the data sheet? I undertand that this needs to be done at runtime using DAO. Finding DAO somewhat bewidering, I'd like to keep this simple as possible. A bit of generic code would be nice.

Thanks,

Paul Hammond
Richmond, VA

[boblarson]

Before I try to wade through it does anyone have a simple way of setting the column width property of the data sheet? I undertand that this needs to be done at runtime using DAO. Finding DAO somewhat bewidering, I'd like to keep this simple as possible. A bit of generic code would be nice.

Umm, no you don't need DAO to set the column width property of the datasheet view of a FORM (so use that instead of just a query or table displayed as a datasheet).

Me.ControlNameHere.ColumnWidth = 1000

Or for the subform from the main form:

Me.SubformControlNameHere.Form.ControlNameHere.ColumnWidth = 1000

adjust the number for the width to get the size you want. It is in TWIPS.

[Paul H]

Me.ControlNameHere.ColumnWidth = 1000

That's certainly more understandable than the custom functions I was looking at, meaning I can do it myself. Without mucking it up to much is there a way to cycle through them and dynamically set each one to best fit? I came up with this.

Dim ctl As Control
For Each ctl In Me.Controls
 
    ctl.SizeToFit
 
Next ctl

It lacks two things.

1. I want to limit it to the text boxes (not sure how) and
2. It doesn't do anything thing.

[boblarson]

Here you go:

Dim ctl As Control
 
For Each ctl In Me.Controls
   If ctl.ControlType = acTextBox Then
      ctl.ColumnWidth = -2
   End If
Next

The -2 is the key.

[Paul H]

Neat as a freak. Thanks!