Change Row Height
By Richard Rost
3 years ago
Change the Row Height in Continuous Forms
In this Microsoft Access tutorial I am going to teach you how to allow your users to change the height of the rows in a continuous form to their liking.
Nancy from Bremerton, Washington (a Platinum member) asks: Is there a way to allow my users to change the height of the rows in a continuous form? Some of my users like them small so they can see more records. Others like them bigger so they can see more info in each field (especially for long text fields). I tried using a split form, but they don't seem to hold their properties.
Members
There is no extended cut, but here's my 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!
Prerequisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Continuous form height, row height, continuous form with differing row heights, continuous form with different row heights, Row height on continuous form, split form
Intro In this video, I will show you how to let users temporarily change the row height in a Microsoft Access continuous form, allowing for easier viewing of long text fields. We'll talk about why split forms are not ideal for this purpose and walk through adding simple buttons to make the rows taller or shorter using VBA. I'll cover how to adjust the height of text boxes and the detail section, add basic safeguards, and even optionally adjust font sizes. This tutorial is designed for developers comfortable with basic Microsoft Access VBA programming.Transcript Today, I'm going to show you how to let your users change the row height in a Microsoft Access Continuous Form. This one's been on my list for a while.
Nancy from Burremerton, Washington, a platinum member, writes: Is there a way to allow my users to change the height of the rows in a continuous form? Some of my users like them small so they can see more records, others like them bigger so they can see more info in each field, especially for long text fields. I tried using a split form, but they don't seem to hold their properties.
Earlier today, another one of my students posted this in the forums - Deborah. She posted that she tried using a split form. Now I don't like split forms. Split forms are kind of like working with tables or queries, and if you change the properties in here, they don't always stick. The row height, the column widths - I don't like using split forms.
What I like using are continuous forms, because in the continuous form, you set all the properties. You can set the colors, the heights, the widths, whatever, and you can change them with a little bit of code. That's what I'm going to show you today.
Now, this is going to be a developer-level video. So if you've never done any programming before in Microsoft Access, go watch this first. It's my intro to VBA class. It's about 20 minutes long. It'll teach you everything you need to know. Then come on back.
All right, so here I am in my TechHelp free template. This is a free database. You can download a copy off my website if you want to. Let's say we have our customer list. I'm going to simplify this real quick just for the purposes of class. I am going to delete these. Yeah, actually, let's delete all these. I'm going to add notes in here so we have a long text field too. I'm just going to copy last name, slide it over here. We're going to change this guy to notes. So, here - notes - and change the name to notes.
Okay, so that's a long text field. You can see some of the data, but you can't see all the stuff. Some users want small, short rows so you can see more records. Some people want these to be taller so they can see more information in these long text fields. We want to give the user the ability to do that.
Now, the easiest way to do that is to give two little buttons. Little buttons make the row taller or make the row shorter. That's all. That's really easy to do.
Before we do that, we have to figure out how tall this row is right now. I'm going to grab another button, put it right here, and cancel the wizard. I'm just going to put in here "how tall."
We're going to see how tall this is, and we'll leave these in here. Let's make these buttons the same size. This will be the how tall button - HowTall_BTN.
Now, I click build event. What I'm looking for is the height property for any of these text boxes. So, I'm going to message box. Let's just look at first name: Firstname.Height. That is the height property of the first name box.
Save it. Close it. Open it. Let's click our how tall button. Boom. I get 315. What is 315? Well, 315 is a number of twips in size that that object is. All the objects in here are measured in twips. It's a screen-independent size. It's one twentieth of a point. I've got a whole separate video on the technical details and how to convert from twips to pixels and all that stuff. All you have to know is that height is 315. 315, what? I don't know - call them oranges, whatever. All right. 315 units tall.
So, now that I know that, I can say, well, if I make the little button to go bigger, how much bigger should we go? Maybe 20 more twips. Maybe 30. You can use however big you want. Or you could just make two buttons, one to go big, one to go small. But I'm going to make little buttons to go bigger and smaller.
So, Design View. Let's just copy this button, copy and paste. Let's call this one big - make it bigger. BTN_Tall. Let me just adjust the position. Right click, build event.
Now, what are we going to do? We're going to make all of the boxes in the detail section get taller. How are we going to do that? Well, start with CustomerID. CustomerID.Height = CustomerID.Height + [how much bigger do you want to make it]. Let's go with 20. 20 is a good, nice round number. Copy that and paste it. Of course, we've got four of them. Then just put each field in here - right, we've got first name, last name, and notes.
What you could actually do is change this and say: They are all going to be the same size. CustomerID.Height is going to get 20 bigger, and then make these guys all equal to its height. That's the easiest way to do it.
Save it. Close it. Open it and click your button. Ready? Tall. Oh, look at that. It's getting 20 twips bigger every time I click on it. I think you can see more information over here. That's kind of cool.
Let's go the other way. Back to Design View. Copy this button here. Let's make this short. Let's make this get shorter. Button short - BTN_Short. Right click. Build event.
Basically, the same thing. Copy all this. Paste it up here. We're going to go minus 20. We're going to put a safeguard in this one though. We don't want to get too small. We're going to say if CustomerID.Height is less than - we started off at 315 - let's say we don't want to get any smaller than 300. If it's less than 300, then Exit Sub. If you go below too small, if you get below one, you'll pop an error up.
Save that. Or you can just put 315 in here and not let them get any smaller than the original.
Ready? Bigger? Smaller? Oh, look at that. Oh, wait, what's happening though? I can get bigger, but when I get smaller, the detail section isn't shrinking.
Remember, there is a Can Grow, Can Shrink for all of these objects in forms. But they don't work when you're looking at the form on the screen. They're only for when you print forms - reports. So, Can Grow, Can Shrink in a form is pretty much useless. But we can manually make that height smaller or shorter.
How do we do that? Back to Design View again. Go back to our code.
Right now we're making it smaller. What we're going to do is make the detail section the same height as those controls. How do we do that? Well, it's going to be Me.Section(0).Height = CustomerID.Height. Me.Section(0) is another name for the detail section. There is an Access constant for it too - you can put in here. What is it - acDetail, I think it is. But I just always remember it's 0. If you look on Microsoft's website, there's detail header, footer, page header, page footer, that kind of stuff. But I did zero - it works fine. I'll put a link to that page down below for you.
Let's see how it works. Save it. Close it. Open it. Ready? Get bigger. Get smaller. Now, if you want to save this per user, you could set up a user table. I've got other videos on how to do a user log on. Go watch this video - it will teach you how to know what users logged on to the database. Once you have that, you can either store their preferences in a local table or set up a system defaults table and also put user preferences in there. That's up to you.
If you want to get fancy, you could throw a little font change in here too. Let's say you want to leave this guy so that it stays with the small fonts so you can see more, but maybe these boxes here you want to make the font get bigger and smaller. Let's try it. Go back in here to Design View. This takes a little finessing.
If your default font is 11, then maybe every time you go up, make the font a little bit bigger. Let's just test it with first name. So, Firstname.FontSize = [current size] + 1. Then up here, you don't want to go too small again, so say if Firstname.FontSize is less than 11, then Exit Sub. Don't go any further. But we can then do the single minus one. I haven't tried this yet - I'm doing this off the top of my head.
Let's see if we get it a little bigger. Okay, look at that. See? A little smaller, a little bigger, a little smaller. Not bad. Just do the same thing with both of those fields. So, you've got first name and last name getting bigger, while this one stays the same. Pretty cool, pretty nifty.
There are lots of things you can do with Access. That's why I love Access so much. It's so versatile. Once you learn a little bit of programming, there are all kinds of things you can do.
Now, before everybody asks in the comments: No, you cannot have multiple different heights for different rows. You can't say, "make this one taller, this one shorter." You can't do it. It's one of the limitations of Access. I have no control over that. I've tried numerous different techniques to change that. It can't be done. That's just one of the very few things that Access can't do, and that's one of them.
So, note to the Access team: Maybe, I don't know. It shouldn't be too hard for you to do.
One thing you can do, if you're interested in just reading this information, is you don't necessarily want to edit it, but you want to be able to read it. I had another student ask me - and this is coming up in tomorrow's video - if you could take something like the contacts form here, and have it so you can read all of these without having to open up each one to look at it. There's a lot in this box here, and you want to be able to just see these. Yes, you can do that with an embedded report inside a form. That will be the topic of tomorrow's video.
So, tune in tomorrow, same bat time, same bat channel.
There's today's TechHelp video. I hope you learned something, folks. Hope you had some fun doing it. Live long and prosper, my friends. I'll see you tomorrow.Quiz Q1. What is the main advantage of using a continuous form over a split form in Microsoft Access, according to the video?
A. Continuous forms display more records than split forms
B. Continuous forms allow easier property customization such as row height and color
C. Split forms require less code to modify row heights
D. Split forms allow multiple users to edit data simultaneously
Q2. In the context of Access forms, what measurement unit is used for control heights?
A. Pixels
B. Inches
C. Centimeters
D. Twips
Q3. What property of a text box determines its vertical size on a continuous form?
A. Width
B. Height
C. Top
D. RowSource
Q4. Which method does the video recommend for letting users change row height in a continuous form?
A. Allowing users to drag row boundaries with the mouse
B. Using split forms with adjustable properties
C. Adding buttons that programmatically increase or decrease the control heights
D. Changing the form layout in design view
Q5. To avoid making the rows too short, what is implemented when decreasing the control heights?
A. A minimum width property is set
B. A safeguard that prevents the height from going below a certain value
C. Users receive a warning message after each click
D. Control positions are locked
Q6. The 'Can Grow' and 'Can Shrink' properties of form controls work when:
A. Viewing the form on screen
B. Editing form design
C. Printing forms or generating reports
D. Switching between form and datasheet view
Q7. What value is commonly used to reference the 'Detail' section of a form in VBA code?
A. Me.Section(2)
B. Me.Detail
C. Me.Section(0)
D. Me.Section(acFooter)
Q8. Can different rows in a continuous form have different heights using the method in the video?
A. Yes, by setting the height property individually for each record
B. Yes, but only using split forms
C. No, continuous forms do not allow variable row heights per record
D. Yes, using conditional formatting
Q9. How can user preferences for row height be stored for future sessions, as suggested in the video?
A. They can only be stored temporarily in form properties
B. By editing the registry
C. By storing preferences in a user table or a system defaults table
D. By setting defaults in an external configuration file
Q10. What is one optional enhancement the instructor demonstrates besides changing row height?
A. Changing the layout orientation
B. Adjusting the font size of certain fields with the buttons
C. Adding new fields dynamically
D. Sorting records alphabetically
Answers: 1-B; 2-D; 3-B; 4-C; 5-B; 6-C; 7-C; 8-C; 9-C; 10-B
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 TechHelp tutorial from Access Learning Zone focuses on allowing users to adjust the row height in a Microsoft Access Continuous Form.
Many users have different preferences for how much information they want to see when browsing through records in a form. Some want compact rows so they can view as many records as possible at once, while others prefer taller rows for easier reading of long text fields. One question I received was whether there is a way to let users control how tall the rows are in a continuous form. Earlier, another student mentioned using split forms, but I have to say, I am not a fan of split forms. They behave more like datasheets and often fail to retain property changes, such as row height or column widths, reliably from session to session.
Continuous forms, on the other hand, are much more flexible. You can set properties like color, control heights, widths, and more. Even better, you can adjust these properties with a little bit of VBA code. That is what I will demonstrate today. A quick note: if you are new to programming in Access, I recommend you take a look at my introductory VBA course first. It is about 20 minutes long and will cover the basics you need before proceeding here.
For this demonstration, I am working in my TechHelp free template database, which you are welcome to download from my website. Let me walk you through the process using a simple customer list. To make the example clear, I have cleaned up the form and added a long text field named "Notes." This makes it a good case for showing the effect of changing row heights, as not all content is visible at a glance with short rows.
Our goal is to give users simple controls to adjust the row height up or down. The idea is to add two buttons: one that makes rows taller and one that makes them shorter.
Before we get started with the buttons, it helps to know the current height of a row. I added a temporary button to the form to show how tall one of the text boxes, such as "FirstName," is. When triggered, this button reveals the object's height measured in twips, which is the unit Access uses for on-screen measurements (1/20th of a point). For example, you might find that a control is 315 twips tall.
Once you have this number as a reference, you can quickly set up the row adjustment buttons. For example, decide on an increment, like 20 or 30 twips. The "Taller" button will increase the height of the controls in the detail section by this amount, making the rows taller and showing more of any long text fields. Conversely, a "Shorter" button will decrease the height, letting the user see more rows at once if they prefer.
After adding these buttons and writing the appropriate VBA code, you have to make sure all the controls for the row (like customer ID, first name, last name, and notes) adjust together, so they always remain properly aligned. The simplest way is to set the height of each control to match a reference control after adjustment. Then, you also need to set the height of the detail section of the form to match, ensuring the overall row size changes accordingly.
Be aware that there is a minimum height to maintain functionality and prevent errors, so you should include a safeguard in your code to stop users from shrinking rows below a certain point. I recommend not going below the original size you started with.
A common question that comes up is about the "Can Grow" and "Can Shrink" properties in Access forms. While those settings exist, they only work when printing forms or with reports. For on-screen viewing and interaction, you must manually manage control and section heights.
If you would like to get a bit more sophisticated, you could also let users change font sizes along with row size adjustments. For example, you might have the font size increase or decrease by one point each time the row height changes, but always ensure you do not allow the font to get too small. This gives users an additional way to customize their view for readability.
After making these changes, users can easily make records more compact or expand them as needed. Just remember that this adjustment applies to all rows in the form. Access does not currently support having a different height for each individual row. That is a limitation of the software, and despite various attempts, it simply cannot be done at this time.
For those primarily interested in displaying all the information in a read-only way, another approach is to embed a report inside a form. This method lets you read longer content from a list without having to open each record individually. I will cover that technique in a future video.
You can find a complete video tutorial covering step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Allowing users to change row height in Access Continuous Forms
Adding buttons to increase or decrease row height
Determining control height property in twips
Using VBA to adjust control heights dynamically
Synchronizing detail section height with controls
Implementing minimum row height safeguards
Adjusting font size along with row height
Synchronizing font size changes across multiple fields |
