Combo boxes not filling tables

[CalgaryGal]

I am creating a database of Plays. My tables and relationships are created as follows:

Relationships

I want to create Combo Boxes for the Title, Company, Author, Director, and Producer fields. However, I am having trouble getting the data input into the form to populate the proper tables. If I set the Form Properties-->Data-->Content to "Title" for the Title combo box, that Content line changes to "Title" for all of the other combo boxes as well. In that case, the Title table is updated with new entries, but the other tables are not.
Am I missing something really simple? (I hope!)

Form properties

[kabing]

The main form needs to relate to one table. For other tables, you need to add subforms, one for each table.

This tutorial might be helpful in that regard, although it's in the middle of a longer tutorial for NeoOffice. Since NeoOffice is based on OpenOffice.org, the instructions should work fine for OpenOffice.org, if you keep the following things in mind:

• 1) references to the command key in a Neo tutorial are the equivalent to the control key in OOo. (i.e. Command-C becomes Control-C) Note that I'm not sure if this is true with OOo for X11.
  2) control-clicking is the same as right clicking
  3) icons sometimes look different, as NeoOffice 2.2.2 has a custom icon set.

You can also search for "add subform" (without the quotes) in this form to get more info.

kabing

[CalgaryGal]

Ah, so the whole problem is my wanting to reference more than one table in my form?

[kabing]

Yes and no. There's a certain overlap in the nomenclature which can get confusing. So let me start with some definitions:

-form: a writer document, usually embedded in a database "document" containing one or more main forms and--optionally--one or more sub forms.

-main form: a collection of form controls, which are linked are linked to fields coming from one (and only) table or query. [can include form controls not linked to any fields] Main forms are essentially independent of other main or sub forms on the form. The table from which data is drawn is specified in the Form Properties window, and applies to all form controls in that form.

-sub form: a collection of form controls, which are linked are linked to fields coming from one (and only) table or query. [can include form controls not linked to any fields] Sub-forms are dependent upon a main form or another main (or sub) form on the form. In the Form Properties window, you specify a master and slave field. The master field is the field in the main form that relates to the slave field in the sub form.

For example, if you had a database that tracked actors and plays, You might want a form that displays biographical information about an actor, and that included a list of all the plays that actor was in. Say the database has three tables: Actors, Plays and Actors-in-Plays (this last table would link Actors to particular plays...but that's another issue). The Main form would be based on the Actors table and would display one actor at a time. You would then create a subform, based on the Actors-in-Plays table, that would display as a table or list. You specify Actor ID as the master/slave field, and the subform would show a list of plays that actor has been in.

NOTE: I have over simplified the process here for the purposes of trying to explain the basic concepts. In particular, the sub form in the example above should probably be based on a query, not a table. But that has to do with principles of database design (in the sense of table structures), not creating forms.

For more specific help with your project, I need a better idea of what is stored in each of your tables, and what you are wanting to do with them.

kabing

[CalgaryGal]

It seemed like such a simple thing at the beginning ... (famous last words!!) I have tons of programs hanging around from plays we have seen, and we keep asking ourselves, "Where have we seen that actor before?" So my bright idea was to consolidate all of these programs into a database.

My main form would have:
- Date
- Play Title
- Company that Mounted the Play
- Author of the Play
- Director of the Play
- Cast and characters in play (I've already created a subform that works - yea!)
- Musicians in play (will probably be a subform as well)
- Notes about play (will be a memo field)

I wanted to have a table called "Names" that was just that - an ID field and a Names field.

Then I wanted to be able to populate that field from my form using combo boxes in the author and director field.

I guess, in taking a hard look at things, there is really no need to go to all that bother, since the likelihood of spillover of names from Authors to Directors is pretty low! Maybe I'm just trying to be too fancy when simplicity is the key.

I could just set up queries for the names of people that I want to check on, couldn't I - and just include the various tables (author, director, cast) in the search parameters.

Mostly, I am treating this like all of the other tutorials out there - as a learning process. I've been going through them all, picking up bits and pieces of information - and almost getting intimidated at the amount of things out there that can be done! My old brain just doesn't process this all that quickly any more.

[kabing]

If you want to account for the possibility of one person being both an Author and a Director or both Director and an Actor, etc., then here's one solution (I presume an existing Plays table):

-Create a People table, containing names and any other desired biographical data on the people. Everyone--actors, Directors, Authors, etc.--go into this table. One field would be an ID field (say People_ID)

-Create an Actors_in_Plays table, to link someone from the People table to a play as an actor. (If you wanted, you could include a role or character field in this table, to note, for example Brent Spiner played Franz (and Dennis!) in the 1984 Broadway Production of "Sunday in the Park with George"

-Create similar Director_of_Plays and Authors_of_Plays tables as well.

kabing