Microsoft Access Form Move Size Action.
We design Access Forms that fits into the existing Application Window Width (to display/edit records), or design popup Forms with specific size without borders or scroll bars (can be moved out of the Application Window area too) or Modal type form (popup type forms with its Modal property value set to True) that must be closed, after taking suggested action on it, before you can work with other forms.
This type of form opens one over the other (when more than one form is open) on the Application Window. You must enable Overlapping Windows by selecting Office Button --> Access Options --> Current Database --> Document Window Options --> Overlapping Windows to open the forms in this way; otherwise, they will be in the Tabbed style in the Application Window.
The Pop-Up Forms.
The pop-up forms will open at the exact location of the Application Window where you have saved it during design time. If you need more details on this topic, visit the Article Link: Positioning pop-up Forms.
We can open a Microsoft Access Form and move it to the Application Window's location in the resized state; if necessary, use the MoveSize Action on the DoCmd Object.
The MoveSize Action.
Here, we will learn the MoveSize Action of the DoCmd Object in VBA.
View the YouTube Demo Video given below for reference. Select the Settings 720p HD option for better quality viewing.
Demo Video
When the Supplier Code is selected on the Supplier List Form, the corresponding Product List is displayed above and to the right of the main Supplier Form. The width of the Product List Form remains unchanged, while its height adjusts dynamically based on the number of items displayed.
We need two tables, two Queries, and the Supplier List Form from the Northwind sample database to build this trick. You need to design a Form for the Product List. A Demo Database Link is given at the end of this Article to download and try out right away.
The list of Tables, Queries, and Forms required to build this database is given below.
- Suppliers
- Products
- Suppliers Extended
- ProductListQ
Tables:
Queries:
SQL Code:
SELECT Products.[Supplier IDs], Right([Product Name],Len([product name])-17) AS Product, Products.[List Price], Products.[Quantity Per Unit] FROM Products WHERE (((Products.[Supplier IDs].Value)=[forms]![Supplier List]![id]));
Forms:
Copy and Paste the following VBA Code into the Supplier List Form's VBA Module and save the Form:
Private Sub Company_Click()
Dim frm As Form, ProductForm As String, items As Integer
Dim mainFormHeight As Integer
Dim intHeader As Integer, intFooter As Integer
Dim intH As Integer, frmchild As Form, oneInchTwips As Integer
On Error GoTo Company_Click_Err
ProductForm = "Product List"
oneInchTwips = 1440 'Form's internal value conversion factor
mainFormHeight = Me.WindowHeight
For Each frm In Forms
If frm.Name = ProductForm Then
DoCmd.Close acForm, ProductForm
Exit For
End If
Next
DoCmd.OpenForm ProductForm
Forms(ProductForm).Refresh
items = DCount("*", "ProductListQ")
Set frmchild = Forms(ProductForm)
'Calc the required height of the chid-form
'based on number of items for selected supplier
intHeader = frmchild.Section(acHeader).Height
intFooter = frmchild.Section(acFooter).Height
'0.272 inch - product item row height
intH = intHeader + items * 0.272 * oneInchTwips + intFooter
intH = intH + oneInchTwips '- one inch margin from bottom
'Move and resize the height of the child form
'4.275 inches to the right from left of the Application Window
'1.25 inches - arbitrary value taken for bottom margin
DoCmd.MoveSize 4.275 * oneInchTwips, mainFormHeight - intH, , (items * 0.272 + 1.25) * oneInchTwips
Company_Click_Exit:
Exit Sub
Company_Click_Err:
MsgBox Err & ": " & Err.Description, , "Company_Click()"
Resume Company_Click_Exit
End Sub
Private Sub Form_Current()
Me.Refresh
End Sub
Note: Don't forget to change the Overlapping Windows option in the Access Options settings mentioned in paragraph two from the top.
- Open Supplier List Form.
- Click the Supplier ID Field (with the Company column heading) of any record to open the Supplier Products List display in the Resized Product List Form, and move it to its specified location.
Download the Demo Database.
Download Demo MoveSize Demo.zip
Download NWmdb.zip
