Unanswered: How to stop duplicate values from being inserted into a table
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
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!"
'add selected value(s) to table
Set ctl = Forms![visits_frm]!List21
For Each varItem In ctl.ItemsSelected
rs!userID = ctl.ItemData(varItem)
rs!visitID = Forms![visits_frm]![visitID]
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.
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.
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
' process duplicate values
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
If err.Number <> 0 Then msgbox("error: " & err.number & vbcrlf & err.description)