I run organised holidays for paraglider pilots. It is possible for each person (Client) to book more than one holiday (Tour) and each tour has more than one client, so I set up a Many-to-Many relationship with the following tables - ClientT; TourT; and ClientXTourT. The payment for each tour can be made in several parts so I set up the PaymentT with a One-to-Many link from the ClientXTourT. I then built a PaymentF and a PaymentSubformF to show the payment history for each client.

I managed to incorporate the PaymentSubformF into the ClientF and eventually got it to show the payments that had already been made.

I have two problems that I have spent many hours trying to resolve, unsuccessfully:

1. When I try to enter data into the subform, or into the PaymentF independantly, I get a warning message appearing in the Status Bar saying "Cannot add record(s); join key of table 'PaymentT' not in recordset." I have tried basing the forms on queries instead of the tables and I have tried changing the linked fields on the form and subform but nothing works.

2. I need to specify that each person can only be on one specific tour once. I tried changing the field index property in the ClientXTourT table to show 'Yes (No Duplicates)' but I get the following error message - "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." I have checked the data but can find no duplicates. I have tried removing the One-to-Many link between the ClientT and the ClientXTourT before changing the field property but it makes no difference.

---

Reply from Alex Hedley:

1. Are you trying to open a subform instead of opening the main form which has the subform in it?

2. You need to base it on two fields, since clientid and tourid can't duplicate but on their own they can since client 1 could go on tour 1 and tour 2 just not tour 1 again. See this tip.