Access Paired Forms

The search for fresh user interface and menu systems continues. Readers of my article Something Not Entirely Unlike Access have asked for more suggestions. Frankly, I didn’t think I had anything more, but a recent new application request prompted me to try a new approach, and what follows is the result. You can download the code here and try it out yourself.


The basic premise is ‘Paired Forms’ as shown in the screen shot below. The trick is how to resize them so they fill the client area and snug up to each other. There’s not a lot of code involved, but there is one trick that’s indispensable. Once you get the basics down, you can use this idea to create your own custom UI experience.



It’s All In The Open Event


Nearly all the code-of-substance is executed in the Form_Open() event. Sure, there are a few other snippets, but nothing that hasn’t been explained in previous articles and/or can be deduced by reviewing the code. Basically, this is what we want to accomplish:



1. Open the Dashboard form on startup


2. Position Dashboard form to the extreme left


3. Size form to 2.5″ wide by the full available height


4. Add an action to load detail form


5. Resize detail form to fill remaining space


6. Allow for toggling of different forms in that space



We’re using a subset of the Pubs database tables, as in previous demos. The app allows users to select a [Publisher] from the drop down list. Once selected, the [Titles] form is loaded in the detail section. This form contains a pair of toggle buttons, which allows the user to switch to the [Employees] form and back again. The screen shot above gives you an idea of what we want to accomplish.


First, let’s consider what happens when the Dashboard form (frmMain) is opened. To avoid unsightly flashing, we set the Echo property to False. That assures that the screen will not be updated graphically with the unloading, loading and resizing of forms. You must remember to unhide the screen in the Exit code by setting Echo to True, or else you’ll have trouble finding your forms.


Now for the trick. I want to find the total available client area, and set the form’s height to that value. While there might be another way to do this, my search on the newsgroups found nothing as simple as what I’m about to suggest. With the Echo Off, you’re free to MAXIMIZE the form, take it’s height measurement, RESTORE it back down and set the height to the value previously determined. Voila! All done!


The DoCmd.MoveSize command is used to place the restored form. The first two parameters are set to zero, which places the form in the upper left corner. The 3600 value for width (2.5″ * 1440 pixels) gives you a nice menu-sized left dashboard panel. While you could load this form up with buttons to launch other forms, I’ve elected to present a single drop-down box where the user may select the publisher whose records we want to view.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler

Dim lngHeight As Long
‘ Hide screen to reduce flashing
DoCmd.Echo False

DoCmd.Maximize
lngHeight = Me.InsideHeight
DoCmd.Restore
DoCmd.MoveSize 0, 0, 3600, lngHeight

SetPubID 0

Exit_Here:
‘ Unhid screen
DoCmd.Echo True
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation
Resume Next
End Sub


Notice that when the form opens, I call a public function to reset the PubID to zero. When the user selects a new publisher from the combo box, this value is set to the current PubID and frmTitles is opened. This form is coded to filter the recordset for this value by calling the GetPubID() public function. This transfer of filter criteria can be handled in other ways, but this is the method I prefer. It’s simple, clean, and the value is readily available to any query, form or function I want.


When the frmTitle opens, it too, needs to resize and relocate itself, but now we have a reference point with frmMain. Again, we turn off the screen Echo so we can work our magic in ‘the dark’. (You can also set Me.Painting to True or False to accomplish the same thing as DoCmd.Echo.) Again the MAXIMIZE trick is used to get the height, but the width now has to be derived from the difference between the total width (Me.InsideWidth) and the width already used by frmMain. This is just simple math at this point.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler

Dim lngLeft As Long
Dim lngHeight As Long
Dim lngWidth As Long
Dim lngDetail As Long
Dim lngShim As Long

DoCmd.Echo False
DoCmd.Maximize

lngLeft = Forms!frmMain.Width
lngHeight = Me.InsideHeight
lngWidth = Me.InsideWidth – lngLeft
lngDetail = Me.Section(acDetail).Height
lngShim = 350

Me!lineTop.Width = lngWidth
Me.lineBottom.Width = lngWidth
Me!lblFooterMessage.Width = lngWidth

Me!objTitles.Left = 0
Me!objTitles.Top = 0
Me!objTitles.Width = lngWidth – cGap
Me!objTitles.Height = (lngDetail – lngShim)

DoCmd.Restore
DoCmd.MoveSize lngLeft, 0, lngWidth, lngHeight

Exit_Here:
DoCmd.Echo True
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation
Resume Next
End Sub


One final bit of housekeeping is to resize the subform object to fill the detail section area. This is a little tricky because you need to grab the height of the Detail Section while the form is maximized, and deduct for the navigation bar. That’s what the lngShim value represents. You might be able to get away without this shim, but by having it there, you can play with values to get your subform object to resize the way you’d like.


Toggle Between Forms


The last trick occurs on the toggle buttons that flips the detail area from Titles to Employees. The code, shown below, is even simpler than that above. If a valid frame option is selected, then open the new form, set some property on it (as desired) and close the current form. Remember that the resize code is on the Form_Open() event so as long as you replicate the logic on each form, they will all resize and relocate themselves upon opening.

Private Sub fraSelectForm_AfterUpdate()
On Error GoTo Err_Handler

If Nz(Me!fraSelectForm, 1) = 2 Then
DoCmd.OpenForm “frmEmployees”
Forms!frmEmployees!txtPubName = Me!txtPubName
DoCmd.Close acForm, Me.Name
End If

Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation
Resume Next
End Sub


This example is simple, having only two options on the Toggle Button control, but it could become a mini-dashboard for a variety of other forms, reports or code-based actions.


Paint Your Own Pallet


So, there’s nothing earth-shattering here … just an idea. Sometimes, getting the idea, getting a starting point, is the hardest part. I believe it might help stimulate the creative juices to see my Paired Forms example, but you’re definitely going to have to apply your own style to it. The code above may be simple, but the possibilities are endless.


» See All Articles by Columnist Danny J. Lesandrini

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles