displaying many to many linked tables

Your query contains "ContactEvent.ConteventCustID = Events.EventID" as link, while your diagram suggests "ContactEvent.EventID = Events.EventID", but that isn't the point.

The main interface solution to display one-to-many relationships is a form with a subform; the subform showing the "many" side. The main interface solution for many-to-one relationships is a combo box. Hence, many-to-many relationships can be viewed as a form with a subform, itself containing a combo.

* Create a form on Contacts. Just the table, not a query.
* Insert a sub-form showing ContactEvent.
* Link the main form to the subform using the key fields
* For the field EventID, display a combo box, based on Events

When you navigate to a contact without events, the subform will be empty. But the "new" record will be visible, with a combo to select an event.

Does that help?
(°v°)

Thanks very much for your reply.
Yes I had linked the wrong fields in the query, that's now correct.
I am still having problems with links though.  I can select an event ID from the ContactEvent subform combi box, but I get an error because of the links to the donations and events tables. and if I link them through a query , then I get nothing showing.
  Do the links in the relationships diagram just affect referential integrity, or do they have a further effect?
How can I show a subform so that it also includes details such as event name and donation, which are in separate tables?
Thanks again for your help, I need something to unscramble my mind!!
John.

Thanks so much for your detailed reply, it has been really helpful
I made a copy of the database so that I could mess around with it, and everything works fine up to making the contacts form with a ContactEvent Subform witha combi box showing the event name, the only problem was that it would not allow me to enter data as it gave an error    "You cannot add or change a record becaus a related record is required in table 'Donations'"     I realised that there is no point in the doantions table, it can all be included in ContactEvents, so that problem went.
When I include the events table in a query with ContactEvent however I get problems as there is no data showing in the query, the combo box disappears and I cannot select anything.
If I can just get over this hurdle, I think the rest will be relatively plain sailing. (I can always hope!)
Thanks again for spending the time to help me.
John.

Since the key of ContactEvent is dual, ConteventCustID & EventID, you need a matching record in both master tables before creating a record. (If the donation ID is required, the same will be true for donations, if if it isn't part of the key.) To link the two tables in a query, use something like:

SELECT CE.*, E.EventName
FROM ContactEvent CE INNER JOIN Events E ON CE.EventID = E.EventID

This will show all fields from CE, and one (or more) fields from the related event. If you change the field CE.EventID, you should see the related field change automatically.

If you don't see any data, it probably means you have set the relationship incorrectly. This would be the case for example if you have linked ContactEvent with Donations using INNER JOIN, but DonationID is Null. If the foreign key can be Null, you need to use LEFT JOIN, so that all records are displayed, even if there is no related record from the joined table.

You can post the query you use here, if you like.

Another possibility is that, when you use a query instead of a table, the subform wizard is no longer able to set the link fields automatically for you. If you are working on the main form Contacts, make sure you have these properties:

    Link Master Fields: ContactID
    Link Child Fields: ConteventCustID

Good luck!
(°v°)

Thanks for all your help.
Things are now beginning to work properly.  I am not sure exactly what the last problem was, eventually I deleted the query and re-made it, the result seemed to be exactly the same except that it worked!
The eventual code is below.
A lot more to write yet, but hopefully I can solve the problems without coming back.
John

SELECT ContactEvent.*, Events.EventID AS Events_EventID, Events.EventName, Events.Location, Events.Date, Events.Cost, Events.Comments
FROM Events INNER JOIN ContactEvent ON Events.EventID = ContactEvent.EventID;

Select allOpen in new window

Very helpful explanations

Thanks, and please don't hesitate to come back when needed!
(°v°)