I have a form with 2 list boxes. The first list box called "List_Resources" which contains a list of Resources from a table called "tbl_Resources". This list allows adding a resource to the second table with a double click. The second list box is named "List_Selected_participants" and upon the after update of this list box the code runs to copy that record information into the second table called "tbl_Group_Participants from the record selected from the first list box. I also have a table called "tbl_Resource_Group" that allow which group someone can be assigned to. I have a combo box that allows for the selection of "Healthy" or "Un-Healthy". The problems is that the same person maybe add several times before my attempt to stop duplication actually works. A person is allowed to be in both groups but not duplicated in the same group.
So the criteria is the a person being can be added to many groups but can not be added more than once per group.
I tried this approach
Searching "tbl_Group_Participants" and looking at the field named "Resource_ID" and then the field called "Group_Name" and concatenating these together. I was then running an if statement saying that if "Resource_ID" and "Group_Name" already equaled from a search of the "tbl_Group_Participants" and fields "Resource_ID" and "Group_Name" than do not add the person else add new.
I have attached some screen shots and the code below if that help. Would appreciate any helps.
Cheers Mick
Private Sub List_Resources_DblClick(Cancel As Integer)
'This code allows for resources to be selected and added to the "List_Selected_Participants" list box
Dim RSdetail As DAO.Recordset
Dim RSUdetail As DAO.Recordset
DoCmd.SetWarnings False
Record_Number = List_Resources
Set RSdetail = CurrentDb.OpenRecordset("Select * FROM [tbl_Resources] ORDER BY Resource_Full_Name ASC")
RSdetail.FindNext "[Resource_ID] = " & Record_Number
Set RSUdetail = CurrentDb.OpenRecordset("Select * FROM [tbl_Group_Participants] ORDER BY Resource_Full_Name ASC")
On Error GoTo ADDFIRST:
RSUdetail.FindNext "[tbl_Resource_ID] = " & Record_Number
If Not RSUdetail.BOF = False Then GoTo ADDFIRST:
If RSUdetail(0) & RSUdetail(33) = RSdetail(0) & Me.[Cmb_Group_Name].Column(1) Then
MsgBox "Resource already exists in this group"
Exit Sub
End If
On Error Resume Next
ADDFIRST:
RSUdetail.AddNew
RSUdetail(0) = RSdetail(0)
RSUdetail(3) = RSdetail(3)
RSUdetail(4) = RSdetail(4)
RSUdetail(5) = RSdetail(5)
RSUdetail(33) = Cmb_Group_Name.Column(1)
RSUdetail.Update
Me.List_Resources.Requery
Me.List_Selected_Participants.Requery
End Sub