By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Lookup on subform

P: 6
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!
Dec 14 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT PerformAudio.*, Performer.* 
  2. FROM PerformAudio INNER JOIN Performer 
  3. ON PerformAudio.PerformID=Performer.PerformID
Then the query should be updateable. The parent child relationship would be based on Audiovisual.AudiovisualID to PerformAudio.AudiovisualID
Dec 14 '08 #2

Expert Mod 10K+
P: 14,534
For further reference to why a subform would not be updateable check out this msdn article.

When can I update data from a query?
Dec 14 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.