Microsoft Access - Auto Resizing SubForms

We all develop forms based on our current setup and that mean that when our forms are deployed to production, they often run on systems with different settings, different screen size and resolutions thus causing us and our users headaches at times and providing a sub-optimal user experience.
In this video, I demonstrate 2 different techniques to automatically resize subforms to maximize their footprint within the available space. This is great to minimize wasted real estate and maximize the data displayed. These techniques also adjust sizing when the main application or parent form is resized.
This helps give a more polished look and feel to your works, standardizes placement of controls (scrollbars, ...), makes it responsive to resizing and optimizes the data displayed to your user. Everyone wins.
Article(s) on this Subject:
• www.devhut.net/access-autosiz...
Other Links of Interest:
• www.devhut.net/access-form-re...
• www.gpcdata.com/AccessTestBed
My Links:
• www.cardaconsultants.com/ (business)
• www.devhut.net/ (blog)

[@JWinAus]

Thanks Daniel, as usual it is well presented and a very useful idea. I much appreciate your work!

[@DanielPineault]

My pleasure!

[@alializadeh8195]

Thanks

[@DanielPineault]

You're most welcome. Glad you find my content informative.

[@neojumpy]

Do u have checked ur code, if u make ur window very small, i guest an error will popup, no?

[@DanielPineault]

Thank you for the feedback. I've updated the sample demo database to adjust for such cases.

[@neojumpy]

OT: i recommend, if possible, never to point to a form from the outside, because the entire name path must be entered. Particularly if you set the recordsource via vba.
Private Sub cbo_Departments_AfterUpdate()
AdjustRecordSource 'here no requery on the whole form, only on the subform what is needed
End Sub
Sub AdjustRecordSource()
Me.Employees.Form.RecordSource = "SELECT employees.* " & vbCrLf & _
"FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no " & vbCrLf & _
"WHERE dept_emp.dept_no = '" & Me.cbo_Departments & "'" 'Here u work with the object ME. always work even when u change the name of the form
' Me.Employees.Form.RecordSource = "SELECT employees.* " & vbCrLf & _
"FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no " & vbCrLf & _
"WHERE (dept_emp.dept_no = Forms![Employees by Department_VBA_Both]![cbo_Departments]);"
End Sub

[@DanielPineault]

I normally don't.
But while perhaps not ideal, there is still nothing wrong with the code used.