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

[TheSmileyCoder]

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:

Private Sub Form_Load()
   'Variables
      Dim lngCount As Long
      Dim lngWindowHeight As Long
      Dim lngOldWindowHeight As Long
      Dim lngDeltaTop As Long
      Dim lngMaxRecordsToShow As Long
 
      lngMaxRecordsToShow = 10
 
   'Find the amount of records in form
      Dim rs As DAO.Recordset
      Set rs = Me.RecordsetClone
      If Not rs.EOF Then rs.MoveLast
      lngCount = rs.RecordCount
 
   'Check whether there are more then Max amount of records
      If lngCount > lngMaxRecordsToShow Then
         lngCount = lngMaxRecordsToShow
         'Enable vertical scrollbar
            Me.ScrollBars = 2 'Vertical
      Else
         'Disable scrollbars
         Me.ScrollBars = 0 'None
      End If
 
   'Calculate new windowheight.
   'If you do not have a header/footer, or they are not visible adjust accordingly
      lngWindowHeight = Me.FormHeader.Height + _
                  Me.Detail.Height * lngCount + _
                  Me.FormFooter.Height + _
                  400
                  'The 567 is to account for title bar Height.
                  'If you use thin border, adjust accordingly
 
   'The form will be "shortened" and we need to adjust the top property as well to keep it properly centered
      lngOldWindowHeight = Me.WindowHeight
      lngDeltaTop = (lngOldWindowHeight - lngWindowHeight) / 2
 
   'Use the move function to move the form
 
      Me.Move Me.WindowLeft, Me.WindowTop + lngDeltaTop, , lngWindowHeight
 
   'Cleanup
      Set rs = Nothing
End Sub

I use:
AutoRezise= True
AutoCenter=True
with this code. Odd results may occur if you do not, but you can go test that yourself.

[twinnyfo]

Smiley,

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!

[TheSmileyCoder]

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.

[NeoPa]

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.

[TheSmileyCoder]

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 :)

[Hoopla3000]

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.

[TheSmileyCoder]

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.

[NeoPa]

Hoopla3000:
In setting the window height, I added to the lngWindowHeight calc:

Where's that "Like" button when you need it? Good idea :-)

[Hoopla3000]

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.

[Seth Schrock]

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.

[biskalero78]

Thank you very much for your solution, works like a dream. It is really annoying the height of those Continuous forms. Awesome !!

[TheSmileyCoder]

Glad you liked it. Thanks for taking the time to let me know :)

[Seth Schrock]

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?

[hkc94501]

@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.

[NeoPa]

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.

[Hoopla3000]

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!