Re: Use a combobox selection to update remaining subform fie
Hi stanjadema
Apologies in advance for the long answer but this might take some explaining. The success of your database will depend on how well your database has been designed.
In the absence of info in your question (so please excuse me if I am covering familiar ground), you will need a people table (i.e. a table of people with a unique identifer, names and addresses etc), an event table (i.e. a table of events with a unique identifier, name, date, location and other info you want etc.) and most importantly you will need a People_Event table.
The People_Event table will consist of just two fields, a person id and an event id. This table will allow you to have a many to many relationship between the people and events (i.e. one person can attend multiple events and each event can be attended by multiple people). The primary key for this table is both fields (highlight the person and event identifiers and click the key symbol), this will prevent one person from attending the same event twice.
Once the 3 tables are in place, make sure the relationships are created (Menu -> Tools -> Relationships) - link the people table to the People_Event table on the person id and link the event table to the People_Event table on the event id. Enforce referential integrity & tick cascade update.
For the form to show all of the bits of information, you need to create a new query that consists of all three tables and include every field from all three tables in the query. Your form will be based on this query. The people info (from the people table) will be shown in the main part of the form, and the events and People_Events info will be shown in the subform part of the form.
I created a form with a subform arrangement by using the new form wizard (after clicking new form, I selected the query, clicked Form Wizard, clicked OK and then clicked Finish - without changing any options whatsoever and the first attempt was pretty good from the wizard).
There were however a couple of things I did change : the event_link_id from the People_Event table can use a combo box that looks up the values in the events table (I usually prefer to have combo boxes looking up another query rather than the table directly so that I can sort the info in the combo box to my liking - your choice) and use this to replace the People_Event.event_link_id field on the form; and I also set the "Enabled" property to "No" for every other field in the subform (so the only things the user can do is to select a person on the main form and then choose events from the subform).
Hopefully this will get you going in the right direction. If you have any other queries relating to this, then reply to this post and someone here will be able to help you.
HTH, Andrew.
