Subform links

[Saintsman]

I have a datasheet subform which holds workshop details Value; Risk; DLS
Each workshop is run by a leader, sometimes the same leader can run more
than one workshop. Each Workshop is a yes/no option in the table & a leader
is associated with each
On the main form I have a simple combobox to select a leader. How do I make
the subform show ALL workshops that leader ran?

 

[KARL DEWEY]

It sounds like you have your table structured as a spreadsheet like this --
Name Shop1 Shop2 Shop3 Shop4 Shop5 Shop6
John Brown X X X
Bill Jones X X X X

It should be like this --
Name Shop
John Brown Shop1
John Brown Shop2
John Brown Shop4
Bill Jones Shop1
Bill Jones Shop3
Bill Jones Shop5
Bill Jones Shop6

To use what you now have you need a union query like this to feed the
subquery --
SELECT Name, "Shop1" AS Shop
FROM YourTable
WHERE Shop1 = -1
UNION SELECT Name, "Shop2" AS Shop
FROM YourTable
WHERE Shop2 = -1
....... through .....
UNION SELECT Name, "Shop6" AS Shop
FROM YourTable
WHERE Shop6 = -1

The union query will only display, not be edited or add records. If you use
a table as I suggested above then you can have leader in main table - main
form and their shops in the subform and use a combo to select shop when you
add records for them.