Advice needed on where to put code that stops duplicate entries

[Pacman52]

Hi all I'm really lost on this one and just can not workout where I'm going wrong as in my mind what I've tried should work logically but that is not the case although it most likely my inexperience with coding thats letting me down again. So after hours of trying to sort it out myself I've finally decided to ask for some help and advice please.

So I have a userform with a textbox call 'textboxEventCode'. This textbox is disabled when the userform is opened as its content is automatically generated once a user completes two other textboxes: 'textboxEventName & textboxEventStartDate' with the following code. All of this works ok.

Private Sub textboxEventStartDate_Change()

    Dim textboxEventName_Exit As String
    Dim textboxEventStartDate As Date

    textboxEventCode.Value = Trim(Me.textboxEventStartDate.Value) & " - " & textboxEventName.Value
    textboxEventName.Value = Application.WorksheetFunction.Proper(textboxEventName.Value)

End Sub

with the 'textboxEventStartDate' I have the following events that also work fine.

Private Sub textBoxEventStartDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    If Not IsDate(textboxEventStartDate) Then
        MsgBox "It looks like you've either entered a text value or a blank space, Please only enter a date in the correct format e.g. 'DD/MM/YYYY'", vbExclamation, "Invalid Entry"
        Cancel = True
        textboxEventStartDate.SetFocus
        textboxEventStartDate = ""
        textboxEventStartDate.BackColor = RGB(204, 255, 255)
    
    Else
    
        textboxEventStartDate = Format(CDate(textboxEventStartDate), "DD/MM/YYYY")
    
    End If

End Sub

Private Sub TextBoxEventStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

        If textboxEventStartDate.Text = "" Then
        Cancel = 1
        MsgBox "You must enter the events start date.", vbExclamation, "Date Required"
        textboxEventStartDate.SetFocus
        textboxEventStartDate.BackColor = RGB(204, 255, 255)
        
    Else
        
        textboxEventStartDate.BackColor = RGB(255, 255, 255)
    
    End If
    
End Sub

Lastly, I have some code that stops a user entering a duplicate event.

Dim a As Long

    a = Application.WorksheetFunction.CountIf(shTestData.Range("B:B"), _
    Me.textboxEventCode.Text)
    If a >= 1 Then
    Me.textboxEventCode = ""
    MsgBox "You already have a record of an event with the same name starting on the same date. Please select it from the Main Events List.", vbInformation, "Duplicate Entry Detected"


End If

So my main question is where can I put this event so that is triggers as soon as the 'textboxEventCode' is populated?

logically I thought it should work in the exit event, which it did to a degree by showing the msgbox to say the event already exists but when I clicked ok on that it also showed the other msgbox in the event.

Hopefully that makes sense and hopefully someone will be kind enough to tell me what I'm doing wrong and how to sort it.

Many thanks Paul

 

[offthelip]

Looking at the code you have shown us , it looks like "textboxEventCode" is the concatenation of textboxEventStartDate & textboxEventName as shown in the Sub textboxEventStartDate_Change(). So you should be checking for duplication BEFORE you create the textboxeventcode. i.e. everywhere Name or startdate are created or changed you should be checking against your testdata range BB to see if they exist ( using the Match function would work) This allows you to warn the user before they complete the Name or start date

 

[Pacman52]

Hi Offthelip, thanks so much for your reply.

The Userform where the eventcode is generated is one of 3 Userforms that can be completed by a user if they need to or if they have actually got the information required to complete them. As such the only place where the EventName and Startdate are entered is on Userform1 hence this is where I put the concatenation of textboxEventStartDate & textboxEventName to get the EventCode.

If the user wants to complete Userform2 then the EventCode is carried over onto that form and subsequently Userform3, if the user opts to complete that. The only reason the EventCode is shown on Userfrom 2 and 3 is just a 'visual check' so the user knows its the same event that they are entering information on. They can not complete or access Userform 2 or 3 seprately .

So this is why in my mind logically I wanted to check for duplicates on Userform1 as that form is the only place a user would enter the EventName and Startdate (these are the first two textboxes the go to when opening the form).

