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!