Synchronized Floating popup Form ~ LEARN MS-ACCESS TIPS AND TRICKS

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 is divided into two sections: Company Information and Personal Information. The Company Information fields are placed on the first page of a Tab Control, and the Personal Information fields are located on the second page, which remains hidden until the user clicks its tab to bring that data into view. Typically, since the Company Information section is accessed or updated more frequently, it is kept in full view, and the Personal Information section stays tucked away in the background, as it is not always viewed or edited.

We will design them differently, an interesting trick with two separate stand-alone Forms, without linking them together as the Main Form and the Sub-Form. Company Information on one Form and Personal Information on a separate Form, but both will have the source record from the Employees table. Why two Forms? Whenever the Personal Information of an employee or several Employees is to be viewed, then open the Form and keep both forms synchronized to view information of an employee(s) on both forms.

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:

Right-click to open Large Image in New Tab/Window.

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 or back, and both ways are synchronized when moving 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, then 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 if it is running.

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 forms for any other examples given on this site, you may import them into your database now.
Open the Employees Form in Design View.
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.
Select Copy from the Edit Menu.
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.
Click on the Detail Section of the Form and select Paste from the Edit Menu to place the copied fields of the Employees Form onto the new Form.
If the Form Header/Footer Sections of the Form are not visible, then select Form Header/Footer from the View Menu.
Copy and paste the Text Control with the employee name expression from the Header Section of the Employees Form into the Header Section of your Form. Change the Font Color to Red or some other color you like.
Create a Command Button in the Detail Section below the data fields.
Display its Property Sheet (View -> Properties).
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 a different the trick may not work as expected after completion.
Create a Command Button at the Footer Section of the Form.
Change the Name Property value to cmdClose and the Caption Property Value to Close.
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), to select the Form's Property Sheet, if it is not the current one.
Change the Caption Property Value to Company Info.

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

Save the Form with the name EmployeeMain.
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.
Display the Form Header/Footer Section of the Form (View ->Form Header/Footer).
Create a Command Button and change the Name Property Value to cmdClose and the Caption Property Value to Close.
Display the Form's Property Sheet.
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.
Display the Code Module of the Form (Alt+F11).

Copy and paste the following code into the Code Module.

Private Sub cmdClose_Click()
        DoCmd.Close acForm, Me.Name
End Sub

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

To try out your creation, open the EmployeeMain Form and click on the Record Navigation Control to advance a few records forward.
Click on the Personal Info Command Button. The EmployeeSub Form will open, displaying the Personal Information that belongs to the same employee on the main Form. Check the Name of the Employee appearing on top of both forms.
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 also belongs to the same record on the main form.
If you close the EmployeeMain Form while the EmployeeSub Form is open, both will be closed.