Results 1 to 6 of 6
  1. #1
    Mick99 is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    58

    STOP entry of duplication's by checking 2 fields in a table first

    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

    Click image for larger version. 

Name:	Screen Shot 2.png 
Views:	25 
Size:	38.5 KB 
ID:	30031Click image for larger version. 

Name:	Screen Shot 1.png 
Views:	25 
Size:	33.3 KB 
ID:	30032

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Shouldn't duplicate participant name parts or group name in tbl_Group_Participants, just save ID values into number fields - Group_ID and Resource_ID. If a resource will always have the same role, there is no need to save that either.

    Instead of opening and manipulating a recordset object, could just do a DLookup() or DCount() and an INSERT SQL action. Something Like:
    Code:
    If DCount("*", "tbl_Group_Participants", "Group_ID=" & Me.Combo_Group & " AND Resource_ID = " & Me.List_Resources) > 0 Then
        MsgBox "Resource already exists in this group"
    Else
        CurrentDb.Execute "INSERT INTO tbl_Group_Participants(Group_ID, Resource_ID) VALUES(" & Me.Combo_Group & ", " & Me.List_Resources & ")"
    End If
    Alternatively, set Resource_ID and Group_ID as compound index No Duplicates. Then wouldn't even have to bother with the lookup, just run the INSERT and duplicates will be rejected.

    Please post code within CODE tags so it retains indentation and readability.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Mick99 is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    58
    Hi June7, that works a treat thank you very much. I took your advice and am using the ID values instead. On the formatting side is there a general rule on how the code should step out and back in again. I am self taught and would like to respect your request.

    Cheers Mick

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You mean the indentation? Just so it's readable. Each nested structure should be another indent. I always line up the initial line of a structure with its companion exit line and lines within the structure are an additional indent. VBA editor will assist.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Mick99 is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    58
    Click image for larger version. 

Name:	Screen Shot 1.png 
Views:	11 
Size:	104.1 KB 
ID:	30154Click image for larger version. 

Name:	Screen Shot 2.png 
Views:	11 
Size:	95.7 KB 
ID:	30155Hi June7, your first solution works great however i am trying to use this with dates. I can not have a group and date duplicated in the table. I can get the code to work if i remove the the date picker and the table field MR_Date_String from the DCount. I have been trying for a quite a while but can not get this to work. Any help would be appreciated. I have added the code and screen shots.

    Private Sub Command15_Click()
    Dim RSdetail As DAO.Recordset
    Dim RSUdetail As DAO.Recordset
    Dim DateValue As String
    Dim DatepickerValue As String


    Set RSdetail = CurrentDb.OpenRecordset("Select * FROM [tbl_Meeting_Register] ORDER BY MR_KEY_ID ASC")
    DateValue = Format(MR_Meeting_Date, "dd/mm/yy")
    DatepickerValue = Format(Me.DTPicker7, "dd/mm/yy")
    On Error GoTo ADDFIRST:
    If Not RSdetail.BOF = False Then GoTo ADDFIRST:
    If DCount("*", "tbl_Meeting_Register", "MR_Group_ID=" & Me.Cmb_Group_Name.Value & " AND MR_Date_String =" & DatepickerValue) > 0 Then
    MsgBox "Item already exists in this group", , "Duplication Check"
    Else
    On Error Resume Next
    ADDFIRST:
    RSdetail.AddNew
    RSdetail(1) = Me.Cmb_Group_Name
    RSdetail(2) = Me.Cmb_Group_Name.Column(1)
    RSdetail(3) = Me.DTPicker7
    RSdetail(4) = Me.Cmb_Meeting_Location
    RSdetail(5) = Me.Cmb_Meeting_Location.Column(1)
    RSdetail(6) = Me.TxtTime
    RSdetail(8) = DatepickerValue
    RSdetail.Update
    End If
    End Sub

  6. #6
    Mick99 is offline Advanced Beginner
    Windows 7 32bit Access 2013
    Join Date
    Jul 2015
    Posts
    58
    Hi June7, I found the solution for this so it's problem solved.

    Dim Answer As Variant

    Answer = DLookup("[MR_Date_String]", "tbl_Meeting_Register", "[MR_Date_String] = '" & DatepickerValue & "'" & " And MR_Group_ID=" & Me.Cmb_Group_Name.Value)
    If Not IsNull(Answer) Then

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

Similar Threads

  1. Replies: 6
    Last Post: 05-26-2017, 01:00 PM
  2. Replies: 2
    Last Post: 01-04-2017, 08:24 AM
  3. Stop Duplicate name entry
    By bharatdubole in forum Access
    Replies: 1
    Last Post: 12-03-2012, 06:11 AM
  4. Stop duplication of appointment
    By cheyanne in forum Forms
    Replies: 48
    Last Post: 06-06-2012, 06:59 AM
  5. Error Checking my Entry Result
    By AKQTS in forum Programming
    Replies: 3
    Last Post: 04-21-2011, 07:46 AM

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