Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check Duplicated Data (2003)

    Hi,

    I have the codes below to check if there are any duplicate data in one filed.

    Private Sub cmdRequestor_BeforeUpdate(Cancel As Integer)
    Dim SID As String
    Dim stLinkCriteria As String

    SID = Me.ID.Value
    stLinkCriteria = "[ID]=" & "'" & SID & "'"

    If DCount("ID", "tbl_ID", stLinkCriteria) > 0 Then
    'Undo duplicate entry
    'Me.Undo
    'Message box warning of duplication
    MsgBox "Warning ID" _
    & SID & " has already been entered." _
    & vbCr & vbCr & "Please recheck the ID#.", vbInformation _
    , "Duplicate Information"
    End If
    End Sub

    Now I would like to check if there are duplicated data at all three fields. How should I add the codes above? Those three fields are in one table.

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Duplicated Data (2003)

    Do you want to check that the value of each of the three fields hasn't been entered before, or that this particular combination of values hasn't been entered before?
    Also: what are the names of the fields, and what is their data type? (Text, number, date/time, ...)

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Duplicated Data (2003)

    I would like to check this particular combination of values hasn't been entered before. Those three fields are Name(Text), PayDate(Date), Receiver(Text).

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Duplicated Data (2003)

    Try this:
    <code>
    Dim stLinkCriteria As String
    stLinkCriteria = "[Name]=" & Chr(34) & Me![Name] & Chr(34) & _
    " AND [PayDate]=#" & Format(Me![PayDate], "mm/dd/yyyy") & "#" & _
    " AND [Receiver]=" & Chr(34) & Me![Receiver] & Chr(34)
    If DCount("*", "NameOfTable", stLinkCriteria) > 0 Then
    'Undo duplicate entry
    'Me.Undo
    'Message box warning of duplication
    MsgBox "Warning: this combination of values has already been entered." & _
    vbCr & vbCr & "Please recheck the ID#.", vbInformation, "Duplicate Information"
    End If
    </code>
    Replace NameOfTable with the name of the table (or query) that acts as record source for the form.

    BTW, Name is an unfortunate choice for a field name, since Name is also a property of a form and many other objects.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Duplicated Data (2003)

    Thank you so much, Hans. The codes works well.

    But I do have a issue regarding the field "Receiver." The Receiver field is combo box and name is "cmdReceiver." The Control Source of cmdReceiver is "Receiver" field of the table.

    I don't should I put the "cmdReceiver" or "Receiver" in the codes below:

    AND [????]=" & Chr(34) & Me![????] & Chr(34)

    Regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Duplicated Data (2003)

    Use

    ... AND [Receiver]=" & Chr(34) & Me![cmdReceiver] & Chr(34)

    The first name (inside the quotes) is the name of the field, and the second one is the name of the control.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Duplicated Data (2003)

    Yes, it's works fine now. Hans.

    But how can I force the user to contiune enter the data. Now, the error message pops up if there a duplicated data. But I still can press enter key by pass the error message. The data will be saved.

    Regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Duplicated Data (2003)

    If you don't want the record to be saved, place the code in the Before Update event of the form as a whole. The line Cancel = True will cancel saving the record.

  9. #9
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Duplicated Data (2003)

    Thanks, Hans.

    I add the line The line Cancel = True, then I will get another message "You can't go to the specified record."

    Please see the attached database.

    Thanks

    Regards
    Attached Files Attached Files

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Duplicated Data (2003)

    1) The PayDate field is not a date/time field but a text field. You should change it to a date/time field, for the user can now enter anything in the PayDate field, for example "Never" or "82/83/84". If it is a date/time field, Access will only allow valid dates to be entered. The code I posted assumed that PayDate was a date/time field, it doesn't work correctly for a text field.

    2) You should probably set the Limit to List property of the cmdReceiver combo box to Yes - at the moment the user can enter anything, for example "Woody Leonhard".

    3) You must check which error occurs in the cmdAddRecord_Click event procedure, and ignore the "You can't go to the specified record" error because you already show a message:

    Private Sub cmdAddRecord_Click()
    On Error GoTo Err_cmdAddRecord_Click
    DoCmd.GoToRecord , , acNewRec

    Err_cmdAddRecord_Click:
    ' Ignore error caused by Before Update
    If Not Err = 2105 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  11. #11
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Duplicated Data (2003)

    Thanks, Hans.

    If I click the close button and I get another error message "You can't save this record at this time......"

    Should I also ignore this error message or should I put custom message?

    Thanks

    Regards

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Duplicated Data (2003)

    You could try to save the record before closing the form, and cancel if an error occurred. Or you could display a custom message.

  13. #13
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Duplicated Data (2003)

    Thanks, Hans.

    How can I custom the message if I would like to?

    Regards

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Duplicated Data (2003)

    See <post:=688,881>post 688,881</post:> for an example of how you can find out the error numbers.

  15. #15
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Duplicated Data (2003)

    Thanks, Hans.

Page 1 of 2 12 LastLast

Posting Permissions

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