Lookup on subform

[kandroski]

On 2/11/08 (Post #3), ADezii helped Robert Johnson with a many-to-many table question. I have the same question - I have a table called audiovisual item (VHS tapes or DVDs - similar to a book). An audiovisual item can have multiple performers (similar to authors), and a performer can be connected with multiple audiovisual items. I created a form which has the audiovisual item details at the top, with a subform for performers at the bottom. When a user goes to the performer subform, I want them to be able to either select an existing performer from the performer table, or add a new performer to the performer table. I have a junction table and a query which brings up the correct information for the form. I tried to create a lookup from the performer subform, which seems to involve a combo box and row source, but it didn't work.

Thanks for your help!

[MMcCarthy]

If for example the audiovisual table was AudioVisID, AudioVisType, AudioVisName and the Performer table was PerformerID, PerformerName and the Join Table (PerformAudio) was AudioVisID, PerformerID. Now for this to work both AudioVisID in Audiovisual table and PerformerID in Performer table have to be auto numbers. Now if the record source of Main form is the Audiovisual table and the record source of subform is the following query:

SELECT PerformAudio.*, Performer.* 
FROM PerformAudio INNER JOIN Performer 
ON PerformAudio.PerformID=Performer.PerformID

Then the query should be updateable. The parent child relationship would be based on Audiovisual.Aud iovisualID to PerformAudio.Au diovisualID

[MMcCarthy]

For further reference to why a subform would not be updateable check out this msdn article.

When can I update data from a query?