I had originally planned a long explanation of my problem and all the issues connected, but I think I may be able to handle this by requesting a blanket statement on correct form design.
I have a database with three tables, and a form for each of them, call them "Sections", "Issues", and "Questions". In that order, they form a one-many-many relationship with one another. I need to be able to show data from the first table on its form, as well as the "Issues" and "Questions" forms as read-only fields.
Originally this was easy, in the first related form I just added controls for the required field using the Field List, where the Sections table was in the related tables. As you know this adds that table to the control source for the form.
On the second table I couldn't take the same approach because the "Questions" table is not directly related to the "Sections", but linked through "Issues". The solution I came up with was to make two queries (one on the other) that would give me the info I needed, and linked the controls to that query.
All this seemed to work at first, but I have been noticing other problems. The "Issues" form seems not to track the "Issues" table very well, it shows more records than the table does. If I set it up through the query, it tracks just fine. I also have combo boxes set up to filter off one of the controls, but when they are sourced from the query, they don't seem to be able to see the control.
So the ultimate question is, what is the correct architecture for relationships like this? Are their problems sourcing a form from multiple tables? If not, is there a way to do the same thing on my third table? I tried to do it in the query box for the row source, but it wouldn't allow me to join three tables.
Convoluted, I know. At this point my head is spinning because I thought I was one control away from getting the DB done, and now I'm questioning my whole design. Thanks to anyone still with me.