Checking Multiple Text Boxes for Unique Values

[AggieLJ]

Howdy All,

I am working on a form that can have UP TO ten unbound text boxes with unique values inputted by the worker. How can I make sure each entry is unique (within the group shown on the form), while disregarding Nulls, before ultimately writing them to a table? I thought there might be some way to loop through them but I haven't been able to make it work like I envisioned...

Thanks for the thought!

 

[Galaxiom]

Post the code you have tried and we may be able to help you sort it.

It can be useful to write the procedure out conceptually in comments first and then add the code to perform each step.

I would use a function or genericly written sub so it could be applied to each box. You are likely to have terms like Screen.ActiveControl in there somewhere.

Using the AfterUpdate Event on each control you will need to test the value of the control against the others in the group and display a message if it is the same and preventing any further action other than changing that value.

You would need to force the focus back to the contol being edited if it had moved away. You can store the control the user moved to and set the focus there at the end.

Your loop will also need to:
choose the controls to be tested against using a naming pattern or tag;
avoid a control testing against itself

 

[AggieLJ]

I had originally thought I could define some string to output the control name I want, but i guess not...

Private Sub cmdContinue_Click()

    Dim n As Integer
    Dim x As Integer
    Dim Control As String
    Dim Check As String
    
    n = 0
    
    Do While DCount("idrPaymentNumber", "qryPaymentList", "[intInvoiceID] = " & Me.txtHiddenInvoiceID) > n
        Control = "Me.cboOver" & n + 1
        x = n - 1
        Do While x > 0
            Check = "Me.cboOver" & x
            If Control = Check Then
                MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this continuing."
                Exit Sub
            End If
            x = x - 1
        Loop
        n = n + 1
    Loop
    
    ' If everything checks out, SQL insert into statement here
 
End Sub

I have a command button to add another option to it, and it checks all the options, but there HAS to be an easier way...

