Using multiple instances of a sub-form in a single form

I recently accepted a project to maintain and upgrade a database that has been in use for about a decade.  One of the first indicators that I was in for a lot of work was that there were three separate forms that contained multiple, identical sub-forms, each of which had its own source object.  In all, there were 60 total subforms related to these three main forms 57 more form objects in the database than were needed, along with 57 unnecessary queries.

One of these forms was a calendar, similar to the one displayed above, which contained 42 separate subforms.

Forget about the bloating this causes in your database (my sample grew from 650KB to 1.8MB when I made all of those copies), what concerns me is the tedious task of:

a. creating and maintaining 41 copies of the original subform

b. modify and saving 41 separate queries

c.  maintaining the code behind those forms

Instead of participating in this nightmare scenario, I strongly encourage you to reuse the same sub-form as the source object in your sub-form controls and make judicious use of SQL and VBA code to display the appropriate data in each sub-form control.

The attached database is a very simplistic appointment application.  It contains abbreviated tables for Providers, Patients, and Appointments, with forms for displaying the calendar and adding/editing/deleting appointments.

There are several key concepts that you must grasp when using this technique:

1.  Naming convention:  You must have a naming convention that allows you to do all of the formattings, querying, and filtering of the sub-form controls in a loop.  In the sample database, each of the sub-forms has a name that looks like: sub_cal_W#_D#, where "W#" represents the week and "D#" represents the day of the week.  This allows me to iterate through all of the sub-form controls with a loop which looks like:

