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

    Unanswered: How to prevent Intersection Table from opening when Duplication happens

    Hello,

    I need to update a piece of code which gives a warning message if duplicate records are selected.

    The senario is I have many Beneficiaries who can be members of many Groups. So I have a many-to-many relationship, and I have created an intersection table called tblLinkBeneficariesGroups to allow for the many-to-many relationships to work.

    On my form called MainFrm I have two listboxes, one for Beneficiaries (lstBeneficiaries), one for Groups (lstGroups). I select a Beneficiary (beneficiaryID PK field), and then select a Group (groupID PK field) and click a command button to add the selected primary key ID's into the intersection table. When the on-click event of the button is executed the form called "view_beneficiary_groups_frm" loads and displays the groups which the selected beneficiary is a member of.

    Now a beneficiary cannot be a member of a group twice - as this is duplication, so, I need to alter my code so a message appears before the form opens warning that the record cannot be saved because the Beneficiary is already a member of the selected group -
    Code:
    If DCount("beneficiaryID", "tblLinkBeneficiaryGroups", "beneficiaryID = " & Me.lst_beneficiaries.Value) = 0 Then
      
        stDocName = "view_beneficiary_groups_frm"
        stLinkCriteria = "[beneficiaryid]=" & Me!lst_beneficiaries.Column(0)
          
        DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
    
        DoCmd.GoToRecord , , acNewRec
          
        Forms![view_beneficiary_groups_frm]![beneficiaryID] = Forms!main_frm.lst_beneficiaries.Column(0)
        Forms![view_beneficiary_groups_frm]![groupID] = Forms!main_frm.lst_link_groups.Column(0)
    
    Else
    MsgBox "Duplicate Warning! The selected beneficiary is already a member of the selected group!"
    Exit Sub
    End If
    Last edited by moss2076; 04-23-10 at 13:52.

  2. #2
    Join Date
    Aug 2004
    Posts
    364
    Any thoughts?

    This is allowed -

    Beneficiary 1 Group 5
    Beneficiary 1 Group 6

    This is not allowed -

    Beneficiary 1 Group 6
    Beneficiary 1 Group 6

    I need to update my code to give a message which will catch the duplication in the Group field of tbLinkBeneficiaryGroups

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Or make the beneficiary and group the primary key
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    They already are the primary keys

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    All I want is for the code to stop the form opening if a duplicate group (groupID) is assigned to a beneficiary (beneficiaryID).

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can easily check the existence of a record with the same values as the one you want to create:
    Code:
    RecordExist = Nz(DLookup("groupID", "tblLinkBeneficiaryGroups", "groupID = " & groupID & " And beneficiaryID = " & beneficiaryID), 0) <> 0
    Have a nice day!

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by Sinndho View Post
    You can easily check the existence of a record with the same values as the one you want to create:
    Code:
    RecordExist = Nz(DLookup("groupID", "tblLinkBeneficiaryGroups", "groupID = " & groupID & " And beneficiaryID = " & beneficiaryID), 0) <> 0
    I am unsure how to incporporate it / alter the code I have already to make this work, im sorry my vb skills arent up to much!

  8. #8
    Join Date
    Aug 2004
    Posts
    364
    I think I may have cracked it with this code -
    Code:
     Dim stDocName As String
        Dim stLinkCriteria As String
      
    Dim RecordExist As Integer
      
     If RecordExist = Nz(DLookup("beneficiaryID", "tblLinkBeneficiaryGroups", "beneficiaryID = " & Me.lst_beneficiaries.Value & " And groupID = " & lst_link_groups), 0) <> 0 Then
          
        stDocName = "view_beneficiary_groups_frm"
         stLinkCriteria = "[beneficiaryid]=" & Me!lst_beneficiaries.Column(0)
          
        DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
        DoCmd.GoToRecord , , acNewRec
          
        Forms![view_beneficiary_groups_frm]![beneficiaryID] = Forms!main_frm.lst_beneficiaries.Column(0)
        Forms![view_beneficiary_groups_frm]![groupID] = Forms!main_frm.lst_link_groups.Column(0)
    
    Else
    MsgBox "Duplicate Warning! The selected beneficiary is already a member of the selected group!"
    Exit Sub
    End If
    Could you let me know what you think of the code as my vb knowledge skills are pretty much non existant!

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Remove RecordExist:
    Code:
    If Nz(DLookup("beneficiaryID", "tblLinkBeneficiaryGroups", "beneficiaryID = " & Me.lst_beneficiaries.Value & " And groupID = " & lst_link_groups), 0) = 0 Then
    If a record with the values returned by beneficiaryID and lst_link_groups already exist, DLookup will return the value of the column beneficiaryID of the matching row in the table. If such a record does not exist, DLookup will return Null which will be converted to zero by the Nz function.

    If the whole expression returns zero, it means that the record does not exist and it's OK to create one. I used RecordExist as a placeholder, and you do not need one in a conditional expression (i.e., an expression beginning with If).
    Have a nice day!

  10. #10
    Join Date
    Aug 2004
    Posts
    364
    Excellent, that is sorted! Once again many thanks!

Posting Permissions

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