Private Sub cmdSplitBill_Click()


    'First Click
    If IsNull(Me.cboOver1) = False Then
        If Me.cboOver2.Visible = False Then
            Me.cboOver2.Visible = True
            Me.txtAmount2.Visible = True
            Me.cmdSplitBill.Top = Me.cboOver2.Top
            Exit Sub
        End If
    Else
        MsgBox "You haven't filled out anything yet. Please fill out the first over-payee" _
             & " and then add more if needed."
        Exit Sub
    End If
    
    'Second Click
    If Me.cboOver3.Visible = False Then
        If Me.cboOver2 <> Me.cboOver1 Then
            If DCount("idrPaymentNumber", "qryPaymentList", "[intInvoiceID] = " & Me.txtHiddenInvoiceID) < 3 Then
                MsgBox "There were only two payees for this invoice. There are no more people " _
                     & "to choose from."
                Exit Sub
            End If
            Me.cboOver3.Visible = True
            Me.txtAmount3.Visible = True
            Me.cmdSplitBill.Top = Me.cboOver3.Top
            Exit Sub
        Else
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before adding another payee."
            Exit Sub
        End If
    End If

    'Third Click
    If Me.cboOver4.Visible = False Then
        If Me.cboOver3 <> Me.cboOver1 And Me.cboOver3 <> Me.cboOver2 Then
            If DCount("idrPaymentNumber", "qryPaymentList", "[intInvoiceID] = " & Me.txtHiddenInvoiceID) < 4 Then
                MsgBox "There were only three payees for this invoice. There are " _
                     & "no more people to choose from."
                Exit Sub
            End If
            Me.cboOver4.Visible = True
            Me.txtAmount4.Visible = True
            Me.cmdSplitBill.Top = Me.cboOver4.Top
            Exit Sub
        Else
            MsgBox "There are multiple refunds allocated for the same person. " _
            & "Please fix this before adding another payee."
            Exit Sub
        End If
    End If

    'Fourth Click
    If Me.cboOver5.Visible = False Then
        If Me.cboOver4 <> Me.cboOver1 And Me.cboOver4 <> Me.cboOver2 _
        And Me.cboOver4 <> Me.cboOver3 Then
            If DCount("idrPaymentNumber", "qryPaymentList", "" _
               & "[intInvoiceID] = " & Me.txtHiddenInvoiceID) < 5 Then
                MsgBox "There were only four payees for this invoice. There are " _
                     & "no more people to choose from."
                Exit Sub
            End If
            Me.cboOver5.Visible = True
            Me.txtAmount5.Visible = True
            Me.cmdSplitBill.Top = Me.cboOver5.Top
            Exit Sub
        Else
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before adding another payee."
            Exit Sub
        End If
    End If

    'Fifth Click
    If Me.cboOver6.Visible = False Then
        If Me.cboOver5 <> Me.cboOver1 And Me.cboOver5 <> Me.cboOver2 _
        And Me.cboOver5 <> Me.cboOver3 And Me.cboOver5 <> Me.cboOver4 Then
            If DCount("idrPaymentNumber", "qryPaymentList", "[intInvoiceID] = " & Me.txtHiddenInvoiceID) < 6 Then
                MsgBox "There were only five payees for this invoice. There are no more people " _
                     & "to choose from."
                Exit Sub
            End If
            Me.cboOver6.Visible = True
            Me.txtAmount6.Visible = True
            Me.cmdSplitBill.Top = Me.cboOver6.Top
            Exit Sub
        Else
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before adding another payee."
            Exit Sub
        End If
    End If

    
    'Sixth Click
    If Me.cboOver7.Visible = False Then
        If Me.cboOver6 <> Me.cboOver1 And Me.cboOver6 <> Me.cboOver2 _
        And Me.cboOver6 <> Me.cboOver3 And Me.cboOver6 <> Me.cboOver4 _
        And Me.cboOver6 <> Me.cboOver5 Then
            If DCount("idrPaymentNumber", "qryPaymentList", "[intInvoiceID] = " & Me.txtHiddenInvoiceID) < 7 Then
                MsgBox "There were only six payees for this invoice. There are no more people " _
                     & "to choose from."
                Exit Sub
            End If
            Me.cboOver7.Visible = True
            Me.txtAmount7.Visible = True
            Me.cmdSplitBill.Top = Me.cboOver7.Top
            Exit Sub
        Else
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before adding another payee."
            Exit Sub
        End If
    End If

    
    
    'Seventh Click
    If Me.cboOver8.Visible = False Then
        If Me.cboOver7 <> Me.cboOver1 And Me.cboOver7 <> Me.cboOver2 _
        And Me.cboOver7 <> Me.cboOver3 And Me.cboOver7 <> Me.cboOver4 _
        And Me.cboOver7 <> Me.cboOver5 And Me.cboOver7 <> Me.cboOver6 Then
            If DCount("idrPaymentNumber", "qryPaymentList", "[intInvoiceID] = " & Me.txtHiddenInvoiceID) < 8 Then
                MsgBox "There were only seven payees for this invoice. There are no more people " _
                     & "to choose from."
                Exit Sub
            End If
            Me.cboOver8.Visible = True
            Me.txtAmount8.Visible = True
            Me.cmdSplitBill.Top = Me.cboOver8.Top
            Exit Sub
        Else
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before adding another payee."
            Exit Sub
        End If
    End If

    
    'Eighth Click
    If Me.cboOver9.Visible = False Then
        If Me.cboOver8 <> Me.cboOver1 And Me.cboOver8 <> Me.cboOver2 _
        And Me.cboOver8 <> Me.cboOver3 And Me.cboOver8 <> Me.cboOver4 _
        And Me.cboOver8 <> Me.cboOver5 And Me.cboOver8 <> Me.cboOver6 _
        And Me.cboOver8 <> Me.cboOver7 Then
            If DCount("idrPaymentNumber", "qryPaymentList", "[intInvoiceID] = " & Me.txtHiddenInvoiceID) < 9 Then
                MsgBox "There were only eight payees for this invoice. There are no more people " _
                     & "to choose from."
                Exit Sub
            End If
            Me.cboOver9.Visible = True
            Me.txtAmount9.Visible = True
            Me.cmdSplitBill.Top = Me.cboOver9.Top
            Exit Sub
        Else
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before adding another payee."
            Exit Sub
        End If
    End If

    'Nineth Click
    If Me.cboOver10.Visible = False Then
        If Me.cboOver9 <> Me.cboOver1 And Me.cboOver9 <> Me.cboOver2 _
        And Me.cboOver9 <> Me.cboOver3 And Me.cboOver9 <> Me.cboOver4 _
        And Me.cboOver9 <> Me.cboOver5 And Me.cboOver9 <> Me.cboOver6 _
        And Me.cboOver9 <> Me.cboOver7 And Me.cboOver9 <> Me.cboOver8 Then
            If DCount("idrPaymentNumber", "qryPaymentList", "[intInvoiceID] = " & Me.txtHiddenInvoiceID) < 10 Then
                MsgBox "There were only nine payees for this invoice. There are no more people " _
                     & "to choose from."
                Exit Sub
            End If
            Me.cboOver10.Visible = True
            Me.txtAmount10.Visible = True
            Me.cmdSplitBill.Top = Me.cboOver10.Top
            Exit Sub
        Else
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before adding another payee."
            Exit Sub
        End If
    'Tenth Click
        MsgBox "Can't split overpayment any more"
        Exit Sub
    End If

