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

    Duplicate Information (2003)

    Hi,

    I am current using the following code to validate HIC filed if have duplicate information:

    Private Sub HIC_BeforeUpdate(Cancel As Integer)
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

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

    'Check StudentDetails table for duplicate StudentNumber
    If DCount("HIC", "tbl_Data", _
    stLinkCriteria) > 0 Then
    'Undo duplicate entry
    Me.Undo
    'Message box warning of duplication
    MsgBox "Warning Student Number " _
    & SID & " has already been entered." _
    & vbCr & vbCr & "You will now been taken to the record.", _
    vbInformation, "Duplicate Information"
    'Go to record of original Student Number
    rsc.FindFirst stLinkCriteria
    Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
    End Sub

    Now I would like to have code to provent duplicated information on both fileds (HIC & Code). If HIC or Code have duplicated information, then it's fine. However if both fields have the duplicated information, message will pops up to prevent it happens. Since code field are next to HIC field. I know I should put code in before Update of Code field. But I am don't know how to write the code to provent if there are duplicated information on both fields.

    Please help.

    Thanks

    Regards

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

    Re: Duplicate Information (2003)

    in a Windows program, you cannot count on the user entering data in a fixed order. So if you want to check on the combination of 2 fields, I would do so in the Before Update event of the form, not that of the indivisual controls.

    You can change stLinkCriteria to include two conditions:
    <code>
    stLinkCriteria = "[HIC]='" & SID & "' AND [Code]='" & Code & "'"
    </code>
    This assumes that Code is a text field, just like SID.

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

    Re: Duplicate Information (2003)

    Thanks, Hans.

    But how should I add Code text field in DCount code:

    If DCount("HIC", "tbl_RACData", stLinkCriteria) > 0 Then

    Thanks

    Regards

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

    Re: Duplicate Information (2003)

    The Code field is handled in stLinkCriteria, that is sufficient. You could even use
    <code>
    If DCount("*", "tbl_RACData", stLinkCriteria) > 0 Then
    </code>
    since you're interested in the number of records.

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

    Re: Duplicate Information (2003)

    Hi Hans,

    I got the Run-time error '2001'; You canceled the previous operation.

    What's the best way to handle it since using stLinkCriteria is sufficient?

    Thanks

    Regards

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Duplicate Information (2003)

    What is the command that produces the error.

    Why dont you include a zipped/compacted version of you database so we may see what the problem is.

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

    Re: Duplicate Information (2003)

    hi,

    Please see the attached database.

    Thank you for all of your help.

    Regards

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Duplicate Information (2003)

    For a start it says that tbl RAC_Data does not exist, and that is correct. There is a table tbl Data

    I have corrected your database for you. Look at the code behind the form.

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

    Re: Duplicate Information (2003)

    Thank you so much, Pat.

    It works.

Posting Permissions

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