none
[HELP] how to prevent duplicate record in same month ms access RRS feed

Answers

  • Hi KORG.Pa3x,

    Try to refer example below.

    Below is the table.

    Below is the Form:

    Code:

    Private Sub dt_AfterUpdate()
    Me.month_no.Value = DatePart("m", Me.dt)
    End Sub
    
    
    
    Private Sub product_name_BeforeUpdate(Cancel As Integer)
    Dim NewProduct As String
    Dim stLinkCriteria As String
    
    NewProduct = Me.product_name.Value
    stLinkCriteria = "[product_name] = " & "'" & NewProduct & "' and [month_no]=" & Me.month_no.Value
    'Debug.Print stLinkCriteria
    If Me.product_name = DLookup("[product_name]", "pro_data", stLinkCriteria) Then
       MsgBox "This product, " & NewProduct & ", has already been entered in database for this month." _
       & vbCr & vbCr & "Please check product name again.", vbInformation, "Duplicate information"
       Me.Undo
    End If
    End Sub
    

    Output:

    Further, You can try to modify the code based on your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Salar Music Friday, April 27, 2018 3:55 PM
    Thursday, February 8, 2018 7:49 AM
    Moderator

All replies

  • What about creating an table index and set the Unique property to Yes?

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Wednesday, February 7, 2018 5:12 PM
  • You can use the Query Wizard to capture duplicate records in any table. Then use VBA code to do whatever if the count is >0:

    If DCount("*","Duplicate Records Query Name")>0 Then

    Do whatever or send a message

    End If

    Wednesday, February 7, 2018 7:21 PM
  • you've gotten 2 good replies from veteran developers.   I notice your question is for "in the same month"...  and so I assume this is going to fire at the time of data entry...   

    in which case you can build a query for the record in question with the month criteria - - save that - and then leverage the dcount method suggested of this saved query to check if there is already a record - - and if so trigger some sort of feedback to the user such as a message box.

    Wednesday, February 7, 2018 11:23 PM
  • Hi KORG.Pa3x,

    Try to refer example below.

    Below is the table.

    Below is the Form:

    Code:

    Private Sub dt_AfterUpdate()
    Me.month_no.Value = DatePart("m", Me.dt)
    End Sub
    
    
    
    Private Sub product_name_BeforeUpdate(Cancel As Integer)
    Dim NewProduct As String
    Dim stLinkCriteria As String
    
    NewProduct = Me.product_name.Value
    stLinkCriteria = "[product_name] = " & "'" & NewProduct & "' and [month_no]=" & Me.month_no.Value
    'Debug.Print stLinkCriteria
    If Me.product_name = DLookup("[product_name]", "pro_data", stLinkCriteria) Then
       MsgBox "This product, " & NewProduct & ", has already been entered in database for this month." _
       & vbCr & vbCr & "Please check product name again.", vbInformation, "Duplicate information"
       Me.Undo
    End If
    End Sub
    

    Output:

    Further, You can try to modify the code based on your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Salar Music Friday, April 27, 2018 3:55 PM
    Thursday, February 8, 2018 7:49 AM
    Moderator
  • Hi KORG.Pa3x,

    Is your issue solved?

    I find that you did not follow up this thread after posting the issue.

    If your issue is solved then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the solution given by the community members.

    If then also you have any further questions then let us know about it.

    We will try to provide further suggestions to solve the issue.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 13, 2018 9:13 AM
    Moderator