End Sub

I have added images of the before and after pictures of the OnUpdate event, just in case...

 

[AggieLJ]

I figured out a way to not only check for duplicates, but to also find the nulls (in case the user deletes an entry after adding it) and make the form move all the entries to the first open box on the form.

Here it is...

Private Sub cmdContinue_Click()

    Dim Records As Integer
    Dim Count As Integer

    Records = DCount("idrPaymentNumber", "qryPaymentList", "[intInvoiceID] = " & Me.txtHiddenInvoiceID)
    Count = 0
    
    Do Until Count = Records + 1
    If Records > 1 Then
        If Me.cboOver2 = Me.cboOver1 Then
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before continuing."
            Exit Sub
        End If
        If IsNull(Me.cboOver1) Or Me.cboOver1 = "" Then
            Me.cboOver1 = Me.cboOver2
            Me.txtAmount1 = Me.txtAmount2
            Me.cboOver2 = ""
            Me.txtAmount2 = ""
        End If
    End If
    If Records > 2 Then
        If Me.cboOver3 = Me.cboOver1 Or Me.cboOver3 = Me.cboOver2 Then
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before continuing."
            Exit Sub
        End If
        If IsNull(Me.cboOver2) Or Me.cboOver2 = "" Then
            Me.cboOver2 = Me.cboOver3
            Me.txtAmount2 = Me.txtAmount3
            Me.cboOver3 = ""
            Me.txtAmount3 = ""
        End If
    End If
    If Records > 3 Then
        If Me.cboOver4 = Me.cboOver1 Or Me.cboOver4 = Me.cboOver2 _
        Or Me.cboOver4 = Me.cboOver3 Then
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before continuing."
            Exit Sub
        End If
        If IsNull(Me.cboOver3) Or Me.cboOver3 = "" Then
            Me.cboOver3 = Me.cboOver4
            Me.txtAmount3 = Me.txtAmount4
            Me.cboOver4 = ""
            Me.txtAmount4 = ""
        End If
    End If
    If Records > 4 Then
        If Me.cboOver5 = Me.cboOver1 Or Me.cboOver5 = Me.cboOver2 _
        Or Me.cboOver5 = Me.cboOver3 Or Me.cboOver5 = Me.cboOver4 Then
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before continuing."
            Exit Sub
        End If
        If IsNull(Me.cboOver4) Or Me.cboOver4 = "" Then
            Me.cboOver4 = Me.cboOver5
            Me.txtAmount4 = Me.txtAmount5
            Me.cboOver5 = ""
            Me.txtAmount5 = ""
        End If
    End If
    If Records > 5 Then
        If Me.cboOver6 = Me.cboOver1 Or Me.cboOver6 = Me.cboOver2 _
        Or Me.cboOver6 = Me.cboOver3 Or Me.cboOver6 = Me.cboOver4 _
        Or Me.cboOver6 = Me.cboOver5 Then
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before continuing."
            Exit Sub
        End If
        If IsNull(Me.cboOver5) Or Me.cboOver5 = "" Then
            Me.cboOver5 = Me.cboOver6
            Me.txtAmount5 = Me.txtAmount6
            Me.cboOver6 = ""
            Me.txtAmount6 = ""
        End If
    End If
    If Records > 6 Then
        If Me.cboOver7 = Me.cboOver1 Or Me.cboOver7 = Me.cboOver2 _
        Or Me.cboOver7 = Me.cboOver3 Or Me.cboOver7 = Me.cboOver4 _
        Or Me.cboOver7 = Me.cboOver5 Or Me.cboOver7 = Me.cboOver6 Then
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before continuing."
            Exit Sub
        End If
        If IsNull(Me.cboOver6) Or Me.cboOver6 = "" Then
            Me.cboOver6 = Me.cboOver7
            Me.txtAmount6 = Me.txtAmount7
            Me.cboOver7 = ""
            Me.txtAmount7 = ""
        End If
    End If
    If Records > 7 Then
        If Me.cboOver8 = Me.cboOver1 Or Me.cboOver8 = Me.cboOver2 _
        Or Me.cboOver8 = Me.cboOver3 Or Me.cboOver8 = Me.cboOver4 _
        Or Me.cboOver8 = Me.cboOver5 Or Me.cboOver8 = Me.cboOver6 _
        Or Me.cboOver8 = Me.cboOver7 Then
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before continuing."
            Exit Sub
        End If
        If IsNull(Me.cboOver7) Or Me.cboOver7 = "" Then
            Me.cboOver7 = Me.cboOver8
            Me.txtAmount7 = Me.txtAmount8
            Me.cboOver8 = ""
            Me.txtAmount8 = ""
        End If
    End If
    If Records > 8 Then
        If Me.cboOver9 = Me.cboOver1 Or Me.cboOver9 = Me.cboOver2 _
        Or Me.cboOver9 = Me.cboOver3 Or Me.cboOver9 = Me.cboOver4 _
        Or Me.cboOver9 = Me.cboOver5 Or Me.cboOver9 = Me.cboOver6 _
        Or Me.cboOver9 = Me.cboOver7 Or Me.cboOver9 = Me.cboOver8 Then
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before continuing."
            Exit Sub
        End If
        If IsNull(Me.cboOver8) Or Me.cboOver8 = "" Then
            Me.cboOver8 = Me.cboOver9
            Me.txtAmount8 = Me.txtAmount9
            Me.cboOver9 = ""
            Me.txtAmount9 = ""
        End If
    End If
    If Records > 9 Then
        If Me.cboOver10 = Me.cboOver1 Or Me.cboOver10 = Me.cboOver2 _
        Or Me.cboOver10 = Me.cboOver3 Or Me.cboOver10 = Me.cboOver4 _
        Or Me.cboOver10 = Me.cboOver5 Or Me.cboOver10 = Me.cboOver6 _
        Or Me.cboOver10 = Me.cboOver7 Or Me.cboOver10 = Me.cboOver8 _
        Or Me.cboOver10 = Me.cboOver9 Then
            MsgBox "There are multiple refunds allocated for the same person. " _
                 & "Please fix this before continuing."
            Exit Sub
        End If
        If IsNull(Me.cboOver10) Or Me.cboOver10 = "" Then
            Me.cboOver10 = Me.cboOver9
            Me.txtAmount10 = Me.txtAmount9
            Me.cboOver9 = ""
            Me.txtAmount9 = ""
        End If
    End If
    Count = Count + 1
    Loop
 
End Sub

If there is anyone who knows a more efficient way, I am all ears.