Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2011
    Posts
    4

    Unanswered: Duplicate records display and allow user to enter the same once again

    Dear All,

    I am trying to design data entry form in ms access.. my criteria is entered Emp Code is already exist in that table (Main Table)... it has to display user message to the user that the record is already exit in database with Yes or No oprtions.. if user select yes it has to display existing records. if user select No option it will allow to continue to fill the remaining records..

    below mentioned code will show if there is any duplicate value in a table and not allowing to enter duplicate values.

    Private Sub Emp_Code_BeforeUpdate(Cancel As Integer)

    '---------------------------------------------------
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Dim intResponse As Integer

    Set rsc = Me.RecordsetClone


    SID = Me.[Emp Code].Value
    stLinkCriteria = "[Emp Code]=" & "'" & SID & "'"

    'Check StudentDetails table for duplicate StudentNumber
    If DCount("[Emp Code]", "[Main Table]", stLinkCriteria) > 0 Then
    'Undo duplicate entry
    Me.Undo
    'Message box warning of duplication
    'MsgBox ("HI")
    MsgBox "Warning Student Number " _
    & SID & " has already been entered." _
    & vbCr & vbCr & "You will now been taken to the record.", vbYesNo _
    , "Duplicate Information"

    rsc.FindFirst stLinkCriteria
    Me.Bookmark = rsc.Bookmark

    'Go to record of original Student Number


    Set rsc = Nothing


    End If

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    And your question is?
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    4
    I want to display how many duplicate records exist in the table and it needs to display message with yes or no option. if user selects yes option it has to display existing records or if user selects no option form allow to user enter duplicate record.

    and i have not created any primary key in Main Table.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For counting the number of rows having a duplicate value into a field:
    Code:
    Private Sub Txt_UserID_BeforeUpdate(Cancel As Integer)
    
        Dim lngDuplicates As Long
        
        lngDuplicates = DCount("User_ID", "Tbl_Users", "User_ID=" & Me.Txt_UserID.Value)
        If lngDuplicates > 0 Then MsgBox "There are " & lngDuplicates & " rows with the User ID: " & Me.Txt_UserID.Value
    
    End Sub
    For displaying the first record with the matching value (all records remain available):
    Code:
    Private Sub Txt_UserID_BeforeUpdate(Cancel As Integer)
    
        Dim lngDuplicates As Long
        Dim rst As DAO.Recordset
    
        lngDuplicates = DCount("User_ID", "Tbl_Users", "User_ID=" & Nz(Me.Txt_UserID.Value, 0))
        If lngDuplicates > 0 Then
            If MsgBox("There are " & lngDuplicates & " records with the User ID: " & Me.Txt_UserID.Value & vbNewLine & _
                       "Do you want to display the first of these records?", vbQuestion + vbYesNo, "Value already exists") = vbYes Then
                Set rst = Me.RecordsetClone
                rst.FindFirst "User_ID=" & Me.Txt_UserID.Value
                Me.Bookmark = rst.Bookmark
                rst.Close
                Set rst = Nothing
            Else
                Cancel = True
            End If
        End If
    
    End Sub
    If you create a primary key on the field you want to test, there cannot be any duplicate values. With a primary key defined on it, the value of a field must be unique.
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    4
    Thanks for your help..

    For every record message is showing total records exist in data base.
    Attached access db.

    My requirement is if there is any duplicate value.. display message value exist and if choose yes it has to show existing records or i choose no option form allow to enter duplicate record.

    And there is no primary key defined in the table.

    Please help..
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you try the code I posted? If yes what problem did you face with it?
    Have a nice day!

  7. #7
    Join Date
    Jul 2011
    Posts
    4
    Yes, i tried but it is displaying message "Total no. of records exist in database"
    i have attached the same code with form also.

    Please help.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mendusuresh View Post
    Yes, i tried but it is displaying message "Total no. of records exist in database"
    A message box cannot display a text that's totally different from what is provided as argument. If the first parameter of the MsgBox function is "There are (x) rows with the User ID: (y)" the message cannot be "Total no. of records exist in database".

    As for the database you joined, it's a 2007/2010 version (accdb) while I only have a 2003 version of Access available at the moment.
    Have a nice day!

Posting Permissions

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