One form, two data sources

As a part of the system that I’m developing, I needed to copy a form.  The two copies were identical, and the data set was the same, the difference was in filtering.  The RecordSource for each was a different query, one connected to a view on my server, the other to a query in Access.  I thought this was a ridiculous thing to have two copies with such a trivial difference, so I went after it and came up with a pretty cool solution.

First, I added a hidden text box called txtRecordSource to the menu form.  I made it visible for testing.  Then I changed the buttons that the user clicked from macros to open the form to the following:

Private Sub cmdEditStudents_Click()
    txtRecordSource = "dbo_vwStudentsFiltered"
    DoCmd.OpenForm "frmStudents"
End Sub
Private Sub cmdCaseloadEditStudents_Click()
    txtRecordSource = "qryCaseloadStudents"
    DoCmd.OpenForm "frmStudents"
End Sub

I could have used the FilterName or WhereCondition parameters but decided not to.  I wanted the student filtering to happen at a lower level so that filter could be used in other places and changed in only one.

Within frmStudents, I added the following code:

Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = Forms!frmMenu!txtRecordSource.Value
End Sub

That’s all there is to it.  Whenever you change the RecordSource of a form or report, it automatically requeries the underlying data.  I’m considering applying the technique in some other places throughout my system, it will require a little more work but might be worth it for a long-term maintenance perspective.

If you’re in the United States, I hope you enjoy your Thanksgiving holiday.  If you’re not in the USA, have a good remainder of the week and a wonderful weekend!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s