Unanswered: Form for many-to-many-to-many relationship
I'm creating a database akin to a real estate application. Basically I have a table for realtors, one for properties and one for property owners. I created a junction table that has the foreign keys for each of the three tables and a PK of its own. My problem is that I' trying to create a single page form, for ease and speed of entry, that allows the user to enter all of the information simultaneously. My problem is that sometimes some of the data is already in the system and other times the user must enter new data. Is there a simple way to create a form similar to the following:
(Lookup) (Lookup) (Create New)
Property Info Broker Name Broker 2 Name New Broker Name
Address Broker Phone Broker 2 Phone New Broker Phone
State Owner Name Owner 2 Name New Owner Name
Zip Owner Phone Owner 2 Phone New Owner Phone
I need to permit the users to add multiple owners and/or brokers to a given property or to create new ones on the fly.
So the property is the main form, allow additions
the brokers are a sub form and the owners are a subform
each sub form has a button that pops up a form to allow adding a new broker or a form for adding a new owner. As the pop up form closes, it will add this newly added record to the junction table for this property, then requery the effected subform.
Just about everything that I suggested should be able to be done using the wizards. I'm sorry, but I can't help with the wizards because I don't use them. But I do know that they will do everything except help you get the subforms requeried when the Add-A-Record forms are closed. When you get to that point, I'm sure you can get some very capable help here. And, if you need more help before that, ask away.