How to check for duplicates within one recordset or row

[kat17]

I would like to check for duplicate entries within one record (one row).
Here is a scaled down example of my table:

NAME GuessA GuessB GuessC
=======================================
Jill 1 2 3
Jack 2 8 6


Basically, each person must enter a number but cannot have the same number twice.
Also, each person can enter a number that has been previously entered by another user (eg. Both Jack and Jill have used the number 2)

What I need to do is create a warning to the user if they have used a number that they have already entered. (check for duplicates)

NOTE: The user can also edit and make changes to the numbers so I need to check for duplicates when creating an new entry as well as when editing an entry.

Can anybody help?

 

[JerryKlmns]

Here is my guess

Sub MyCheckDouplicateValue()

Dim EnteredValue As Double
Dim personName As String
Dim rst As ADODB.Recordset
Dim strSQL As String

EnteredValue = txtEnterValue.Value
personName = txtUserName.Value
strSQL = "SELECT [NAME] " & _ 
         "FROM YourTableName " & _
         "WHERE (((GuessA=" & EnteredValue & ") OR " & _
                 "(GuessB=" & EnteredValue & ") OR " & _
                 "(GuessC=" & EnteredValue & ")) AND " & _
                "([NAME]='" & personName & "'));"
Set rst = CurrentProject.Connection.Execute (strSQL)
If rst.BOF And rst.EOF Then 
  'New Value
Else
  MsgBox "Value already used"
End If
Set rst = Nothing
End Sub

But I would recomend about normalizing your table structure.

 

[Remou]

Another idea, if you are using a form:

Private Sub GuessA_BeforeUpdate(Cancel As Integer)
Dim strCheck
strCheck = CStr(Nz(Me.GuessB, "")) & CStr(Nz(Me.GuessC, "")) _
         & CStr(Nz(Me.GuessD, ""))
If InStr(strCheck, Me.GuessA.Value) > 0 Then
    MsgBox "Duplicate guess"
    Me.GuessA.Undo
    Cancel = True
End If
End Sub

Here are some relevant links for JerryKlmns suggestion:

http://r937.com/relational.html

283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

 

[JoeSmoe55]

I was also going to suggest like Remou that this be done before the update of the table because if the user has enter the same number in twice you would have to update or delete the record for as many times as they do so. Are they gauranteed to have three guesses?

 

[kat17]

Thanks all.
Unfortunately, I can't normalise because the database not mine! I've just been asked to help make it more user friendly.

I'm only a rookie at VBA so I'll ponder on your posts and do some research. Hopefully I'll get it working!