notinlist doubles with join table

[anna]

I have a many to many situation (two tables with a join table). I have
a form with the primary table (Projects) and a subform (keywords which
also includes the join table). When I add a new keyword, using
notinlist event for the idkeyword field in the subform (which open the
keyword table to add the new keyword) --- the new keyword appears twice
in the keyword table and once in the join.

Example: I want to add the keyword Banana. I write it in the keyword
field in the subform and am taken to a popup form -- I type in Banana
and it shows the idkeyword as being 100. I shut that form and Banana
appears in my subform, but the idkeyword (of the join) is 101. Open up
the keyword table: idkeyword 100 is Banana and idkeyword 101 is Banana.
Open up the Join table, 101 is Banana joined to the right idProject

What can I do to add the new keyword only once in the keyword table as
well as only once in the join?

 

[Pieter Wijnen]

First Of all make sure to have a unique index on the keyword table.. ie
keyword coulumn.
seccondly examine your code as to why duplicates are made

NTF today

Pieter

 

[anna]

I have finally solved the problem. The VB works great (Use NotInList
Event to Add a Record to Combo Box
http://support.microsoft.com/?kbid=197526).

For anyone else who has this problem, I'd like to tell them how I did
it since to me it wasn't an obvious choice. --- By the way, I got the
hint from searching this group and seeing someone else who was trying
to do the same thing and was doing something different from what I was
doing -- the key is having the subform be the Join Table and choosing
the right table for the SQL is within that form.
....anyway...here is the way it now works.

Table Products has idProducts and other fields. Table Keyword has
idkeyword and a keyword field. Many products have many keywords and
visa versa. The Join table has idProducts and idKeyword only and the
relationship goes from the Table(s) to the Join one-to-many.

I made form/subform choosing all the fields from Table Products and the
fields from the Join table (the Join fields become a subform known as
Joinsubform and the entire form has to be a datasheet style). Then I
made a form from Table Keyword in a simple collumar style.

Open the Joinsubform and make the idkeyword a combo box. In the SQL
statement, choose the Keyword Table so it reads: SELECT
[Keyword].[idkeyword], [Keyword].[keyword] FROM Keyword ORDER BY
[Keyword].[keyword]; -- Select Yes for Limit To List -- select Column
Count 2 and Column Widths 0";2" so the keyword and not the id number
shows up in the dropdown. Select Event Procedure for On Not in List and
follow the directions from Microsoft (second method) pasting in the
code and changing the wording as necessary so it reflects my tables and
not Northwind.

Then I opened the keyword form and selected its properties and pasted
the Microsoft code into the Event Procedure for OnLoad to complete the
second method.