Results 1 to 11 of 11
  1. #1
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239

    Join table - duplicate records

    Hi,

    I have a join table, and I write to this table via Combobox. Here is my JoinTable:



    ID_Table1 (FK)
    ID_Table2 (FK)
    Active (YES/NO field)

    I want to prevent duplicates of whole records, but be able to change "Active" field to YES or NO. So far I can prevent duplicates, but I cannot change "Active" field on records, that are allready saved.

    Here is my code:

    Code:
    'If Combobox not empty check duplicate of record on 2 fields
    If Not IsNull(Combo5) Then
    If DCount("*", "JoinTable", "[ID_Table1]=" & Me.Combo5 & " AND [ID_Table2]=" & Forms![Table2].Form![ID_TABLE2]) > 0 Then
    
    
    Cancel = True
    Me.Undo
    MsgBox "Entry not allowed. You can add only one record with same Field1 !", vbCritical
    
    
    : Exit Sub
    End If
    Any suggestions on what I should do ?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You should make a compound PK Primary Key of these fields

    ID_Table1 (FK)
    ID_Table2 (FK)

    They are what should be unique in your junction table.

  3. #3
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Hi Orange,

    I'm sorry I didn't post fully. Here is my JoinTable exactly:

    JoinTable_ID(PK)
    ID_Table1 (FK)
    ID_Table2 (FK)
    Active (YES/NO field)

    Is that you meant or something else ? What is compound PK ?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Since you already have a PK on your "join table", you can not have another. A PK is unique within a table.
    So make a unique composite index of


    ID_Table1 (FK)
    ID_Table2 (FK)


    The data base software will prevent having duplicates in a unique index.

  5. #5
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Oh, you meant that ! I tried with indexing before posting, but problem is that I have code in before update event, for "Saving changes". When I try indexing like you suggested, my Before_update code launches, new record is shown as entered and then Access window pops-up for duplicates. Is there any option to at least supress this default Access window with mine as in post#1 and then perform delete of this new record ?

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Have you considered simply not presenting the field value in the combo if it already exists in the table (so that it can't be chosen)?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Have you considered simply not presenting the field value in the combo if it already exists in the table (so that it can't be chosen)?
    No, I haven't ! How can this be done ? Can you show me how ?

    By the way, I managed to solve my problems. I did as Orange suggested, but I had to supress Access message in form_Error event, and slight modifications in my "Save" button.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can't show you as I don't have the necessary info about your form/controls/table etc.
    Can say, make the rowsource for the combo a query or sql statement that excludes values, but I don't know what to tell you to base it on. Take a look at the Unmatched Query Wizard to see if you can make it work. As a test, just try creating a query that excludes the values you don't want. If you intend to apply your creation to the combo box, best to do this in a copy of your existing form in case it goes bad (per last item in my signature).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Micron, I have tried your suggestion, but I think It might not be possible in my case. Here It is (an example, please read explanation below):

    Code:
    SELECT Table2.Names, Table2.Surnames, Table2.Description FROM Table2 LEFT JOIN JoinTable ON Table2.[IDx] = JoinTable.[IDx] AND JoinTable.[IDy]= [Forms]![MyForm].[Form]![IDy] WHERE (((JoinTable.IDx) Is Null));
    Explanation:

    I have 3 tables - Table1, Table2 and JoinTable. Upper SELECT statement without "AND JoinTable.[IDy]= [Forms]![MyForm].[Form]![IDy]" finds all unmatched records, but that's not what I need. My form is based on recordsource of JoinTable. So, when I'm in one of the records, Combobox should exclude records from Table2 that allready exists in JoinTable under that record. This is what It is in Jointable:

    Code:
    JoinTable:
    
    IDx (FK)
    IDy (FK)
    In other words, Combobox should have rowsource that would exclude Table2 records that exists under same "IDy". That's where my "AND JoinTable.[IDy]= [Forms]![MyForm].[Form]![IDy]" ended up in SELECT statement, that actually represents IDy from another opened form that should match with JoinTable IDy. But, unfortunally this doesn't work. When I add this as Combobox rowsource, I get empty list,,,and weirdly form also doesn't show any "allready-entered" records from before.

    Do you have any suggestions on how to change this SELECT ?

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Use the query design view to construct a query, not edit it as I suspect you have done. You can't stick an AND operator in the middle of a join clause like that. If it is to work using the criteria portion you have posted, it has to be in the WHERE clause. Maybe
    Code:
    SELECT Table2.Names, Table2.Surnames, Table2.Description FROM Table2 LEFT JOIN JoinTable ON Table2.[IDx] = JoinTable.[IDx] WHERE (((JoinTable.IDx) Is Null AND JoinTable.[IDy]= [Forms]![MyForm].[Form]![IDy]));
    However, I'm not sure that's what you're trying to say. You write about 3 tables, but I only see 2 in your select statement - I'm easily confused
    Suggest you post small snippets of data. I find the easiest way is to create small Excel data ranges and copy/paste them here. This will automatically create a table structure which will hold the layout and formatting. Here's an example:

    December November October
    $4,833.34 $4,833.34 $4,833.34
    $122.50 $122.50 $122.50
    Make A1 the name of your table and provide as many rows/columns as you need. Do this for each required table. Restate the goal please, and I'll do what I can to figure out what you need based on the data you provide.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    I have solved It. I created a compound unique key of both FK and PK of JoinTable as suggested, and added this code in Before_update event

    Code:
    Dim Duplicate As Long
    
    
    If Me.Dirty = True Then
       
      
       If Not IsNull(Combo5) Then
                                    
                   Duplicate = Nz(DLookup("ID_Table2", "Table2", "ID_Table1 = " & Me.ID_Table1 & " AND Field1= '" & Me.Field1 & "'" & _
                        " AND ID_Table2 <> " & Nz(Me.ID_Table2, 0)), 0)
    But I'm still struggling with micron's solution, can't figure that one out. For now I'll close this thread, but If I figure out how Combobox doesn't show selected Items anymore, I'll post that too.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 11-22-2015, 10:21 AM
  2. Replies: 26
    Last Post: 08-19-2015, 01:42 PM
  3. Adding records to a join table using a query
    By brharrii in forum Queries
    Replies: 5
    Last Post: 04-12-2013, 11:08 AM
  4. Cannot add Records; Join Key of table not...
    By turntabl1st in forum Access
    Replies: 1
    Last Post: 07-18-2012, 06:51 AM
  5. Replies: 1
    Last Post: 09-20-2011, 03:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums