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

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.
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 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 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 View Menu.
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.
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 differently 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) meet, 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 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.
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.
If you close the EmployeeMain Form while the EmployeeSub Form is open both will be closed.