none
prevent duplicate data entry only On a specific date RRS feed

  • Question

  • hi:

    i know how to do a prevent duplicate data entry 

    my question is if i want to prevent duplicate data only in specific time. let's say 7 days and after 7 days the prevent duplicate data

    will not Apply 

    (little explanation) i don't want that 2 people will report the same failure twice in the time i have to fix it

    so i want them to see a message " this  failure has been already reported"

    Sunday, August 23, 2015 9:56 PM

Answers

  • Hi eshay1,

    I don't recommend that delete the last record  and then recreate it since the delete is not necessary at all. To prevent the duplicate data on a specific date, we can just check it before the form updating.

    I also build a demo to illustrate it.

    1. Create table1 with two fields, field1(short text), field2(date)

    2. Create a single form for table1

    3. Create the an textbox change event to get the text for field1

    Private Sub Field1_Change()
    Field1Value = Me.Field1.Text
    End Sub

    4. Create before update event for the form to check whether the record is duplicate and prevent it saved if it is duplicate

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim sqlQuery As String
    
    sqlQuery = "select count(1) from table1 where " & "field1='" & Field1Value & "' and field2>#" & DateAdd("d", "-7", Date) & "#"
    
    If CurrentDb.OpenRecordset(sqlQuery).Fields(0) > 0 Then
     MsgBox "The problem already reported before!"
      Cancel = -1
    Else
    
    End If
    
    End Sub

    Here is the full code for the form1:
    Option Compare Database
    
    Dim Field1Value As String
    
    Private Sub Field1_Change()
    Field1Value = Me.Field1.Text
    End Sub
    
    
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim sqlQuery As String
    
    sqlQuery = "select count(1) from table1 where " & "field1='" & Field1Value & "' and field2>=#" & DateAdd("d", "-7", Date) & "#"
    
    If CurrentDb.OpenRecordset(sqlQuery).Fields(0) > 0 Then
     MsgBox "The problem already reported before!"
      Cancel = -1
    Else
    
    End If
    
    End Sub
    
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by eshay1 Tuesday, September 1, 2015 9:33 PM
    Monday, August 31, 2015 7:06 AM
    Moderator

All replies

  • Show us your table design. What would you consider duplicate? Free text like a bug report synopsis rarely is.

    -Tom. Microsoft Access MVP

    Sunday, August 23, 2015 10:02 PM
  • Show us your table design. What would you consider duplicate? Free text like a bug report synopsis rarely is.

    -Tom. Microsoft Access MVP

    i can't show you my table design because is not in english

    but i'll explain. i have 2 fields one called sublocation and the second call description   

    let's say i have a room called   24 and there 5 people living in this room i want to prevent that the all 5 will report the same failure (broken door Etc.What is happening today. for now i'm using Duplicates Query 

      
    Sunday, August 23, 2015 10:18 PM
  • So what if person 1 reports "broken door", person 2 reports "door is broken", and number three some other variant. How would Access know it's really the same issue?

    -Tom. Microsoft Access MVP

    Sunday, August 23, 2015 11:27 PM
  • So what if person 1 reports "broken door", person 2 reports "door is broken", and number three some other variant. How would Access know it's really the same issue?

    -Tom. Microsoft Access MVP

    i build the db that you can only press a button that say "broken door" there is no text field by my job i know every milfaction that can be so i spend days to  to assign the name of the all malefaction to a buttons

    acutely i build an  CMMS software 

     
    Monday, August 24, 2015 12:04 AM
  • OK, maybe you can use the DCount function to find out if another one is already there. Something like (guessing at your field names):

    '(in form_beforeupdate event)
    'Check if already one within 7 days
    if DCount("*", "myTable", "DateReported > #" & DateAdd("d", -7, Date) & "# And MalefunctionID=" & Me.MalefunctionID) > 0 then
      Cancel=True
      Msgbox "We already have that one. Thanks.", vbInformation
    end if


    -Tom. Microsoft Access MVP

    Monday, August 24, 2015 12:45 AM
  • OK, maybe you can use the DCount function to find out if another one is already there. Something like (guessing at your field names):

    '(in form_beforeupdate event)
    'Check if already one within 7 days
    if DCount("*", "myTable", "DateReported > #" & DateAdd("d", -7, Date) & "# And MalefunctionID=" & Me.MalefunctionID) > 0 then
      Cancel=True
      Msgbox "We already have that one. Thanks.", vbInformation
    end if


    -Tom. Microsoft Access MVP

    nothing happened and it's not showing any bug can you write the code with my value.                                     field1=(sublocation)                                                                                                                                       field2=(description)
    Wednesday, August 26, 2015 3:38 PM
  • Hi eshay1,

    >>my question is if i want to prevent duplicate data only in specific time. let's say 7 days and after 7 days the prevent duplicate data<<

    Based on my understanding, we just need to query whether the duplicate data by adding the date filed to filter. Just like Tom suggested using Dcount to add an extra criteria. You can get more detail about DCount function from link below:
    https://support.office.com/en-in/article/DCount-Function-f6b5d78b-ad0b-4e42-be7a-11a64acbf3d3

    If the issue still exits, would you minding share with us the original code you prevent duplicate data?

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 27, 2015 8:16 AM
    Moderator
  • Hi eshay1,

    >>my question is if i want to prevent duplicate data only in specific time. let's say 7 days and after 7 days the prevent duplicate data<<


    the code tom suggest doesn't do nothing and does not show any bug. so i was thinking on another method   1.create query and restrict it to show only 7 days report                                                                           2.then apply some code to the query like 

    <if record is already in the query then go to last record delete" show msg "this already been reported thank you>

     how do i Approached this code 

    Thanks in advance


    Friday, August 28, 2015 12:01 PM
  • Hi eshay1,

    I don't recommend that delete the last record  and then recreate it since the delete is not necessary at all. To prevent the duplicate data on a specific date, we can just check it before the form updating.

    I also build a demo to illustrate it.

    1. Create table1 with two fields, field1(short text), field2(date)

    2. Create a single form for table1

    3. Create the an textbox change event to get the text for field1

    Private Sub Field1_Change()
    Field1Value = Me.Field1.Text
    End Sub

    4. Create before update event for the form to check whether the record is duplicate and prevent it saved if it is duplicate

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim sqlQuery As String
    
    sqlQuery = "select count(1) from table1 where " & "field1='" & Field1Value & "' and field2>#" & DateAdd("d", "-7", Date) & "#"
    
    If CurrentDb.OpenRecordset(sqlQuery).Fields(0) > 0 Then
     MsgBox "The problem already reported before!"
      Cancel = -1
    Else
    
    End If
    
    End Sub

    Here is the full code for the form1:
    Option Compare Database
    
    Dim Field1Value As String
    
    Private Sub Field1_Change()
    Field1Value = Me.Field1.Text
    End Sub
    
    
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim sqlQuery As String
    
    sqlQuery = "select count(1) from table1 where " & "field1='" & Field1Value & "' and field2>=#" & DateAdd("d", "-7", Date) & "#"
    
    If CurrentDb.OpenRecordset(sqlQuery).Fields(0) > 0 Then
     MsgBox "The problem already reported before!"
      Cancel = -1
    Else
    
    End If
    
    End Sub
    
    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by eshay1 Tuesday, September 1, 2015 9:33 PM
    Monday, August 31, 2015 7:06 AM
    Moderator
  • hi fei:

    tnx for your help i really appreciate your effort  

    work great

    regards eshai 

    Tuesday, September 1, 2015 9:38 PM