For intWeek = 1 to 6
    For intDay = 1 to 7
        set ctrl = me.controls("sub_cal_W" & format(intWeek, "0") & "_D" & format(intDay, "0")
        set frm = ctrl.form
        'do something here
    Next intDay
Next intWeek

2.  Common record source:  Use a query as the record source for the sub-forms which will provide the data for all of the sub-forms.  This prevents you from having to update the record source of each sub-form every time you change the calendar month.  In the sample database, I took the easy route and simply created a query that filters the Appointments table for the selected service provider and the year displayed in the calendar.   In a production application, I would refine this to only return records for the 42 days displayed on the calendar (more on this later).

SELECT A.Appt_ID, A.Appt_Date, A.Appt_Start, A.Appt_End, A.Appt_Notes
, A.Appt_Dr_ID, [Dr_Firstname] & (" "+[Dr_Lastname]) AS Doctor
, A.Appt_Pat_ID, [Pat_Firstname] & (" "+[Pat_Lastname]) AS Appt_Patient
FROM (tbl_Appointments AS A LEFT JOIN tbl_Doctors AS D ON A.Appt_Dr_ID = D.Dr_ID)
LEFT JOIN tbl_Patients AS P ON A.Appt_Pat_ID = P.Pat_ID
WHERE (Year([Appt_Date])=fntempvars("ApptYear"))
AND (A.Appt_Dr_ID=fntempvars("ApptDrID"))
ORDER BY A.Appt_Date, A.Appt_Start;

Note: I've been using tempvars in my queries ever since Access 2007 came out with this feature.  You can read more about why I do this in my article on Using Global Variables in MS Access.

3.  Control Processing:  You need a function that you can use which will loop through each of the controls (sub-forms) to format the controls in the sub-form and filter the sub-form to display only the records for that day.  You would call this function every time the month changes.  In the sample database, this function is located in the FormatSubs procedure of frm_Calendar, but you could just as easily put it in a standard code module.  I'll provide more detail on this later.

The Calendar Form:

The Calendar form is quite simple, displaying a combo box to select the provider that the appointment is with, a textbox containing the year/month of the appointment (defaults to the current year and month), a couple of buttons to change the month displayed, and a set of 42 subforms.

Why 42 subforms?

In any calendar, you have to account for the maximum of 31 days in a month.  You could probably get away with only having 31 subforms, and simply moving them around in the FormatSubs code.  However, people are used to seeing all of the days preceding the first day of the month (if it falls on other than Sunday) and all of the days following the last day of the month, if it doesn't end on a Saturday;  this scenario lends itself to 6 full weeks.

Creating the sub-forms:

The concept of copying, pasting, and positioning 41copies of the sub-form control (even with the layout view) was not something I wanted to tackle, so I used the loop concept mentioned above to create, position, and define the source object for each of the subforms.

Public Sub BuildSubformControls()

    Dim intWeek As Integer, intDay As Integer
    Dim sfHeight As Double, sfWidth As Double, sfGap
    Dim dblLeft As Double, dblTop As Double
   
    Dim ctrl As Control
   
    sfHeight = 1.5 * 1440
    sfWidth = 1.25 * 1440
    sfGap = 0.0625 * 1440
    For intWeek = 1 To 2
        For intDay = 1 To 3
       
            Set ctrl = CreateControl("frm_Test", acSubform, acDetail)
            ctrl.Name = "sub_cal_W" & Format(intWeek, "0") & "_D" & Format(intDay, "0")
            dblLeft = sfGap + (intDay - 1) * (sfWidth + sfGap)
            dblTop = sfGap + (intWeek - 1) * (sfHeight + sfGap)
            ctrl.Move dblLeft, dblTop, sfWidth, sfHeight
            ctrl.SourceObject = "Calendar_sub"

        Next intDay
    Next intWeek
           
End Sub

How does it work:

As I said, this is a very simple sample database.  

1.  When frm_Calendar is loaded it:

a.  Sizes the form precisely to the size I want it

b.  Sets the value of the Provider combo box to the most recent value used (stored in Tempvars!Provider_ID) and re-queries the subforms

c.  sets the calendar date and formats the sub-forms

2.  When the provider is changed, it loops through and re-queries each of the sub-forms (procedure RequerySubs)

3.  When the month changes, it reformats the subform controls (procedure FormatSubs)

The code for re-querying the subs is quite simple:

Public Sub RequerySubs()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.Name Like "sub_Cal*" Then
            ctrl.Form.Requery
        End If
    Next
End Sub

The code for formatting the subs is a bit more complex, so I won't copy it here, but I will discuss the most important aspect.  For a calendar, the most important thing is that you display the right data for each day.  After going through a couple of ugly algorithms, I hit on the concept that the date associated with each sub-form could be determined by incrementing a counter (intDayCounter) as I looped through each week and day.  If you then subtract the weekday of the first day of the month from that value and add it to the first day of the month, you get the date that should be displayed for each day.  The formula looks like this:

dtFilter = DateAdd("d", intDayCounter - intWeekDayFDOM, dtFirstDayOfMonth)

Once you have that value, it is easy to determine the day number to display in lbl_Day.Caption, determine whether the day should have a black or gray foreground color, and whether to display the sub-form or not.

intDayCounter = 0
For intWeek = 1 To 6
    For intDay = 1 To 7
        Set ctrl = Me.Controls("sub_Cal_W" & Format(intWeek, "0") & "_D" & Format(intDay, "0"))
        Set frm = ctrl.Form
        intDayCounter = intDayCounter + 1
        dtFilter = DateAdd("d", intDayCounter - intWeekDayFDOM, dtFirstDayOfMonth)
        frm.lbl_Day.Caption = Day(dtFilter)
        If Format(dtFilter, "YY/MM") = Format(dtCalendarDate, "YY/MM") Then
            frm.lbl_Day.ForeColor = RGB(0, 0, 0)
        Else
            frm.lbl_Day.ForeColor = RGB(215, 215, 215)
        End If
        ctrl.Visible = intDayCounter <= intDaysToDisplay
        frm.Filter = "[Appt_Date] = #" & dtFilter & "#"
        frm.FilterOn = True
    Next
Next

With a little additional effort, you could modify the height of the subforms and their position on the form based on the number of days to actually display.

Adding/Editing an appointment:

Rather than displaying record selectors in the sub-forms, I elected to place a much narrower, unbound textbox on the far left of each record as a faux record selector.  I set conditional formatting on the textbox to display with a grey background unless the record has the focus, then it displays in blue.  To add an item you can simply click on the day in the upper left corner of each subform, or double-click on the empty record (last entry) in each sub-form.  To edit an existing appointment, simply double-click this "record selector"

I hope that the simplicity of this application will convince those of you that are using multiple identical sub-forms to switch to this very simple technique.

Sample Database to Download: Calendar_for_Article.accdb