Access Paired Forms
February 13, 2008
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
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.