Link a Record in a Subform to a Form

[ddrew]

I have a form (frmAddBeaterToShootDay) with a subform (frmChooseBeaters). I want the subform to show all the records of all the beaters in the contact list, which it does. I then want to be able to tick the box (AddToShootAsBeater) and associate that person with that record on the form (frmAddBeaterToShootDay).

When I go to the next record on the form (frmAddBeaterToShootDay) again I want to show all the beaters on the subform but all the tick boxes in (AddToShootAsBeater) should be blank until I tick the required ones.

I think I should be doing this as a Many to Many relationship which I have done from (tblContacts) (tblShootBeaters) (tblShootDates).

The problem is when I put all three tables in the query I get no results in the subform!

 

[Mihail]

Hi
I can't help you very well because I don't understand very much from what you are trying to achieve.
What I can say is that the double relationship for table tblShootBeaters (one from tblContacts and another one from tblShootDates) is NOT ok because the ContactID (tblShootBeaters) cant be simultaneously equal with ContactID (tblContacts) and with ShootDateID (tblShootDateID).

Advice_1) Set the referential integrity for each (every) relationship. This will give you a better image about the whole database.

Advice_2) Always use the same name for a field in the "child" table and in the "parent" table. If you can't, is sign that something is wrong (here you can't adapt the ContactID field name in the "child" table tblShootBeaters to be the same with linked fields names in "parents" tables => something is wrong).

Advice_3) For the ID field use AutoNumber. There are very very rare cases when you can (not when is necessary; is NOT necessary) use other data type than AutoNumber.

Advice_4) Only in order to visually manage more comfortable yours databases: Start the names for ID fields with this two characters "ID" (ID_Beaters, ID_SpeciesType etc).

Advice_5) Divide et Impera (Filip the second, Macedonia King, 382-336 BC).
Lets take a closer look to table tblContacts:
Have you ever need to search either by FirstName, either by Surname ? If YES then your table is well designated. If NO then a single field Contact_Name should be enough.

In the next 5 fields you store the address(es) for the Contact. So, a new table tblAdresses apear as necessary (ID_Adress, ID_Contact,ID_Town,Address,PostCode) and you can remove from tblContacts all that 5 fields. The tblContacts start to look better.
The same for the next 4 fields: A new table tblCommunications(ID_Communication, ID_Contact, DeviceType, HowToUse) will allow you to remove that 4 fields from tblContacts. Much better.
While I can't translate in Romanian the names for the rest of fields, I can't say more (if you can catch some of them in a table).

Hope this is a help for you, beyond my poor English.
Good luck !