Microsoft Access Form Move Size Action
We design Access Forms which 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 it’s Modal property value set to True) that must be closed, after taking suggested action on it, before you are able to 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 opened in tabbed style in the Application Window.
Popup Forms will open on the exact location of the Application Window from where you have saved it during design time. If you need more details on this subject visit this Article Link: Positioning popup Forms.
We can open a Microsoft Access Form and move it to a particular location of the Application Window in resized state, if necessary, with MoveSize Action of Docmd Object.
Here, we will learn the usage of MoveSize Action of the DoCmd Object in VBA.
View the Youtube Demo Video given below for reference. Select 720p HD Option from the Settings for better quality viewing.
When Supplier Code is selected on the Supplier List Form, the related Product List is displayed above the Supplier Form to the right of the main form. The width of the Product List Form is not changed but the height is changed depending on the number of items on the form.
We need two tables, two Queries and the Supplier List Form from the Northwind.accdb sample database to build this trick. You need to design a Form for the Product List. A Demo Database is given at the end of this Article to download and try it out, right away.
The list of Tables, Queries and Forms required, to build this database, are given below.
- Suppliers Extended
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]));
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 Overlapping Windows option in Access Option settings mentioned in paragraph two from top.
- Open Supplier List Form.
- Click on Supplier ID Field (with Company column heading) of any record, to open and display the selected Supplier related Product List, in Resized Product List Form and Moved to its specified location.