Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Synchronized Floating popup Form

Introduction

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 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 of fields are placed on second page of the Tab Control and stays 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 is the most frequently viewed or updated information and it is kept in full view and Personal Info kept behind because it is not so often viewed or updated.

We will design them differently with an interesting 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 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 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 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 if you don't open another Pop Up Form. If you don't want the EmployeeSub Form to stay on you may close it and open 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 and select Form from 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 Employees Form on to 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 to 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 Open state or not before attempting to refresh its source data and bring it in 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, click on the Record Navigation Control to advance few records forward.
  2. Click on the Personal Info Command Button. The EmployeeSub Form will open up showing Personal Information pertains to the same employee on the main Form. Check the Names of the Employee appearing on top of both forms.
  3. Now, try advancing records on the main from forward or back with 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:



Download Demo SynchronizedForm2k.zip


Share:

1 comment:

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

    ReplyDelete

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...

Labels

Blog Archive

Recent Posts