One-to-Many Table/Form quirk 1

[midnightJackal]

I have a main table containg lists of Devices. I have a related table listing recent diagram Revisions that is related to the Device table via an autonumber field w/ a one-to-many relation.

All the table relationships work perfectly, everything displays fine when looking at it from the back end.

When I open a form to display my data, it shows each Device as many times as there are Revisions. So, if my Device has had three Revisions, and there are no other Devices in the database, the form still shows three Devices - displaying the same information.

Is this a relationship issue? Or should I take this to the Form forum?

I appreciate any insight you can lend,

MJ

 

[JeremyNYC]

MJ,

It's a problem with the recordsource of your form. It sounds like you've got the recordsource set to gather data for the many side. What you want is a main form for the one side and a subform for the many side. The main form holds data for the Device. The subform holds data for the Revisions. Make the subform display in continuous view, and you'll be able, on the main form, to flip through the Devices one by one. On each one, you'll see a list of the Revisions _for that Device_. You set that relationship up with the MasterLinkFields/ChildLinkFields property of the control that holds the subform--make them equal to the Primary Key and foreign key of the respective tables (hopefully the fields have the same name, but that's a question of style).

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[midnightJackal]

I'm sorry, I should have been more descriptive the first time.

I am using a Main form with a header to display all general information about these devices, and then in the actual form section of the Main form I am using tabs to display data from different tables ( i have a total of 5 tables that each contain information about each device ).

One of my tabs contains a subform that pulls information from the Revisions table and displays it as a datasheet. I didn't have my form set to 'continuous', so I changed that and now it looks much more polished.

The Master/ChildLinkFields were set correctly (and yes, my keys have the same field name *grin*).

I am still, however, encountering some issues with the main form. I think it probably has something to do with relationships that I have established, which was my motivation to post in this topic originally.

In the Main form, even though I only have one Device listed in my Device table (related with a one-to-many relationship to the three revisions in the Revisions table) I still have a total of 3 total records displayed at the bottom of the Main form. By clicking through the records (using the forward/back buttons at the bottom of the Main form) there is nothing obviously different about them - the layout is the same, the same Revision number is displayed in the Revisions tab-subform.

All of my other tables contain information only related to a single Device (ie. no duplicate Device numbers in any of the tables _other_ than the Revisions table, and therefore only one-to-one relationships between the Device table and the other tables).

Thank you for your help so far, I really appreciate it. I think that I'm going to fiddle with things for a while (relationships, table links etc) and see if I can drive out the problem with experimentation. I realize that this is a rather complicated problem with, likely, a simple solution.

If you have any more insights, I would be receptive to them, but I also realize that this is probably one of those things that needs to be handled in person (or at least that's the way it's starting to seem, more and more).

Have a great day, Jeremy!

Thanks for pointing out that 'continuous form' bit, that made things visually tighter!!

-MJ

 

[JeremyNYC]

MJ,

Can you post the recordsource for your main form? My guess is that you're using a query that combines the Device table with the Revisions table. If so, you'll want to change that, because it will always give you three records like that.

JW

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.