Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Microsoft Access Form Move Size Action

Introduction.

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

Pop-Up Forms.

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 is in the resized state, if necessary, with MoveSize Action of Docmd Object.

The MoveSize Action.

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.

Demo Video

When the 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.

    Tables:

  • Suppliers
  • Products
  • Queries:

  • Suppliers Extended
  • ProductListQ
  • 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:

  • Supplier List
  • Product List

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 Access Option settings mentioned in paragraph two from the top.

  1. Open Supplier List Form.
  2. 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.

Download the Demo Database.


Download Demo MoveSize Demo.zip

Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

Translate

PageRank

MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web

Newsletter


Feed

Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Event List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form ImageList Control Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ListView Control Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code