Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19

    Unanswered: Preventing Duplicate Entries? Using value of two fields???

    I'm trying to prevent duplicate records that have the same Date with the same Shift.

    The following entries are correct:

    Date: 23 Apr 2003 Shift: Day
    Date: 23 Apr 2003 Shift: Night

    There will always be two records for each date, but I want to make sure that they don't enter a duplicate Shift for that date, i.e.,

    Date: 23 Apr 2003 Shift: Day
    Date: 23 Apr 2003 Shift: Day


    Thank you,

  2. #2
    Join Date
    Apr 2003
    Location
    Santa Barbara, CA
    Posts
    67

    Re: Preventing Duplicate Entries? Using value of two fields???

    Originally posted by cdakjat
    I'm trying to prevent duplicate records that have the same Date with the same Shift.

    The following entries are correct:

    Date: 23 Apr 2003 Shift: Day
    Date: 23 Apr 2003 Shift: Night

    There will always be two records for each date, but I want to make sure that they don't enter a duplicate Shift for that date, i.e.,

    Date: 23 Apr 2003 Shift: Day
    Date: 23 Apr 2003 Shift: Day


    Thank you,

    if dCount("ID","Schedule","date = #" & DaDate & "# and shift = " & quotes & DaShift & quotes ) > 0 then
    msgbox "Shift is already Exists"
    exit sub
    endif

    When setting up the Table, you can also drag the cursor over both fields and click on the key command button. When a invalid entry is attemped it will post an error message unless you have setwarnings set to false.

  3. #3
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19
    Thanks Robt917 for your suggestion....

    I tried your suggestion at the Table level and it does prevent duplicates. I do like the idea of having a warning msg though, so I will try to make this DCount function work for me. I do have it working on one of the fields. This is what I got so far....

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If DCount("[Date]", "tblSchedule", "[Date]= '" & Me![Combo26] > 0) Then
    MsgBox "This is a duplicate date for the same shift."
    Me.Undo
    Cancel = True
    End If
    End Sub

    (I'm using a combo box to select the dates as they represent the start of a shift)

    The above code works good, but doesn't check the Shift.

    I'm am now trying to figure out how to include the check for the Shift...I'm trying your suggested DCount function and haven't gotten it to work just yet.....i know i'm close though.

    Thanks again...

  4. #4
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19

    Still stumped on this one....

    I keep getting a Syntax Error on this one.....Can anyone share with me the correct syntax for the above problem.

    Thanks you

  5. #5
    Join Date
    Dec 2002
    Location
    Alaska
    Posts
    19

    Got it!

    Got it! Was missing a couple quotation marks around the Shift field.

    Thanks All.....

Posting Permissions

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