One to Many DB - one form?

[bees292]

Hi

I have a one to many DB. One 'Paper' (academic journal articles basically) can contain many 'Experiment' records. I'm also thinking about adding another table of Authors, so one Paper can have many Authors, but this is not that important here.

I am wondering whether it is possible to have a form display info from both tables (Papers and Experiments), whether this must be done using a query or whether I have to use mutliple forms....

At the moment I have a form that displays the Paper info and then a button is used to open the 'Experiments' form which displays the relevant data (I figured out how to link these up to display the relevant record). However, this is rather messy, doesn't look very good and if I have to take the 'Author' field out of the 'Papers' table into its own table things are going to get very messy...

Could someone suggest a way in which I might do this. It seems some sort of query might be in order, but I'm fairly new to Access and cant quite figure the best way to go about this. Thanks

 

[KeithIT]

You can certainly do it using a query which would be the easiest method. Just make sure you set your relationships correctly in the query.

 

[directormac]

Subform?

Perhaps I'm missing something, being a bit rusty, but what about an Experiments subform within your Papers main form?

 

[bees292]

Didn't spot the right mouse click option on the relationship. Have now set it to 2 (Inc. only experiments that match the correct specified field). But now I cant get a form to use this query as a record source!!! I have the form's control source set to the query, but selecting a source for a text box brings up "Type Mismatch" error. What? My head hurts. Thanks for your help...sorry for being dim!

 

[bees292]

DirectorMac - Yes was trying to learn how to use subforms, but my Access book says very little. Do you have a good link (somewhere on this forum?)

 

[directormac]

F1 is your friend...

As always, start w/ MS Access Help. Put in "about subforms" as your topic. Then check the Northwinds sample to see how they do it. If you need further guidance, search this forum on the keyword "subform".

 

[bees292]

Thanks. This is clearly the way to do it.