Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

notinlist doubles with join table

Discussion in 'Information Technology' started by anna@peerbolt.com, Jul 28, 2006.

  1. anna@peerbolt.com

    anna@peerbolt.com
    Expand Collapse
    Guest

    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?
     
  2. Loading...

    Similar Threads Forum Date
    This Above All: Masters of Doublespeak Hard Talk Jan 9, 2010
    Sikh News Yield doubles as Punjab ryots adopt Pak method (Express India) Breaking News Mar 11, 2008
    Sikh News Punjab and Sind net doubles to Rs 219 cr (The Economic Times) Breaking News Jun 2, 2007

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    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@peerbolt.com> wrote in message
    news:1148055134.901556.56470@u72g2000cwu.googlegroups.com...
    >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?
    >
     
  4. anna@peerbolt.com

    anna@peerbolt.com
    Expand Collapse
    Guest

    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.
     

Share This Page