Duplicate Text Values

[Tor_Fey]

Good Morning All;

I have a form (frm_check_table) bound to a table (tbl_check); and on this form I have 3 bound fields (check_name1, check_name2 and check_name3) and one unbound combobox (called: checktype) which takes it values from tbl_check_type.

What I need to do is select the data from the unbound combobox and populate the field called: check_name1; but if this field contains a value to move to the field:check_name2 etc.

I need to prevent the fields from being overwritten and if the user selects a value that has been previously used in any field; then to disallow this and show a message box stating the text value has already been entered; then setting the focus to an empty field.


Your help on this would greatly be appreciated.


Regards
Tor Fey

 

[Isskint]

Hi Tor

I would suggest coding in the AfterUpdate event of the combobox checktype. You can easily check each check_name field for a value and check the new name is not in the other fields.

 

[Tor_Fey]

Hi Isskint;

Thanks for the reply, unfortunately; I don't know what the code is to carry out this kind of validation check, are you able to help with this?

Regards
Tor Fey

Hi Tor

I would suggest coding in the AfterUpdate event of the combobox checktype. You can easily check each check_name field for a value and check the new name is not in the other fields.

 

[Isskint]

The following code is unchecked, but should provide what you are looking for. I have coded this making the following assumptions;
1)check_Name fields are filled sequentially
2) checktype bound column is = to a chaeckname

Private Sub checktype_AfterUpdate()

Dim intCheckName As Integer

intCheckName = 0

If Nz(Me.check_Name1, 0) = 0 Then 'apply selected name to check_Name1
    intCheckName = 1
ElseIf Nz(Me.check_Name2, 0) = 0 Then 'apply selected name to check_Name2
    intCheckName = 2
ElseIf Nz(Me.check_Name3, 0) = 0 Then 'apply selected name to check_Name3
    intCheckName = 3
Else 'all 3 check_Names contain a value
    MsgBox "All check names selected", vbInformation
    Exit Sub
End If

Select Case intCheckName 'check preceding check_Name fields do not equal current name
    Case 1
        Me.check_Name1 = Me.checktype
    Case 2
        If Me.checktype = Me.check_Name1 Then 'matches Me.check_Name1
            MsgBox "The name " & Me.checktype & " has already been submitted", vbInformation
            Exit Sub
        End If
        Me.check_Name2 = Me.checktype
    Case 3
        If Me.checktype = Me.check_Name1 Or Me.checktype = Me.check_Name2 Then
            MsgBox "The name " & Me.checktype & " has already been submitted", vbInformation
            Exit Sub
        End If
        Me.check_Name3 = Me.checktype
End Select

End Sub

 

[Tor_Fey]

Hi Isskint;

This works perfectly and is exactly what I am looking for.

Thanks so much for your help on this, it is very much appreciated

Kind Regards
Tor Fey

The following code is unchecked, but should provide what you are looking for. I have coded this making the following assumptions;
1)check_Name fields are filled sequentially
2) checktype bound column is = to a chaeckname

Private Sub checktype_AfterUpdate()

Dim intCheckName As Integer

intCheckName = 0

If Nz(Me.check_Name1, 0) = 0 Then 'apply selected name to check_Name1
    intCheckName = 1
ElseIf Nz(Me.check_Name2, 0) = 0 Then 'apply selected name to check_Name2
    intCheckName = 2
ElseIf Nz(Me.check_Name3, 0) = 0 Then 'apply selected name to check_Name3
    intCheckName = 3
Else 'all 3 check_Names contain a value
    MsgBox "All check names selected", vbInformation
    Exit Sub
End If

Select Case intCheckName 'check preceding check_Name fields do not equal current name
    Case 1
        Me.check_Name1 = Me.checktype
    Case 2
        If Me.checktype = Me.check_Name1 Then 'matches Me.check_Name1
            MsgBox "The name " & Me.checktype & " has already been submitted", vbInformation
            Exit Sub
        End If
        Me.check_Name2 = Me.checktype
    Case 3
        If Me.checktype = Me.check_Name1 Or Me.checktype = Me.check_Name2 Then
            MsgBox "The name " & Me.checktype & " has already been submitted", vbInformation
            Exit Sub
        End If
        Me.check_Name3 = Me.checktype
End Select

End Sub