473,389 Members | 1,132 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,389 developers and data experts.

How to dynamically resize a continuous Dialog form to fit / show all records

2,322 Expert Mod 2GB
I have recently had a issue where I open a continuous dialog form, showing anywhere from 1 to X records, X usually being in the 2-5 range. I found it very annoying that Access2010 per default made the continues form take up the entire height of the screen, even when there is only a few records.

So to fix that issue I came up with a bit of code to make the window size roughly match the amount of records, with the ability to specify a max amount of records to show. If the amount of records exceed the max, I also use the code to modify the scrollbar settings, so that only if there are more then Max records the scrollbar is shown.

The code is shown below, I placed it in the Load event of the form:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.    'Variables
  3.       Dim lngCount As Long
  4.       Dim lngWindowHeight As Long
  5.       Dim lngOldWindowHeight As Long
  6.       Dim lngDeltaTop As Long
  7.       Dim lngMaxRecordsToShow As Long
  9.       lngMaxRecordsToShow = 10
  11.    'Find the amount of records in form
  12.       Dim rs As DAO.Recordset
  13.       Set rs = Me.RecordsetClone
  14.       If Not rs.EOF Then rs.MoveLast
  15.       lngCount = rs.RecordCount
  17.    'Check whether there are more then Max amount of records
  18.       If lngCount > lngMaxRecordsToShow Then
  19.          lngCount = lngMaxRecordsToShow
  20.          'Enable vertical scrollbar
  21.             Me.ScrollBars = 2 'Vertical
  22.       Else
  23.          'Disable scrollbars
  24.          Me.ScrollBars = 0 'None
  25.       End If
  27.    'Calculate new windowheight.
  28.    'If you do not have a header/footer, or they are not visible adjust accordingly
  29.       lngWindowHeight = Me.FormHeader.Height + _
  30.                   Me.Detail.Height * lngCount + _
  31.                   Me.FormFooter.Height + _
  32.                   400
  33.                   'The 567 is to account for title bar Height.
  34.                   'If you use thin border, adjust accordingly
  36.    'The form will be "shortened" and we need to adjust the top property as well to keep it properly centered
  37.       lngOldWindowHeight = Me.WindowHeight
  38.       lngDeltaTop = (lngOldWindowHeight - lngWindowHeight) / 2
  40.    'Use the move function to move the form
  42.       Me.Move Me.WindowLeft, Me.WindowTop + lngDeltaTop, , lngWindowHeight
  44.    'Cleanup
  45.       Set rs = Nothing
  46. End Sub
I use:
AutoRezise= True
with this code. Odd results may occur if you do not, but you can go test that yourself.
Oct 5 '12 #1
15 51153
3,653 Expert Mod 2GB

Quite an interesting solution. Have you found any bugs or quirks with this code, as of yet? My experience with resizing forms has been trial and error, and sometimes the forms will just be the wrong size, even though I set the size. Very creative solution, though!
Oct 5 '12 #2
2,322 Expert Mod 2GB
Not as of yet, as I just wrote the code for the first time today. I looked around online, but couldn't find any similar solution, so figured I should share my approach.

Problems I imagine might occur, is if you leave the MaxNrOfRecords very high, and thus the total height of the window overflows some maxHeight on the window.
Oct 5 '12 #3
32,556 Expert Mod 16PB
I did something similar a while back and it took me ages to realise that to resize a form you had to move it. Not very intuitive, but I got there eventually. I was convinced for ages that resizing simply didn't work.
Oct 22 '12 #4
2,322 Expert Mod 2GB
I had to laugh a bit at that (not you) cause I have been there myself. Thankfully Google and Bytes were there to help me :)
Oct 22 '12 #5
7 Nibble
Cool idea Smiley. I've used this twice now with great success. In setting the window height, I added to the lngWindowHeight calc:

... + IIf(Me.NavigationButtons = True, 250, 0) + ...

in case the form has the navigation buttons enabled.
Apr 10 '13 #6
2,322 Expert Mod 2GB
Nice touch Hoopla, thank you very much for reporting it back. I shall try to have a look at it soon, and then update the original post.
Apr 14 '13 #7
32,556 Expert Mod 16PB
In setting the window height, I added to the lngWindowHeight calc:
Where's that "Like" button when you need it? Good idea :-)
Apr 14 '13 #8
7 Nibble
I just noticed today that this doesn't work in Access 2013 as well as it does in 2010. It may be that the window height is returned differently in 2013 with the flat metro styling. Does anyone know a quick fix?

