Results 1 to 11 of 11
  1. #1
    Join Date
    May 2014
    Posts
    5

    Unanswered: Code for message to pop up on duplication of data

    I have been trying for 2 days to get the correct code so when someone trys to put in duplicate data on the form (the field is indexed Yes(No duplicates)) a message will pop up that it is a duplicate entry (before having entered into all the other fields) and will take the user to the record. I have looked at different coding and I know that it needs to be built into Event - before update but can't get the correct code.

    The field is a text field named Txt_NHSNumber, the column is NHS Number and the table is Patient data.

    I managed to get the message box to appear but it did so after whatever data I put in not just duplication of data.

    Please help before I pull my hair out!!

    Many thanks in advance

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Check first:
    Code:
    If DCount("*", "[Patient Data]", "[NHS Number] = " & Me.Txt_NHSNumber.Value) > 0 Then
        ' --> A row with that NHS Number already exists.
    Else
        ' New row.
    End If
    Note: If [NHS Number] is not numeric, use:
    Code:
    If DCount("*", "[Patient Data]", "[NHS Number] = '" & Me.Txt_NHSNumber.Value & "'") > 0 Then
    Have a nice day!

  3. #3
    Join Date
    May 2014
    Posts
    5

    Nothing happens

    Thanks Sinndho but nothing happens, it lets me fill in the other fields? I want it to stop after the first field is duplicated to save time filling in the other fields when it's a duplicate entry and for a message to appear why and if possible take the user to the record so they can edit it.

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use an unbound control for Txt_NHSNumber and unlock all the other controls:
    - If the record exists, move to this record then unlock.
    - If the record does not exist, create it, requery the form and move to the new record then unlock.
    Have a nice day!

  5. #5
    Join Date
    May 2014
    Posts
    5

    ?

    Not sure how to do this?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Remove the value in the ControlSource property of Text_NSHNumber, so that it becomes an unbound control. Leave all other data controls bound to their related columns.

    2. Paste the following code into the Form Module and check that the AfterUpdate event of Text_NSHNumber is actually linked to its event handler. Do the same for the Form_Open and Form_Current event.
    Code:
    Private m_ColControls As Collection
    
    Private Sub SetControlsLocked(ByVal State As Boolean)
    
        Dim ctl As Control
        
        For Each ctl In m_ColControls
            ctl.Locked = State
        Next ctl
    
    End Sub
    
    Private Function SetCurrentRecord(ByVal RowID As Long) As Boolean
    
        Dim rst As DAO.Recordset
        Dim strCriteria As String
    
        strCriteria = "[NSH Number] = " & RowID
        If DCount("*", "[Patient data]", strCriteria) > 0 Then
            Set rst = Me.RecordsetClone
            With rst
                .FindFirst strCriteria
                Me.Bookmark = .Bookmark
                .Close
            End With
            SetCurrentRecord = True
        End If
        Set rst = Nothing
    
    End Function
    
    Private Sub Form_Current()
    
        Me.Text_NSHNumber.Value = Me![NSH Number]
        SetControlsLocked True
        
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    
        Dim ctl As Control
        
        Set m_ColControls = New Collection
        For Each ctl In Me.Controls
            Select Case ctl.ControlType
                Case acCheckBox, acComboBox, acListBox, acOptionButton, acOptionGroup, acTextBox, acToggleButton
                    If ctl.ControlSource <> "" Then m_ColControls.Add ctl
            End Select
        Next ctl
        SetControlsLocked True
        
    End Sub
    
    Private Sub Text_NSHNumber_AfterUpdate()
    
        Const c_SQL As String = "INSERT INTO [Patient data] ( [NSH Number] ) VALUES ( @N );"
        
        Dim lngRowID As Long
        
        lngRowID = Me.Text_NSHNumber.Value
        If SetCurrentRecord(lngRowID) = False Then
            CurrentDb.Execute Replace(c_SQL, "@N", lngRowID)
            Me.Requery
            SetCurrentRecord lngRowID
        End If
        SetControlsLocked False
        
    End Sub
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    make it a manual procedure
    users enter the NHS number
    search
    if not found then show other columns for data entry
    if found display the data

    how you implement that (effectivley where yoiu put Sinndho's code) is up to you.

    do you do it as a command button
    do you do this as lost focus event on nhsnumber box
    do you open a sub form... its up to you
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2014
    Posts
    5

    Unhappy Thanks

    Thanks both,

    Sinndho - I'm getting an error message for - For Each ctl In m_ColControls,

    I've spent too much time on this already, I was just trying to make it easier for the staff to input but will have to give up, thanks for your help, I will stick to things I know how to do.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by SueConway View Post
    Sinndho - I'm getting an error message for - For Each ctl In m_ColControls
    If you don't provide information about the error (error number, description) it's quite hard to help you, although it's probably a detail: I tested the process and it works here so it should work for you too.
    Have a nice day!

  10. #10
    Join Date
    May 2014
    Posts
    5
    It came up with a run-time error '424' object required

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you declared the collection in the Declaration section of the module?
    Code:
    Private m_ColControls As Collection
    And did you instanciated it in the Form_Open event handler?
    Code:
    Set m_ColControls = New Collection
    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
  •