Field Selector
By Richard Rost
15 hours ago
Let Users Choose Which Fields Appear on a Form In this lesson, I will show you how to build a Field Selector for a Microsoft Access continuous form so users can choose which columns to display without leaving gaps in the layout. We will create check boxes for selectable fields, use VBA to show or hide text boxes and labels, and reposition visible controls from left to right based on their widths. We will also use a form event to apply the selected field layout when the form opens. Marianne from Detroit, Michigan (a Gold Member) asks: I have a continuous form that shows several fields for each record, but different users only need to see certain columns. I can hide the fields they don't want, but it leaves big blank gaps in the middle of each row. Is there a way to close up those spaces automatically? MembersIn the extended cut, we will learn how to move form layout details into a reusable layout management system, store layouts in tables, create a management form, capture and apply saved layouts, and manage visibility, sizing, ordering, and arrangement. We will also discuss multiple forms, saved layouts, user-specific preferences, reset-to-default options, and a generic layout manager for the database. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, continuous form dynamic columns, continuous form hide fields, reposition controls VBA, compact continuous form layout, user selectable columns, checkbox show hide fields, control Left property, control Visible property, form layout manager, configurable list view
Subscribe to Field Selector
Get notifications when this page is updated
More InformationTranscript Have you ever hidden a field on a continuous form only to leave a big ugly hole right in the middle of your data?
Your users want fewer columns or want to pick the columns that they want to see, but you still want the form to look like you designed it.
Well, today we're going to talk about how to let your users pick which fields they see on your continuous forms.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today, we're going to make your continuous forms a little more user-friendly by letting people choose which fields they want to see. And the important part isn't just making unwanted fields disappear, we're also going to keep the fields that remain lined up nice and compact, with all those blank spaces in the middle just disappearing.
So, if you want the flexibility of a configurable list while keeping the control that a continuous form gives you, this is a handy little technique to have in your toolbox.
Today's question comes from Marianne in Detroit, Michigan, one of my Gold members. She says, "I have a continuous form that shows several fields for each record, but different users only need to see certain columns. I can hide the fields they don't want, but it leaves big gaps in the middle of each row. Is there a way to close up those spaces automatically?"
Yep, absolutely. With a little bit of VBA, you can let users choose what they want to see and keep the remaining fields neatly lined up.
Let's take a look at that in detail.
Now, hiding a field by itself is easy, but if you hide, let's say, the last name in the middle of a row, you get a big blank hole where last name used to be. It looks like somebody removed a tooth from the form.
What we want is for the fields that are still visible to slide over to the left and keep everything nice and compact.
So, what we're going to do is give the user simple little check boxes that they can choose the fields they want to see. Then VBA will show or hide both the text box and its matching label and will reposition the visible controls in order.
And then the end result is going to be a continuous form that behaves more like a user-configurable list or like Datasheet view, but it still looks like the form that you designed on purpose.
Now, some of you might be thinking, why not just use Datasheet view? And that's a perfectly fair question.
Datasheets already let users move columns around, resize them, hide them, sort, filter, and all that Excel-type stuff. And they're especially handy for subforms and for people who like working in a grid.
But a continuous form gives you much more control over the whole interface. You can have a proper form header with labels and buttons, you can put totals and other information in a form footer, you control formatting, add command buttons, and generally make it look and behave the way you want.
The tradeoff is that Access doesn't automatically manage those columns for you in a continuous form. So we're going to have to do it ourselves with a little VBA.
And the basic idea is simple. With each selected field, we'll make it either visible or not visible. Then we'll assign its Left property to the next available horizontal position. And after we place it, we increase the position by the field's width. Then we rinse and repeat. We do that for the next field.
And we'll also use a little quick-access trick to read the actual widths of our controls instead of sitting there trying to guess widths. Guessing measurements in Access is about as much fun as measuring a room with spaghetti noodles.
All right, before we get started, this is a developer-level video. So we're going to be using some VBA.
If you've never done any VBA programming before, go watch this video. It'll teach you everything you need to know to get started in about 20 minutes.
We'll definitely be using some If Then blocks. We're going to use my Status Box function to display information in a status box. So go watch this if you don't know what it is.
We're definitely going to be using some variables. You should know what the Visible property is and how to manipulate it with VBA. And we're going to be using little event-handler functions.
So if you're not familiar with any of this stuff, go watch these videos first and then come on back. These are all free. They're on my YouTube channel. They're on my website. Go on, go watch them. And I'll wait for you.
All right, I'm going to give you first a little preview of what we're going to be doing in class.
Here's my customer list. And I've got these fields set up on it right now: First Name, Last Name, State, and so on. And you can have a whole bunch of them. But we're just going to do these five.
By the end of the class, we're going to be able to do this. Click on Last Name. Boop. And Last Name disappears. And everything else slides to the left. See? Slide to the left.
Same thing with State. Goodbye. Credit Limit. Goodbye. I want to put State back. Okay. There you go.
That's what we're going to be doing. Now, you can line up a whole bunch of them. I just picked five to make it nice and simple because those are the five that I already had on there. But you could put 15 on there and just start by showing five of them and then let users add more.
You ready to see how to build this? All right. Here we go.
All right. Here I am in my TechHelp free template. This is a free database you can grab off my website if you want to. This is the pre-version before we put all that stuff in it.
Now, the first thing that we have to do is determine the widths of our controls.
Now, each one of these controls has a Width property. You open it up here, you'll see under Format, it's got a width. Where is Width? There's Width.
Now, here it shows up in inches or centimeters if you're in the part of the world that uses metric, which is a much better system, but I digress.
But internally, Access uses something called twips. What's a twip? I'm not going into it right now.
But I'm going to hijack this button, copy and paste, and put it over here. We're going to call this guy Get Widths.
Get Widths.
Right-click, Build Event. Yes, my code builder is in dark mode.
Get Widths button.
And here, I want to see, what are the widths of these different controls? So I'm just going to come in here and go Status. And let's say the Customer ID width. We're going to say, what is CustomerID.Width? That's the width of that box.
Save that. Come back out here. Close it. Close it. Open it.
And you can see in the status box, okay, so the Customer ID is 1200 twips across.
Now we need to know what all of these fields are: Customer ID, First Name, Last Name, State, Customer Since, and Credit Limit, whatever other fields you've got in here.
And we're going to store those as constants in our code. We don't have to keep looking them up and referring to them. And we're going to have the Status function build our VBA for us.
So instead of just displaying it like this, we're going to write it out like we want it written out for our code.
So here's what we're going to do. Instead of that, we're going to say literally right here, each of our fields, we're going to say:
Const CustomerIDWidth As Long = CustomerID.Width
So when we finish with this, it's going to write out all these constant statements for us.
I'm going to copy and paste this. What do we need? Last Name, State, Customer Since, and Credit Limit.
So now just put each customer in here. First Name, Last Name, State. Whoops. What do we got? Customer Since and Credit Limit.
And now put all of these over here. And then State and Customer Since and Credit Limit.
And I'm going to blank the box up here to StatusBox =. Actually, we have to do Forms!MainMenu!FStatusBox = blank. That way it's just a blank slate when we get to it.
Maybe put a beep at the end.
Save it. Debug Compile once in a while. Close it. Close it. Open it. Click the button.
There we go. There's all of our widths for all of the fields we need. And now we have it written in a constant that we can use to put in our VBA code. It's a series of constants.
Now, here's a little teaser. In the extended cut, we're going to loop through all the fields that happen to be on the form and programmatically read those into a settings table. So this is just the simple version.
Because later on, if I add another field or five, I don't want to have to keep redoing this.
So what's this stuff going to do? Well, copy this to your clipboard. Copy.
We're going to come over here. We're going to go into Design View again. We're going to open up the code for this. You can go back into this button if you want to.
And let's say right here, we're going to make a function that we're going to call to move the display fields around based on what's visible or not.
Private, or, yeah, probably you can make it Private unless you want other forms to be able to call this function. But for now, Private's fine.
Private Function MoveDisplayFields()
And then, why a function instead of a subroutine? It's not returning a value. That's so we can use it as an event handler. That's what that other video was for. Go watch that.
And here, I'm going to just paste in those constants. And now we've got the values. We've got the widths of all of our controls now.
Now, we're going to come back to this. Put a pin in this for now.
The next step is to make a series of check boxes down here. We can move this over to the side. Make a series of check boxes that represent each of our fields.
Now, we're not going to hide the Customer ID for this video. Just leave it there. You pretty much always want to see it anyways. But that one's fixed.
But for the rest of them, we're going to make check boxes.
So, Form Design, find a check box, drop it right now.
And let's make it so we can actually read it. I'll make this white.
First Name.
And this is a label, so you can put First Name in there.
Now, what you name these things is going to be important. We're going to name this whatever the field name is, ending with CHK.
So open this guy up. Come in here. This is going to be FirstNameCHK.
And let's make its Default Value Yes. By default, we're going to see all the fields. You could change that later if you want to.
Save it. And move this over here.
And we need five of these. Copy, paste, paste. Copy, paste, paste. Copy, paste. Oh, I got that copy-paste problem. Copy, paste.
Yeah. That's my video recording software. There it goes. It's a bug with my system. Other people have reported this to them. I think that the Access team is aware of it. I think it has to do with the Windows Clipboard History. Plus, my video recording software makes it even worse.
So if you see me struggling with copy and paste, that's why. I'm not an idiot. Well, most days, I'm not an idiot.
I'm going to put two of these over here just to save some space.
So we've got First Name. We've got Last Name. We've got State. We got, what else we got? Here we've got Customer Since. And we've got Credit Limit.
And now we need to actually name these boxes.
So we've got FirstNameCHK. This is LastNameCHK. Come on all the way up here. There we go. LastNameCHK.
This guy, StateCHK. Customer Since, it's got to be the same CHK. Actually, it doesn't really have to be the same for this video. But for the next video, for the extended cut, you're going to want these to be the same. Trust me, because programmatically, we're going to look at them.
CreditLimitCHK.
Now, we're also going to do the same thing with the labels because we also want the labels to disappear.
So this guy here, even though this one isn't disappearing, let's keep everything consistent. This will be CustomerIDLabel. This guy will be FirstNameLabel. LastNameLabel. You are StateLabel. CustomerSinceLabel. And CreditLimitLabel.
And yes, for those of you wondering why I'm making you watch me do each of these, because back in the day, I used to do it where I would say, "Okay, I'm going to pause it now and just do all the labels," so you didn't sit there and watch me.
But I'd get more complaints about that than if I made you just sit here and watch me. So now, you just got to sit here and watch me. If you don't like it, take a sip of your coffee.
So we've got our labels all set. We've got our checkboxes all built. Now it's time to go back to the code.
And we're basically going to say, okay, but if this guy is unchecked, hide that box. We'll do that first, and we'll worry about shifting everything around.
So let's go back to our code.
Now I've got a little button up here on my Quick Launch Toolbar. Put this on here. If you haven't modified your Quick Launch Toolbar, the View Code button is so handy to go right back into the code editor.
Or you just leave this thing running. Usually, I just minimize it when I'm done, or I click like this. So you can see it's still running there behind it. Then I can just come over here and go, and I'm right back into my code editor.
All right, so right here, we'll do First Name. We're going to say:
FirstName.Visible = FirstNameCHK
Do we need an If Then block for that? No, not really, because the Visible property will just get the value from whatever's in the checkbox.
And then we'll do the same thing with the label:
FirstNameLabel.Visible = FirstNameCHK
And then we just copy and paste this for all of our fields. LastName, State, CustomerSince, and CreditLimit.
We got LastName. This one's easy. Last. Last. Last. Last.
And then we got the State. It goes fast if I can type. If I forget how to type, then. No, this is just State. State, StateLabel, State.
Okay. That's why you make sure everything capitalizes as you're going along, too.
Credit. CreditLimit. CreditLimitLabel. CreditLimitCHK. CreditLimit.
And Customer Since. CustomerSince. CustomerSince. CustomerSince.
Now we've got this in our MoveDisplayFields. How do we call this thing? Well, we're going to call this anytime someone clicks one of those boxes.
So we're going to do this. We're going to copy this whole thing with the open, close parentheses. Copy that.
Debug Compile once in a while. Save it.
Come back out here. Now I'm going to select each of these boxes. Click. Hold down the Shift key. Click. Click. Click.
Go to Events. Go to On Click or After Update, either one. On Click equals that. And that's why we made it a function.
Save it. Close it. Open it.
And now watch this. Click on Last Name. Oh, look at that. It disappeared. And Customer Since. Oh, it disappeared.
Now see what I was talking about before. We made it not visible, but now it looks like we're missing two teeth.
So now what we need to do is slide over the State and slide over Credit Limit to fill these holes.
Back to our code editor.
Now, right up here, we're going to say:
Dim X As Long
X will be our placeholder for where we are horizontally.
And since First Name is always the first field, yeah, you could start. I guess technically, we really didn't need CustomerIDWidth. Actually, did I put it up here? Yeah. It's the last one. Oh, because remember, the Status Box display stuff is backwards.
So CustomerIDWidth, something about it. Technically, we don't need that. But again, it's nice to stay consistent.
So we're going to start:
X = FirstName.Left
The left position of that First Name box, this guy here.
If we look at it, every property has a Width, and every property also has a Left. That's where it starts left, the X coordinate basically, going across. And again, it's in twips, but you don't need to know what it is. We just need VBA to know what it is.
Now, right here, we have shown it or hidden it.
Now we need to say, if it's visible, slide X over to the right equals to the width of this field.
And again, just to remain consistent, I want each of these blocks to be the same. Even though First Name doesn't move, we want to be consistent, just in case you add something in the future to the left of this.
So we're going to say:
If FirstNameCHK Then
FirstName.Left = X
FirstNameLabel.Left = X
X = X + FirstNameWidth
End If
Now, that doesn't make any sense for First Name since it doesn't move, but again, we're just doing this to be consistent.
So we're going to copy this now and come down after Last Name. And I'm going to paste that there.
And we're going to say:
If LastNameCHK Then
LastName.Left = X
LastNameLabel.Left = X
X = X + LastNameWidth
End If
So if First Name is hidden, what happens? Well, X starts at FirstName.Left. X is no longer visible, so X never moved. And now we're putting Last Name in the First Name spot.
Let's see if this works.
Save this. Debug Compile. Come back up. Close it. Open it.
Now I'm going to hide First Name. Boop. And look at that. Last Name slid over because First Name didn't increment X.
Now that works. That's pretty cool.
So now we just need to do that same thing to the other fields. Create State, and then Credit Limit and Customer Since.
So State, StateCHK, State, State, State.
CreditLimit, CreditLimitCHK, CreditLimit, CreditLimit.
And CustomerSince, CustomerSinceCHK, CustomerSince, CustomerSince.
And see, this is where we save and Debug Compile and realize that I missed one. No, okay, I didn't miss any.
Always close it and reopen it. Get a fresh start.
First Name, Last Name, State, Customer Since, Credit Limit. Everybody's hidden.
I wanted to see just Last Name and Customer Since. Look at that. Isn't that cute?
If you wanted to, you can move this total over too. If you really wanted to be cool, just get this guy and move it over with Customer Since.
You'd have to probably slide all these buttons down to give him the slide. But there's all kinds of stuff you could do with this, folks.
Now, this doesn't make sense because the form is only this wide. But you could turn the horizontal scroll bar on.
Let's say you only want your form to be this wide. But now you want to see First Name and Credit Limit. Look, see, First Name and Credit Limit. See, and you've got just those.
But again, I'm thinking of a form of like 15, 20 fields in it. You start off displaying five. And obviously, if you're going to start this form where, let's say, you don't normally see Credit Limit.
So let's say you want Credit Limit to normally not be visible. Just set its Default Value to No.
And what you want to do is you want to call this guy in the form's On Open event or On Load, either one. So you're going to MoveDisplayFields as soon as the form opens.
So now, boom, look at that. It opens like that.
And you could even start the width of the form here if you want to. Turn off Auto Resize if that's the case, too. So come in here, go to the form's property, go to Auto Resize, turn that off. Otherwise, it's going to resize to the max width.
Just for class, I'm going to get rid of this guy. He's annoying. Maybe we'll slide you over here. Maybe we'll, I don't know, whatever.
So now, when you open it, resize it where you want it. Like you want it to start there.
And now, when it opens, you get that. It's not auto-resizing.
And you can hide what you don't want and show what you do.
And you can make different configurations for different users. There's all kinds of stuff you could do with this. This is really neat stuff.
And see, now you can make this look more like a spreadsheet that your users are used to. We only have used Excel for the past 20 years.
Now, today's extended cut, you're not going to want to miss this one. We're going to take this a whole lot further.
The version that we just built is fine for a handful of fields. Once you start adding a lot more columns, changing widths, rearranging controls, or building similar forms elsewhere in your database, a hard-coded layout like this can become a maintenance headache.
And nobody wants to go hunting through VBA every time they decide that the phone number field needs to be just a little bit wider. That's how perfectly innocent afternoons disappear and become spaghetti code.
So we're going to move the layout information out of the form code and into a reusable layout management system.
We'll build a place to store each form's layout in detail in a management form so you can work with that information more easily.
Then we'll create tools to capture the form's current layout and apply saved layouts back to the form. That means visibility, sizing, ordering, and arrangement can be stored, changed, and used without having to rewrite the form's layout logic every time something changes.
We'll also discuss where this could go from here: multiple forms, different saved layouts, user-specific preferences, reset-to-default options, and eventually a much more generic form layout manager that can work across your entire database.
So if you want to turn this from a neat little trick into a flexible, reusable system, that's coming up in today's extended cut.
First, stick around. Everybody else, what are you waiting for? Become a member. You'll get this lesson and a whole lot more Access goodness.
Silver members and up get access to all of my extended cut videos. Not just this one, all of them. There's over 400.
In fact, members, this is something you've been asking for for a while. Is there a place to find all the extended cuts?
Well, I do have a playlist set up on my YouTube channel, but on my website, I just added this so you can go down and look for all the extended cuts.
And this will show you the ones you haven't seen yet. I notice I haven't seen a lot of my own extended cuts.
But they're all on here. So come check them out. There's hundreds of them. Lots of stuff.
Now, if you want to learn more, I've got a bunch more TechHelp videos available, including this one where I show you how to hide a field.
Now, this is a little bit different. We're not hiding the entire field, we're not hiding the whole column. But let's say an order doesn't have a ship date. Well, you can hide that field instead of displaying nothing there. That's kind of neat.
This video teaches you how to arrange forms in Tabular view.
This video goes into more detail on that form Open event, On Open, On Load.
And if you want to learn more about building custom dynamic forms in Access Developer 37, I teach you how to do something very similar, but we use a list box columns instead of a continuous form.
You can do the same thing. You can hide or display different columns in a list box on your form. This is great for searching and sorting and listing stuff.
You can have a whole big giant list of fields over here, and you just check the ones on or off using a multi-select list box, and it'll display whatever you want there. That's pretty cool.
That's in Access Developer 37.
And in Access Developer 58, I just finished this one recently. This is the Kanban board, where you can not only have multiple different list boxes in here arranged differently, they can resize, you can reorder stuff, you can move items, click and drag from box to box. This one's got a lot of stuff in it, so check that out.
So that, folks, is going to do it. That's your TechHelp video for today. I hope you learned something.
Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the extended cut. Intro In this lesson, I will show you how to build a Field Selector for a Microsoft Access continuous form so users can choose which columns to display without leaving gaps in the layout. We will create check boxes for selectable fields, use VBA to show or hide text boxes and labels, and reposition visible controls from left to right based on their widths. We will also use a form event to apply the selected field layout when the form opens. Quiz Q1. What problem does simply hiding a control on a continuous form create?
A. It leaves a blank gap where the control was
B. It deletes the field from the table
C. It prevents users from editing records
D. It converts the form to Datasheet view
Q2. What is the main goal of the technique demonstrated in the video?
A. Allow users to choose visible fields while keeping the form compact
B. Automatically delete unused fields from the record source
C. Convert a continuous form into a report
D. Create a separate form for every user
Q3. Why might you use a continuous form instead of Datasheet view?
A. Continuous forms provide greater control over headers, footers, buttons, and formatting
B. Datasheet view cannot display more than five fields
C. Continuous forms do not require a record source
D. Datasheet view cannot be sorted or filtered
Q4. What property is used to hide or show a text box or label with VBA?
A. Visible
B. Enabled
C. Locked
D. TabStop
Q5. Why should the matching label be hidden along with a text box?
A. So the field heading does not remain visible without its data control
B. So the label can be deleted safely
C. So the form can be converted to a report
D. So the field value becomes read-only
Q6. What is the purpose of the check boxes on the form?
A. They let the user select which fields should be displayed
B. They validate whether the data in each field is correct
C. They filter records based on field values
D. They change the underlying table structure
Q7. Why are check boxes named using a consistent pattern such as FirstNameCHK?
A. Consistent names make it easier to identify related controls in code
B. Access requires every check box to end in CHK
C. The name automatically binds the check box to the table field
D. It causes the check box to save its value permanently
Q8. What does the variable X represent in the MoveDisplayFields function?
A. The next available horizontal Left position for a visible field
B. The number of records displayed on the form
C. The width of the entire form
D. The selected record's primary key value
Q9. What property determines a control's horizontal position on a form?
A. Left
B. Top
C. Width
D. Height
Q10. After placing a visible control at position X, what should the code generally do next?
A. Increase X by the width of that control
B. Set X back to zero
C. Hide the next control
D. Increase the form height
Q11. Why does the code only increase X when a field is selected to be visible?
A. Hidden fields should not reserve horizontal space
B. Hidden fields must always move to the far right
C. Access cannot read the width of hidden controls
D. Labels cannot be moved when controls are hidden
Q12. Which event can be used to run the layout adjustment after a user clicks a field-selection check box?
A. On Click or After Update
B. On Current only
C. On Delete only
D. On Error only
Q13. Why is MoveDisplayFields created as a Function in the example rather than a Sub?
A. It can be used directly as an event handler
B. Functions are the only VBA procedures allowed on forms
C. A Sub cannot change control properties
D. A Function automatically saves the form design
Q14. When should MoveDisplayFields be called to apply the initial field layout when the form opens?
A. In the form's On Open or On Load event
B. Only when the form closes
C. Only after a record is deleted
D. Only when the database starts
Q15. What can be adjusted to prevent Access from automatically expanding the form to its full design width?
A. Turn off the form's Auto Resize property
B. Turn off the Allow Edits property
C. Set the form's Recordset Type to Snapshot
D. Set every text box to Locked
Q16. What is one limitation of the hard-coded approach shown in the main lesson?
A. It becomes harder to maintain when fields, widths, or layouts change frequently
B. It cannot hide labels
C. It cannot use check boxes
D. It only works with one record
Q17. What improvement is suggested for a more reusable layout system?
A. Store layout details outside the form code, such as in a settings table
B. Replace all text boxes with command buttons
C. Store every field value in a temporary variable
D. Use a separate table for each visible column
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. Summary Today's video from Access Learning Zone shows you how to let users choose which columns they want to see on a Microsoft Access continuous form, while automatically closing up the empty spaces left behind by hidden fields.
A common problem with continuous forms is that hiding a control only makes the control invisible. It does not cause the controls to the right of it to move. If a user hides a field in the middle of a row, the result is a large blank area where that field used to be. The form can end up looking incomplete or poorly designed.
In this lesson, I show you how to give users a set of check boxes that determine which fields are displayed. When a user changes one of those check boxes, VBA hides or shows the corresponding text box and its label. It then repositions the remaining visible controls so they stay neatly arranged from left to right.
This gives you some of the flexibility of Datasheet view while preserving the advantages of a continuous form. Datasheets are useful because users can hide columns, resize them, move them, sort records, and filter data. However, a continuous form gives you much greater control over the interface. You can include custom headers, buttons, labels, formatting, totals, footers, and other elements that are not as easy to manage in a datasheet.
The basic idea is straightforward. I keep track of the horizontal position where the next visible field should be placed. Each visible control is moved to that position, and then the horizontal position is increased by the width of that control. Hidden controls do not take up any space, so the next visible control moves into the available space.
This lesson uses VBA, variables, If Then blocks, the Visible property, control properties, event procedures, and a Status Box function. If you are new to VBA, I recommend reviewing the beginner VBA lessons first so you understand how event handlers and control properties work.
For this example, I use a customer list continuous form with fields such as CustomerID, FirstName, LastName, State, CustomerSince, and CreditLimit. I leave CustomerID visible at all times because an identifier is usually useful to keep on the form. The other fields can be turned on or off by the user.
The first step is to determine the width of each control. Every control on an Access form has a Width property. Although Access may display the width in inches or centimeters in the property sheet, VBA works internally with twips. You do not need to calculate twips manually. VBA can read the Width property directly from each control.
I create a temporary button that reads the width of each display control and sends that information to the Status Box. Instead of merely displaying the values, I use the Status Box to produce the constant declarations needed for the form module. This is a quick way to collect the widths for FirstName, LastName, State, CustomerSince, CreditLimit, and any other fields I want to manage.
Once I have the control widths, I add constants to a private function in the form module. This function is responsible for arranging the display fields. Keeping the widths as constants makes the positioning logic easier to read and maintain.
Next, I add a check box for every optional field. Each check box represents a field that the user may choose to display. For example, I create check boxes for FirstName, LastName, State, CustomerSince, and CreditLimit.
I use a consistent naming convention for the check boxes. Each check box uses the field name followed by CHK, such as FirstNameCHK, LastNameCHK, and StateCHK. Consistent names are especially important if you later decide to build a more automated version of this system.
I set the Default Value for each check box to Yes if that field should normally be visible. If a field should normally be hidden, such as CreditLimit in some databases, I can set its Default Value to No instead.
I also make sure that the labels have consistent names. For example, the label associated with FirstName is named FirstNameLabel, and the label associated with LastName is named LastNameLabel. This matters because hiding the text box alone is not enough. The label should disappear as well.
Inside the layout function, I first set each text box and its matching label to visible or hidden based on the value in the corresponding check box. The Visible property can be assigned directly from the check box value, so a checked box displays the field and an unchecked box hides it.
After handling visibility, I begin the repositioning process. I use a Long variable to store the next available horizontal position. I initialize that variable using the Left property of the first movable control. The Left property tells Access where a control begins horizontally on the form.
For each field, I check whether its associated check box is selected. If the field should be visible, I assign both the text box and its label to the current horizontal position. I then add that field's width to the horizontal position variable so the next visible field will be placed immediately after it.
The same process is repeated for every optional display field. If FirstName is hidden, LastName moves into the FirstName position. If LastName is also hidden, State moves into the next available position. If several fields are hidden, every remaining visible field shifts left to fill the gaps.
This is why it is useful to keep the same structure for every field, even if a particular field does not normally move. Consistent logic makes future changes much easier. If you later add another field to the left side of the form, the layout function is already organized to handle it.
Each check box needs an event procedure so the layout function runs whenever a user changes the selection. I assign the same function to the appropriate event for each check box. When users select or clear a check box, the form immediately updates the visible controls and shifts the remaining fields into place.
It is a good idea to periodically save the form and use Debug Compile in the VBA editor. This helps catch spelling mistakes, mismatched control names, and other errors before testing the form.
If you want the form to open with the correct fields already displayed, call the layout function from the form's On Open or On Load event. This is especially important if some check boxes default to No. Without this step, the form may briefly show fields in their original design positions before the layout logic is applied.
You may also want to turn off the form's Auto Resize property if you want the form to retain a particular width when it opens. This can be useful if users commonly display only a few fields. You can leave the horizontal scroll bar enabled if users may choose additional fields that extend beyond the normal form width.
This technique works especially well when you have a large number of available fields but users only need to see a few at a time. You might have fifteen or twenty possible columns while showing only five by default. Different users can select different fields based on their needs, and the form will remain organized instead of showing empty gaps.
You can also expand this idea further. For example, you could save different column configurations for different users, create preset layouts for different job roles, provide a reset-to-default option, or store each user's preferences in a table.
Also, in today's Extended Cut, we will take this idea beyond a hard-coded layout. I will show you how to move form layout information out of the VBA code and into a reusable layout management system. We will create a place to store information about each form's controls, including visibility, size, order, and arrangement.
I will also show you how to capture a form's current layout and apply a saved layout later. This makes it easier to maintain forms when fields are resized, reordered, or added. Instead of editing a long block of VBA every time a control changes, you can manage layouts using stored configuration information.
From there, this system can be expanded to support multiple forms, multiple saved layouts, user-specific settings, reset options, and a more general form layout manager that can be used throughout your database.
This is a useful technique when you need the flexibility of configurable columns but still want the design control provided by a continuous form.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends. Topic List User-selectable columns on continuous forms
Adding field visibility check boxes
Showing and hiding fields with VBA
Showing and hiding matching field labels
Reading control widths with the Width property
Using control Left positions for form layout
Repositioning visible controls to close gaps
Applying column layout when the form opens
Preventing automatic form resizing Article Microsoft Access continuous forms are useful when you want more design control than Datasheet view provides. You can add a form header, command buttons, custom formatting, totals, and other interface elements. However, continuous forms do not automatically rearrange controls when a field is hidden. If you hide a control in the middle of a row, Access leaves an empty gap where that control was.
You can solve this by allowing users to choose which columns they want to see and then using VBA to reposition the visible controls. The result is a configurable continuous form that stays compact instead of developing blank spaces between fields.
The basic idea is to give the user a checkbox for each optional field. For example, a customer list might include First Name, Last Name, State, Customer Since, and Credit Limit. Each checkbox represents whether its matching field should be displayed.
When the user checks or unchecks one of these options, your form should run a layout routine. That routine should first determine whether each field is selected. If a field is not selected, the routine hides both the text box and its corresponding label. Hiding the label is important, especially if your labels are in the form header, because otherwise the header will still show column titles for fields that are no longer visible.
After setting visibility, the routine should reposition the remaining visible controls from left to right. Start with a horizontal position variable based on the Left property of the first optional field. Then process each field in the order you want it to appear.
For each field, the code should check whether that field is selected. If it is selected, move the text box to the current horizontal position and move its label to the same position. Then increase the horizontal position by the width of that field. If the field is not selected, do not move the position forward. The next visible field will therefore occupy the hidden field's former space.
For example, imagine a form with First Name, Last Name, State, Customer Since, and Credit Limit. If Last Name is hidden, State should move into the position previously used by Last Name. If State is also hidden, Customer Since should move immediately after First Name. This creates a compact row without gaps.
To make this reliable, use consistent naming conventions. Name each checkbox after the field it controls, such as FirstNameCHK or CreditLimitCHK. Likewise, give each label a predictable name, such as FirstNameLabel or CreditLimitLabel. Consistent names make the layout code easier to read and maintain, especially when you later add more fields.
You also need to know the width of each control. Access stores control positions and widths internally in twips, even though the property sheet may display inches or centimeters. Your layout routine should use the actual Width property of each text box rather than relying on guessed measurements. This ensures that the next control begins immediately after the previous visible control ends.
Run the layout routine whenever a user changes one of the field-selection checkboxes. You should also run it when the form opens or loads. This is particularly important if some checkboxes default to No, meaning their fields should be hidden when the form first appears.
You may want to keep certain fields fixed. For example, a Customer ID is often useful enough that it should always be shown. In that case, leave it out of the checkbox options and begin repositioning optional fields immediately to the right of the fixed field.
If users may choose very different combinations of columns, consider the overall width of the form. A form that has many possible fields may need a horizontal scroll bar when several wide fields are displayed. You can also disable automatic resizing if you want the form to open at a controlled width rather than expanding to its widest possible layout.
This technique is especially useful when you have many fields but different users need different information. One user may only need a name and phone number, while another may need state, account status, balance, and credit limit. Rather than maintaining separate forms for each situation, you can let users configure one continuous form to match their needs.
For a small form with only a few fields, it is reasonable to handle the controls directly in one form-level layout routine. For larger projects, consider storing layout details outside the form code. A layout table can store information such as the form name, control name, label name, display order, saved width, and default visibility. This lets you create reusable tools that capture a form's layout, restore a saved layout, provide user-specific preferences, and offer a reset-to-default option.
The key principle is simple: hiding a control is only half the job. After hiding it, recalculate the positions of the visible controls so every remaining field shifts left into the next available space. That gives users flexibility without making the form look unfinished. Primary Topics
Access continuous forms, user-selectable field visibility, VBA control Visible property, dynamic control Left positioning, control Width property, check box event handlers, compacting hidden columns Secondary Topics
form Open or Load event, default check box values, labels paired with text boxes, form Auto Resize, horizontal scroll bars, Datasheet view comparison |
