473,327 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Lookup on subform

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
2 1792
MMcCarthy
14,534 Expert Mod 8TB
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
MMcCarthy
14,534 Expert Mod 8TB
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

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

Similar topics

9
by: Koen | last post by:
Hi all, My application uses a lot of lookup tables. I've splitted the frontend (forms, reports, etc) from the backend (data). The database has around 10 different users. The values in the...
0
by: Mat | last post by:
Hello all, I have a form which contains a subform. The subform is a datasheet. With the datasheet I have the following fields 1) ID 2) Type 3) Workshop
0
by: Mat | last post by:
Hello all, I have a form which contains a subform. The subform is a datasheet. With the datasheet I have the following fields 1) ID 2) Type 3) Workshop
1
by: Mat | last post by:
Hello all, I have a form which contains a subform. The subform is a datasheet. With the datasheet I have the following fields 1) ID 2) Type 3) Workshop
3
by: Eric | last post by:
I try to get the value in subform 2. So i go to the table of subform 2 name tbl_Events and create a field name equip and in the lookup i wrote this query but it still not showing any thing in the...
3
by: john | last post by:
In my form (table A) I have subform (table B (only 2 fieds: ID and App_name) where table A -Table B are linked 1XM. To be able to add a record in the subform I want to use a lookup form since the...
4
by: keri | last post by:
Hi everybody, I'm very new to access so please treat me with kid gloves! (i'm hopeless with code and macros but enjoying learning). I am loving what it is capable of though, and i'm sure I...
2
by: pats2kdynasty | last post by:
I have a bunch up lookup tables that I want to allow the user to edit. I am trying to accomplish this via one form with a drop-down list of the lookup tables. When the user selects a table the...
3
by: dancole42 | last post by:
I'm self-taught in Access, and as such I'm missing large chunks of knowledge, so I'm hoping someone here with some training can help me. Right now I have an Invoice form with a Customer subform....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.