The attached image shows how the sizing differs between two versions of Access but the same application.

I just realized and should note that the Access 2013 image was taken from Windows Server 2012, which may be an additional factor.

Attached Images
File Type: png Access_2010_vs_2013_sizing.png (27.9 KB, 7417 views)
Jun 5 '14 #9
Seth Schrock
2,965 Expert 2GB
This is a great help. I'm always fighting with the height of my continuous forms. Everytime I go into design view and back to form view, I have to fix the height of the window. Thanks Smiley.
Jun 7 '14 #10
Thank you very much for your solution, works like a dream. It is really annoying the height of those Continuous forms. Awesome !!
Jan 13 '16 #11
2,322 Expert Mod 2GB
Glad you liked it. Thanks for taking the time to let me know :)
Jan 13 '16 #12
Seth Schrock
2,965 Expert 2GB
I have just run across a bug with this. I have a form in which I set the record source in the form's OnLoad event based on selections from the previous form. After I do that, I then call your function, but I get error 7159 saying that I have made an invalid reference to the object's RecordsetClone property. I tried changing your code to instead use the Record Source property to create a recordset, but then I get an error saying that the OpenRecordset() function can't find the recordset ''. Any suggestions on how to get around this?
Apr 15 '16 #13
1 Bit
@Seth Schrock
It's been a long time since Seth posted this question so he's probably figured it out by now but I may have an answer.
I got this same error. It turned out that the recordset was empty or null. It had not been initialized so it could not be cloned.
Aug 7 '21 #14
32,556 Expert Mod 16PB
Hi DJ.

Please remember that it is never appropriate to add your own question into an existing thread. This is termed hijacking and is outlawed here (as well as pretty well everywhere of course). If you need help then post a thread in the normal way. You are allowed to reference existing threads.

This is even more true for article threads. Who wants to go through all the dross when they're trying to learn something specific. It's like barnacles on the hull of a ship.
Aug 5 '22 #15
7 Nibble
Thanks again for posting this code. I don't make a lot of pop-up forms and finding this code again was a big help!
VBA is still relevant in 2023!
Feb 15 '23 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Dalan | last post by:
I designed a dialog box with a combo to select individual vendors from a form and its related data for print ouput. Though the dialog box seems to work okay, I apparently do not have the filtering...
by: Noah Coad [C# MCP] | last post by:
I'm creating a program the must show the time (a clock) in a user control and the text must fill the user control. How do I make text dynamically resize to fill a label/panel/usercontrol? ...
by: andrew | last post by:
Hi there, I'm having a problem with a modeless form in my app. I have a main form in my app and a socket that waits on data from a server (I use BeginReceive/EndReceive for that) and when I...
by: Guadala Harry | last post by:
Here's the functionality I'm after: I need for a page to display a photo. When users click on other links within the page, the photo changes. I'd like to swap out the photo without doing a...
by: Peter | last post by:
I have a primary form named frmMain. During some extended data pulls I launch another Dialog Form named frmUpdating. How do I keep the frmUpdating centered on the frmMain if a user moves the...
by: Simon Abolnar | last post by:
I would like to know how to open child form from dialog form. Thanks for help! Simon
by: Teelions | last post by:
I know that this can be done for iframes but am wondering if it can be also done for layers. I have 2 pages: an asp content page that has a html (toc.html) include placed within it. The...
by: JDeats | last post by:
I have a simple WinForm with a WinForms splitter down the middle. I would like to make it so when the user clicks on a button inside the left portion of the screen (the panel to the splitters left)...
by: ashishyadav2007 | last post by:
In Setup project for .net I have added a dialog(form) between Start and End dialog(form), but i need to show that dialog(form) only if a registry search condition is successful. If registry search...
by: rythmic | last post by:
Hi! I have developed a system where I use Dojos dijit dialog to show certain information. It works perfectly for all major browsers except for firefox ( where I've tested v3.6 and 4 BETA) The...
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.