Access Form/Subform Using Same DAO Recordset

Question

I am new to programming VBA for Access.  I have seen many examples using two recordsets, one for a form and the other for its subform, with the two synchronized in code.  I created ONE recordset and assigned it to both the form (record detail) and subform (record selection for the main form).  The two forms are synchronized and no additional code was needed.  That is, when I click on a record in the subform datasheet, the main form changes records to match. 

My question is:  Is this the wrong way to connect the two forms?  It SEEMS to work, but am I asking for trouble later?

I can think of no instance when the two forms would have or need a different recordset.  Also, the form goes into a navigation form, so a split form is not possible.

Thank you for any advice.

Answer 1

Thank you!

Answer 2

Hi Estimator, I'm an independent adviser and will try to help.

First, there is no VBA involved in what you have done. Essentially what you have done is create a Split Form. A Split form is a special type of form where there is both a single form view and a datasheet view. The idea of a Split form is to give you a way to use datasheet filtering to find the record you want, then display that record in a single form view.

That is not what subforms were designed for. A Mainform/Subform combinate is design to show records from RELATED tables on the same form. Usually this is done to display 1:many relations so you can see all matching records in the many side. An example of this might be a Student application. Student may take multiple courses. So you would have a main form bound to the Student table and subform bound to a Courses table. The subform would then display all the course the student is taking. So there are, in fact, many, many reasons why a subform would use a different recordset.

I just noticed that you stated that this goes on a Navigation form. Which is why a split form doesn't work (I'll take your word that it doesn't work). So you may have stumbled on a viable alternative to split forms to use within a Navigation form. Personally, I've never seen much value in Navigation forms and rarely use them.

I can't say you are wrong in doing this, especially considering the Nav form issue. And I can't think of any reason this might cause problems down the line. But it is important that you understand what mainform/subforms were designed for and the many uses they have.