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.

Click for larger image

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers