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



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.



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

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.

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 - File: SynchronizedForms2K.zip (Size:54.6K)

---

Rounding Function MRound() of Excel
Custom Calculator and Eval() Function
Data Editing and Security Issues
Event Trapping & Summary on Datasheet
Sum() Min() Max() ParamArray