I tried using different event options with the duplicate code but the only one I could get close to it working was by using the Exit event

I thought by putting the duplicate code in the Exit event would be the best place as once the users fills in the EventName and Startdate then exits the StartDate textbox the full date would be completed and thus so would the EventCode so the duplicates code would have something to check against.

So opting to use the Exit event along with the code to check for any text I tried 'ElseIf' or simply 'Else' as well as but both of these options didn't work properley in that the message box saying there was already an event appeared twice i.e it checks for duplicates shows the msgbox, I click ok and then the same msgbox shows again - If I could sort this out I'd be happy as its basically the only thing I need to do to finish the project.

As mentioned my level of knowledge on VBA is very basic although I'm keen to learn and understand more I always try to work out problems myself if I can through reading posts on this forum and the usual Google searches but they are not always easy to understand sadley.

 

[DanteAmor]

'textboxEventCode'. This textbox is disabled when the userform is opened as its content is automatically generated once a user completes two other textboxes: 'textboxEventName & textboxEventStartDate'

Then you must validate the 2 textboxes. I suggest you perform validations in the exit event of each textbox. Replace all your code with the following:

Option Explicit             'At the beginning of all the code
Dim comingout As Boolean

'START DATE events
Private Sub textboxEventStartDate_Change()
  textboxEventName.Value = Application.WorksheetFunction.Proper(textboxEventName.Value)
  textboxEventCode.Value = Trim(textboxEventStartDate.Value) & " - " & textboxEventName.Value
End Sub

Private Sub TextBoxEventStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Dim n As Long
  Dim msg As String
  
  If comingout Then Exit Sub
  comingout = False
  
  With textboxEventStartDate
    If .Text = "" Then
      msg = "You must enter the events start date."
    ElseIf Not IsDate(.Text) Then
      msg = "Please only enter a date in the correct format e.g. 'DD/MM/YYYY'"
    Else
      textboxEventCode.Value = Trim(.Value) & " - " & textboxEventName.Value
      
      n = WorksheetFunction.CountIf(shTestData.Range("B:B"), textboxEventCode.Text)
      If n >= 1 Then
        msg = "You already have a record of an event with the same name starting on the same date. Please select it from the Main Events List."
      Else
        .BackColor = RGB(255, 255, 255)
      End If
    End If
    
    If msg <> "" Then
      Cancel = True
      .SetFocus
      .BackColor = RGB(204, 255, 255)
      MsgBox msg, vbInformation
    End If
  End With
End Sub

'EVENT NAME events
Private Sub textboxEventName_Change()
  textboxEventName.Value = Application.WorksheetFunction.Proper(textboxEventName.Value)
  textboxEventCode.Value = Trim(textboxEventStartDate.Value) & " - " & textboxEventName.Value
End Sub

Private Sub textboxEventName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  Dim n As Long
  Dim msg As String
  
  If comingout Then Exit Sub
  comingout = False
  
  With textboxEventName
    If .Text = "" Then
      msg = "You must enter the events Name."
    Else
      textboxEventCode.Value = Trim(textboxEventStartDate.Value) & " - " & .Value
      
      n = WorksheetFunction.CountIf(shTestData.Range("B:B"), textboxEventCode.Text)
      If n >= 1 Then
        msg = "You already have a record of an event with the same name starting on the same date. Please select it from the Main Events List."
      Else
        .BackColor = RGB(255, 255, 255)
      End If
    End If
    
    If msg <> "" Then
      Cancel = True
      .SetFocus
      .BackColor = RGB(204, 255, 255)
      MsgBox msg, vbInformation
    End If
  End With
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  comingout = True
End Sub

Try and comment

 

[Pacman52]

Wow thank you so much for this - to write that would have taken me hours ! lol

I'll try it all when I get home this evening and let you know if any problems but I'm sure it will work thanks again Paul

 

[Pacman52]

Hi DanteAmor, just to say your revised code works perfectly so thank you so much for replying to the post. Just to add I must be going forward in my learning of VBC as I could actually understand some your code does !

thanks again Paul

​