Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Synchronized Floating popup Form

Synchronized Floating Popup Form.

This Form is designed specifically for inquiry purposes (not for Data Entry). The Source Data Table has several fields and the information in them can be categorized into different groups for viewing.

For example, the Employees Form, in the Northwind.mdb sample database, has been divided into two parts, viz. Company Information and Personal Information. The Company Info part is designed on the First Page of a Tab Control and the second part Personal Info category fields are placed on the second page of the Tab Control and stay hidden till it receives a click on the second page of the Tab Control, to bring the data into view. Let us assume that the Company Info has been the most frequently viewed or updated information and it is kept in full view, and Personal Info is kept behind because it is not so often viewed or edited.

We will design them differently with an exciting trick involving two separate stand-alone Forms without linking them as Main Form and Sub-Form. Company Info on one Form and Personal Info on a separate Form but both will have source data from the Employees table.

Let us name them as EmployeeMain and EmployeeSub Forms. They will remain as two independent Forms. A sample image of both Forms in running mode is given below:


The Trick Designs Plan

The trick is that when we open the EmployeeMain Form it will show only the Company Info alone (no trick here). We will move a few records forward using the Record Navigation Button and at this point, we would like to see the Personal Info part of the current record. We will click on a Command Button to open the EmployeeSub Form with the Personal Info of the current record on the EmployeeMain Form. From this point onwards the records on both Forms move forward/back synchronized, when you move to the Next or Previous records on the EmployeeMain Form, even though they are two separate Forms.

The EmployeeSub Form is defined as a Pop-up Form (or its Pop-up Property value is set to Yes) to float it above the EmployeeMain Form or any other Form open in the Application Window, provided you don't open another Pop-up Form. If you don't want the EmployeeSub Form to stay with you may close it and open it again when needed using the Personal Info Command Button on the EmployeeMain Form. When you close the EmployeeMain Form this action will close the EmployeeSub Form too if it is running.

  1. We can very easily design these two Forms by importing the Employees Table and Employees Form from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb Database. If you have not used these Objects so far for any other examples given on this site, you may import them into your database now.

  2. Open the Employees Form in Design View.

  3. Click on the Form and drag the mouse over all Controls on the Company Info Tab except the Photo (if you want it you may select that too) and the Command Button controls.

  4. Select Copy from Edit Menu.

  5. Click on the Employees Table, select Form from the Insert menu, and select Design-View, from the list of options displayed, to create a new Form.

  6. Click on the Detail Section of the Form and select Paste from Edit Menu to place the copied fields of the Employees Form onto the new Form.

  7. If the Form Header/Footer Sections of the Form are not visible then select Form Header/Footer from View Menu.

  8. Copy and Paste the Text Control with the employee name expression from the Header Section of the Employees Form in the Header Section of your Form. Change the Font Color to Red or some other color you like.

  9. Create a Command Button in the Detail Section below the data fields.

  10. Display its Property Sheet (View- -> Properties).

  11. Change the Name Property Value to cmdPersonalInfo and the Caption Property Value to Personal Info.

    NB: You must be careful with the names of Forms and Controls I suggest here because they are used in the Programs. If you give them differently the trick may not work as expected after completion.

  12. Create a Command Button at the Footer Section of the Form.

  13. Change the Name Property value to cmdClose and the Caption Property Value to Close.

  14. Display the Form's Property Sheet. Click on the left top corner of the Form where a black rectangle is shown, at the intersection of the horizontal and vertical design guide (scales) meet, to select the Form's Property Sheet, if it is not the current one.

  15. Change the Caption Property Value to Company Info.

  16. Display the VBA Code Module of the Form (Alt+F11). Copy and Paste the following code into the Code Module.

    The Form Module Code

    Option Compare Database
    Option Explicit
    Dim strSQL As String
    
    Private Sub cmdClose_Click()
       DoCmd.Close
    End Sub
    
    Private Sub cmdPersonalInfo_Click()
    If IsLoaded("EmployeesSub") Then
       strSQL = "SELECT Employees.* FROM Employees "
       strSQL = strSQL & "WHERE ([EmployeeID] = " & Me![EmployeeID] & ");"
       Forms("EmployeeSub").RecordSource = strSQL
       DoCmd.SelectObject acForm, "EmployeeSub", False
    Else
       DoCmd.OpenForm "EmployeeSub", acNormal, , "[Employeeid] = " & Me![EmployeeID], acFormReadOnly, acWindowNormal
    End If
    
    Forms("EmployeeMain").ActiveControl.SetFocus
    
    End Sub
    
    Private Sub Form_Close()
        DoCmd.Close acForm, "EmployeeSub"
    End Sub
    
    Private Sub Form_Current()
    If IsLoaded("EmployeeSub") Then
       strSQL = "SELECT Employees.* FROM Employees "
       strSQL = strSQL & "WHERE ([EmployeeID] = " & Me![EmployeeID] & ");"
       Forms("EmployeeSub").RecordSource = strSQL
       DoCmd.SelectObject acForm, "EmployeeSub", False
       Forms("EmployeeMain").SetFocus
    End If
    
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    DoCmd.Restore
    End Sub
    
  17. Save the Form with the name EmployeeMain.

  18. Repeat the process from Step 3 to Step 8 for transferring information from the Personal Info Tab of the Employees Form to a new Form.

  19. Display the Form Header/Footer Section of the Form (View - ->Form Header/Footer).

  20. Create a Command Button and change the Name Property Value to cmdClose and the Caption Property Value to Close.

  21. Display the Form's Property Sheet.

  22. Change the following Property Values as given below:

    • Caption = Personal Info
    • Default View = Single Form
    • Allow Additions = No
    • Allow Deletions = No
    • Data Entry = No
    • Record Selectors = No
    • Navigation Buttons = No
    • Dividing Lines = No
    • Auto Resize = Yes
    • Pop Up = Yes
    • Border Style = Dialog
    • Allow Design Changes = Design View Only.
  23. Display the Code Module of the Form (Alt+F11).

  24. Copy and paste the following code into the Code Module.

    Private Sub cmdClose_Click()
            DoCmd.Close acForm, Me.Name
    End Sub
    
  25. Save the Form with the name EmployeeSub.

    We need a small program to check whether the EmployeeSub Form is in an Open state or not before attempting to refresh its source data and bring it into a visible state.

    The Standard Module Code

  26. Copy and Paste the following Code into a Global Module (Standard Module) and save the Module:

Public Function IsLoaded(ByVal strFormName As String) As Boolean
Dim j As Integer

On Error GoTo IsLoaded_Err

IsLoaded = False
For j = 0 To Forms.Count - 1
    If Forms(j).Name = strFormName Then
       IsLoaded = True
       Exit For
    End If
Next

IsLoaded_Exit:
Exit Function

IsLoaded_Err:
IsLoaded = False
Resume IsLoaded_Exit
End Function

The Demo Run

  1. To try out your creation, open the EmployeeMain Form, and click on the Record Navigation Control to advance a few records forward.

  2. Click on the Personal Info Command Button. The EmployeeSub Form will open up showing the Personal Information that pertains to the same employee on the main Form. Check the Names of the Employees appearing on top of both forms.

  3. Now, try advancing records on the main form forward or back to the record navigation control. You will see that the corresponding personal information on the EmployeeSub Form is also moving along with the records on the main form.

  4. If you close the EmployeeMain Form while the EmployeeSub Form is open both will be closed.

Download Demo Database.

You may download the sample database from the Download Link given below and give it a try before you design one of your own to understand the trick:


Share:

1 comment:

  1. Great information! I’ve been looking for something like this for a while now. Thanks!

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


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 ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button 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