Results 1 to 4 of 4
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    SQL Insert Into if Record does not all read exist

    Code:
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO Person2AddressTbl (PersonID, AddressID) " & _
            "VALUES (" & CboPerson & ", " & AddressID & ")"
        DoCmd.SetWarnings True
    Hi all,


    I have a many - many table that I want to insert into by SQL Statement above Which does work, however, is there a way to make this not insert if PersonID and the AddressID all ready exist in the same record on the table? Case being this is on a onclick of the close button so when I hit close, it inserts into my Person2AddressTbl, but if the record is not new, then I don't want a duplicate if that makes sense?

  2. #2
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Clarification on this, if not a new record, then don't insert?

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could put a unique composite index on CboPerson , AddressID in your Person2AddressTbl and the database would reject duplicate records. You could check for error 3022 to process the error and carry on with other processing logic.

    Your code indicates that CboPerson and AddressID are text data type, is that true?
    Good luck with your project.

  4. #4
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Orange,
    Thanks and the CboPerson and AddressID are both text, Numbers of the ID's or PK's... I think I found a solution which I am testing now which is appearing to work just fine?

    Code:
        If Me.NewRecord Then
        
            DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO Person2AddressTbl (PersonID, AddressID) " & _
            "VALUES (" & CboPerson & ", " & AddressID & ")"
        DoCmd.SetWarnings True
        
        End If

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

Similar Threads

  1. Before Insert to check on if all ready exist?
    By d9pierce1 in forum Programming
    Replies: 5
    Last Post: 04-07-2019, 08:31 AM
  2. Replies: 5
    Last Post: 04-25-2017, 08:48 AM
  3. Replies: 1
    Last Post: 12-02-2016, 02:57 AM
  4. Replies: 15
    Last Post: 09-14-2015, 01:31 PM
  5. Replies: 4
    Last Post: 06-21-2012, 05:39 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