Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Preventing Duplicate Entries in combobox subform

    Hi,

    The form I have is about a Treaty, and on this Treaty form, there is a subform that allows the user to choose multiple contacts for the Treaty. I don't want the user to be able to choose the same contact more than once for a particular Treaty.

    I have tried creating a unique index with the TreatyID and the ContactID in my subform table, but when I put in a duplicate contact, I can not then erase that contact and act as if it were never put in at all without receiving the message "Primary key can not be null"

    So then I tried using this code in the beforeUpdate event of the combobox.

    i = DCount("JunctionID", "JunctionContactTreaty", "TreatyID=" & Me.[TreatyID] & "AND ContactID=" & Me.[ContactID])
    If (i > 0) Then
    MsgBox "Cannot enter same contact twice", vbOKOnly + vbExclamation, "Duplicate"
    Me.Undo
    End If

    However, this does not solve my problem either.

    What I want to happen is that if the user chooses the same contact twice, an error message pops up and then that combo box is cleared so the user can just click off of it and move on if they so choose--or they can pick a different contact.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Give the following a try. Note I didn't know the name of your ComboBox so I just used that. the thing you missed was setting Cancel to True in the IF statement.

    Code:
    Sum ComboBox_Before_Update(Cancel as Integer)
    
      i = DCount("JunctionID", "JunctionContactTreaty", "TreatyID=" & Me.[TreatyID] & "AND _
           ContactID=" & Me.[ContactID])
      If (i > 0) Then
        MsgBox "Cannot enter same contact twice", vbOKOnly + vbExclamation, "Duplicate"
        Cancel = True
      End If
    
    End Sub
    P.S. I'm assuming your DCOUNT statement works.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Didn't work :(

    Sadly that didn't work.

    I want to be able to leave the combo box blank after the error message pops up. But when I erase what is inside of the combo box and click off of it to fill out other parts of the form, this message appears

    Run-time error '3075':
    Syntax error (missing operator) in quert expression 'TreatyID=1 AND ContactID='

    Something is not letting me leave the Contact combo box blank after the error message shows.

    See, my subform is continuous, and the combo box only allows 4 options at the moment. However, if the user were to use all 4 options, they could still go onto the 5th option but then this error would keep popping up as there are no other choices to choose from and I am trying to prevent that.

  4. #4
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Also, I think my DCount statement works...I get the Msg box that says "Cannot enter same contact twice" when I run the code...

  5. #5
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Ah, figured it out by using a DoCmd.RunCommand acCmdUndo satement.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    If you're clearing the ComboBox you need to add a test for isnull(Me.[ContactID]) or Me.[ContactID]="" not sure which. That way when you click off the combobox and it tries to run the BeforeUpdate event again it tests to see if ContactID is blank and then exits {via Cancel=True} before attempting to use the blank value.

    Code:
    Sum ComboBox_Before_Update(Cancel as Integer)
    
      If not Me.ContactID = "" Then
        i = DCount("JunctionID", "JunctionContactTreaty", "TreatyID=" & Me.[TreatyID] & "AND _
             ContactID=" & Me.[ContactID])
        If (i > 0) Then
          MsgBox "Cannot enter same contact twice", vbOKOnly + vbExclamation, "Duplicate"
          Cancel = True
        End If
      Else
         Cancel = True   'Combo box was cleared
      End if
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Lounger
    Join Date
    Jun 2011
    Posts
    25
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Thank You!

    Thanks so much for your help! It makes much more sense to do that as I was placing it in the AfterUpdate event to make it work!

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Your Welcome.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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