On Not in List

[twonames]

My brain is fried, if anyone can come up with some inspiration for my problem I would be most grateful.

In an access project (.adp) I have a many to many relationship between two tables using a third table to store the links.

The records in each of the two primary tables have a primary key based on an autoincrementing integer. Therefore the primary key on the third (or relationship holding) table consists of the combination of the first two primary keys.

On a form for one of the main tables I have a subform that constitutes a listing of all the attached records from the relationship table. I need to be able to add records in this table using a combo box listing all the records from the second table bound to the ID but showing the names (ie two column, bound to col 1 but with col 1 width set to zero). So far so good.

The problem arises when I need to add an 'On Not In List' event to enable me to add a new record to the second table at this point.

Any ideas as I seem to get stuck with generating a new record in the relationship table and unable to add a name to the second table record.

I don't think I've explained this very well, but any help would be most appreciated.

TIA

Twonames

 

[WildHare]

I'm not sure I completely follow, but let me take a crack at it - if your 'second' table is keyed by an autonumber, you should be able to insert a new record there pretty easily. Are you having problems doing this?

The second part of your question, unless I'm missing something, seems to be updating the 'join' table that sits between the two main tables. Obviously, this guy will need to know about the new record in Table-2, right?

If the INSERT to Table-2 is successful, it should be no trick to MOVELAST in it (positioning to the new record), grabbing the new value(s), and then shoving them into the JOIN table.

I just feel I'm missing something here - I try not to use M~M relationships very often, so I'm probably not the best person to answer this problem. Let us know if this opens any avenues of solution.

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[twonames]

Thanks Jim

I see where your at, tried it and got the response I wanted. Thanks for your help.

Cheers

John