Beginning forms. How to access another table with a combo box?

[Deweyusa]

Hi.

I have what seems like it should be a beginner Access question. I have an extremely simple database for a magazine. It has a table of stories to be written, a table of people, and a table of roles those people can play (there are 3: author, photographer, and editor). There is a junction table between all three aforementioned tables to link them together.



I would like to have a form that allows me to cycle through the stories and enter relevant information. No problem when that info is in the Stories table. However, on this form, I would like a combo box that allows me to select from the people table and assign people to their various roles for each story. This should update the junction table with that persons role when it is chosen.

I quickly realized that to have the information available in the form, I'd need to modify its Control Source with a query of my own, rather than use the default Wizard-created Stories table as a Control Source. However, when I did that, I realized I had numerous results in the query for the same story (where, for example, I one result with story 1 and a role of photographer, then another result with story 1 again, with the assigned author). As I then cycled through the records on the form, I wasn't seeing each story, but rather each result from the query. I know, this is simple stuff, but I'm new to forms. If I take out the custom query, I lose that role information within the form.

Is my solution a different query, a different organization of the relationships/table structure in the database, the use of DLookup for the combo box, a form based on a different table, or some other method? I realized this may be an instance where a subform would help (show the junction table there), but ideally, I wanted to not show a subform, as this seems kind of cluttered to me. Does doing it the way I'm suggesting (in a combo box) add the requirement of a lot of background VBA magic?

Thank you for any help. Here is a snapshot of the relationships, and attached is the database with a bunch of stuff from the Stories table stripped out to just illustrate the point.

Writing Database.zip




-Dewey

[June7]

Not sure why you feel a form/subform arrangement is 'cluttered'.

Main form bound to Stories. Subform bound to RolesStoriesPeople. Two comboboxes to select Role and Person.

Otherwise, a form bound to RolesStoriesPeople and 3 comboboxes to select values.

Comboboxes can be multi-column in order to display related info from the 'lookup' table in the dropdown list.

Most experienced developers would break a name into multiple fields: FName, MName, LName

[Deweyusa]

I see where a subform would work, I just figured a separate window showing one would seem a little, well, cluttered, but I didn't consider that perhaps I could alter (or get rid of) the subform's border, which might be just fine for making it "blend in" to the main form. That might work.

But also out of curiosity, I wondered if what I'm trying to do is even possible, or if it's just an unusual way of trying to do things. I read somewhere else on this forum that generally, one form is tied to one table. Is that common practice?

Thanks for your response!

[June7]

Yes, usually one form for data entry/edit to one table (I do have a db that does allow edit of existing records from multiple tables on one form because of 1-to-1 relationship but, yes, that is unusual). The lookup tables can be included to display related info but do not allow data entry/edit via the same form.