One of the nice things about a continuous form is that you can make it look exactly the way you want. One of the annoying things about a continuous form is that when you hide a control in the middle of a row, Access leaves a big ugly gap behind. Your users may only want to see a few columns, but you still want the form to look intentional and not like somebody knocked out a tooth.
The solution is to give users a set of check boxes that determine which fields they want to see, then use a little VBA to both hide unwanted controls and slide the remaining controls to the left. The result behaves a little like a configurable datasheet, but you retain all the advantages of a continuous form: headers, footers, buttons, totals, formatting, and complete control over the interface.
Datasheet View is still a perfectly good option in many situations. Users can hide, resize, reorder, sort, and filter columns without you having to write a single line of code. If all you need is a spreadsheet-style grid, a datasheet may be the better answer.
But continuous forms are better when you want a polished interface. You can place command buttons in the header, put totals in the footer, use custom colors and formatting, add calculated fields, and make the form look like part of your application instead of a raw table that escaped into the wild.
The basic idea is straightforward. For every optional field on the form, create a corresponding unbound check box. If the check box is checked, that field is displayed. If it is unchecked, the field is hidden. You should also hide the field's matching label at the same time, otherwise you end up with headings that have no data below them, which is only slightly less confusing than the original problem.
A consistent naming convention makes this much easier. For example, a text box named FirstName can have a check box named FirstNameCHK and a label named FirstNameLabel. The exact names are up to you, but consistency matters. Future You will appreciate it, and Future You is often crankier than Present You.
Set the Default Value of each check box to Yes or No depending on whether that field should normally appear when the form opens. You might always show Customer ID, for example, but leave something like Credit Limit hidden by default unless the user specifically asks to see it.
The VBA routine has two jobs. First, it assigns each field's Visible property from the value of its check box. Conceptually, it is as simple as setting the field and its label to visible when the check box is true, and invisible when it is false.
That alone hides the data, but it does not close the gaps. The second job is where the useful part happens. The routine keeps a variable that represents the next available horizontal position on the form. In other words, it tracks where the next visible column should begin.
Start that position with the Left value of the first optional field. Then work through your fields in display order. For each field that is selected, move both the control and its label to the current position. After placing the field, increase the current position by that control's width. Then move on to the next field.
So if First Name is visible, it occupies the first available space and advances the position by its width. If Last Name is hidden, it does not advance the position at all. State, Customer Since, Credit Limit, or whatever comes next gets placed immediately after the previous visible field. No blank spaces. No missing teeth.
You will want to use the controls' actual Width values rather than guessing at measurements. Access stores these measurements internally in twips, and there is no need to sit there manually calculating them like you are measuring a kitchen floor with spaghetti noodles. A quick temporary VBA routine can read each control's Width property and help you create constants for the layout routine.
Using constants for widths is fine for a small form with a handful of fields. It keeps the layout logic easy to read and makes the routine predictable. If you later resize a control, though, remember that the saved width in your code must be updated too. This is one of those tiny maintenance details that is easy to forget until one column suddenly overlaps another.
Run the repositioning routine whenever a user clicks or updates one of the selector check boxes. It is also a good idea to call the same routine from the form's Open or Load event. That way, if certain fields default to hidden, the form opens in its compact layout immediately instead of briefly showing gaps until the user clicks something.
If the form can potentially display many fields, consider the width of the form itself. You may want to turn off Auto Resize and allow a horizontal scroll bar, especially if users can select more columns than will comfortably fit on screen. Otherwise, Access may happily stretch the form wider than you intended.
This technique can also be expanded in some useful directions. You can save each user's preferred field selection, provide different layouts for different job roles, add a Reset to Default button, or store layout information in a table rather than hard-coding it in VBA. Once you start managing lots of forms and columns, a reusable layout system becomes much easier to maintain than a pile of one-off procedures.
For a small continuous form, though, the simple approach works beautifully: let users choose fields with check boxes, hide the controls and labels together, and reposition every visible field using its Left and Width properties. It gives users flexibility without sacrificing the carefully designed look of your form.
The embedded video includes the full walkthrough, including the VBA implementation, reading control widths, wiring up the events, and seeing the fields slide into place as users make their selections.
Live long and prosper,
RR
No comments:
Post a Comment