Changing RecordSource at Mainform with a Subform creates different behaviour

Michael Paravicini
Michael Paravicini used Ask the Experts™
I’m stuck. I have a Form/Subform frame with the specialty that I will use different pairs of tables depending on need. One pair is Quotes/QuotesDetails the other pair of tables are Reservations/ReservationDetails. I change the RecordSource at the Load event of the mainform. The two tables are linked via GRESID for both sets of tables. The QuotesDetails and ReservationsDetails have RESID as unique additional key. Now, in the case of adding a new record for the set Quotes/QuotesDetails it will generate the Quotes!Gresid and the QuotesDetails!Resid but NOT the QuotesDetails!Gresid. In the case of the other table set the following happens when adding a new record: Reservations!Gresid and ReservationsDetails!Gresid are correctly generated when changing the first control of the main form. However, the RESID is only generated once I change something in the subform which seems to me to be the correct behavior. Does anyone understand why the behavior is different with the quotes set of tables and why the QuotesDetails!Gresid IS NOT shown? When I step thru the code the sequence is absolutely identical! Sorry if it all sounds very confusing but I would tremendeously appreciate any help! Rg Michael
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chief Technology Officer
The proper operation of mainform and subform with linked keys depends on the defining of Relationships in the Relationships window.
The importance of the Relationships window is an often overlooked piece of the Access application design.
It's one off the many design support pieces that tells Access's parent/child tables and forms/subfoms how to handle the relationships of the record in one table/form with the child record in the child table/form.

If you look at your Relationships window, I believe you'll find that you're missing this relationship that's not working as you expect.
If you don't use the relationship window, then you have to code your operations to handle things the way the Relationships window would do for you.  
If you don't use the Relationships window OR code your own handling operation, then you arrive at the "sticking" point that you're at.
Distinguished Expert 2017
I have a Form/Subform frame with the specialty that I will use different pairs of tables depending on nee
If the schemas are identical, then everything should be in the same table.  Use a type field so you can distinguish between quotes and reservations.

There is no benefit to using one form to support multiple different types of data when you have code that needs to distinguish between one operation and another.


Thank you to both of you. I do use relationships and they are defined exactly the same way and yet I do have a different behaviour. The reason for using different tables is that quotes may reach very large numbers and would slow down the overall system if i keep them all in one single table. Thanks anyway for the help. Regards Michael

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial