Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: How to stop duplicate values from being inserted into a table

    Hello,

    Im using the code below with a multi-select listbox to add a field (userID) from tblUsers into tblLinkUserVisits.

    The code works fine.

    But, I need to stop the code from inserting if a matching userID already exists in tblLinkUserVisits. So if I select userID 1 from the listbox I get message informing me that that userID is already in tblLinkUserVisits

    Code:
     Dim strSQL        As String
      Dim db            As DAO.Database
      Dim rs            As DAO.Recordset
      Dim ctl           As Control
      Dim varItem       As Variant
      Set db = CurrentDb()
      Set rs = db.OpenRecordset("tblLinkUserVisits", dbOpenDynaset, dbAppendOnly)
      'make sure a selection has been made
      If Forms![visits_frm]!List21.ItemsSelected.Count = 0 Then
        MsgBox "Must select at least 1 user!"
        Exit Sub
      End If
    
      'add selected value(s) to table
      Set ctl = Forms![visits_frm]!List21
      For Each varItem In ctl.ItemsSelected
        rs.AddNew
       
         rs!userID = ctl.ItemData(varItem)
         rs!visitID = Forms![visits_frm]![visitID]
         rs.Update
      Next varItem

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Make it an index no duplicates or a PK.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Hello, my appologies, I neglected to mention that when a new record is inserted into tblLinkUserVisits there is a PK field called visitID and a field called linkUserID.

    For example I can currently have -

    visitID number 001 with
    linkUserID 111 with userID's usr005
    linkUserID 112 with userID usr005.

    I need to catch and stop this as usr005 cannot be part of the same visitID twice.

    The table linlUserVisits is a link table between tblUsers and tblVisits to allow for many-to-many relationships as many users can be booked into many visits.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't understand the structure of your m:m table. Can you post a screen shot of it in design view?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Sure, and here is a shot of the ERD. Everything works fine with regards to the database adding data to the tables, I just need it to not allow duplicate userID's under the same visitID.
    Attached Thumbnails Attached Thumbnails 1.jpg   2.jpg  

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I don't understand why LinkID shows up as a PK in the relationship diagram but not the table design view. However:
    The column LinkID is totally redundant and should be removed.
    The correct primary key for that table is a composite key of (userID, visitID).
    In fact the perfect model for what this should be like is already there for tblLinkUserGroups.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    Yup, you've got it, it was staring right at me! Ive removed the linkID field now, and everything seems fine

    Thank you very much!!

  8. #8
    Join Date
    Nov 2008
    Posts
    24

    Check for duplicates

    Hi I use a function to check for duplicates in a table:
    This takes the values and give a true if there is a record with the required value or False if there is no duplicate.
    usage:
    if check_for_duplicate("[TBL_accidents]", "Reportername LIKE '" & Me!Reportername & "'" & _
    " AND ContactType = " & Me!ContactType & _
    " AND DateNotified = #" & DateValue(Me!DateNotified) & "#" & _
    " AND DateOccurred = #" & DateValue(Me!DateOccurred) & "#") = false then
    ' continue
    else
    ' process duplicate values
    end if
    ------------------------------------------------------
    Public Function check_for_duplicate(theTableName As String, SQLstring As String) As Boolean
    On Error GoTo err_trap
    Dim lngCount As Long
    check_for_duplicate = False
    If IsNothing(theTableName) Then Exit Function
    If IsNothing(SQLstring) Then Exit Function 'Count how many times this record appears
    lngCount = Nz(DCount("*", theTableName, SQLstring), 0)
    'if lngCount is 0, no duplicates else there is
    If lngCount <> 0 Then check_for_duplicate = True

    final_exit:
    Exit Function

    err_trap:
    If err.Number <> 0 Then msgbox("error: " & err.number & vbcrlf & err.description)
    GoTo final_exit
    Exit Function

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •