Changing RecordSource at Mainform with a Subform creates different behaviour

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
mpimAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark EdwardsChief Technology OfficerCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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.
0
mpimAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.