Have 4 fields but don't want repeat value each field have to be unique

have a formAA
with Fields  A,  Field B, Field C, Field D

each field have to be unique value
in mean there is an compare value to don't repeat the field value?

any help?
micky runningAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Sorry, No.  You have created what in relational database terms is called a repeating group.  It violates first normal form and neither Access nor any other relational database has "functions" to solve this problem.  Relational databases work on set theory and that means that functions look at rows NOT columns.  Spreadsheets work with matrix type data and so they have functions that work with either/both rows and columns.

The correct way to solve the problem is to create a separate table so that each value can be in a separate row.  That way you can define a unique index or primary key and the database engine itself will prevent duplicates.  You would not need to write code or use a function to do it yourself.  Do some reading on normalization and relational database design to help you understand what normalization is about.

The incorrect way to solve the problem is to write VBA code that compares each column to every other column to identify duplicates.
micky runningAuthor Commented:
PatHartman thanks good friend for you good point !
 
ok i understand you point!
but this dont resolve the issue  of repeat value
because doesnot matter if in diffent tables  the value can't repeat....

thing about an expession to compare the fields is they are equal
one of them don't  go!
PatHartmanCommented:
What you have is:

rec1, 1234, 4567, 1234, 7889

What you need is:
rec1, 1234
rec1, 4567
rec1, 1234 --- this would throw an error since it duplicates the first record

If you do it the wrong way, your only option is to write your own VBA code to compare field 1 to field 2 and then to field 3 and then to field 4.   Then you compare field 2 to 3 and 4, and finally 3 to 4.  I can't write the code for you.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

micky runningAuthor Commented:
PatHartman YES !  That will help me !
but have no idea!
Dale FyeOwner, Developing Solutions LLCCommented:
Micky,

If you are not going to create the 1-to-many table that Pat is recommending, and insist on having 4 separate fields, you could write your own function to check for duplicates.  It might look like:
Public Function IsDup(ParamArray SomeVal() As Variant) As Integer

    'Returns a zero if no duplicates are found
    'Otherwise, returns the item position which is the duplicate
    
    Dim intLoop1 As Integer
    Dim intLoop2 As Integer
    
    For intLoop1 = LBound(SomeVal) To UBound(SomeVal)
        For intLoop2 = LBound(SomeVal) To UBound(SomeVal)
            If intLoop1 <> intLoop2 Then
                If SomeVal(intLoop2) = SomeVal(intLoop1) Then
                    IsDup = intLoop2 + 1
                    Exit Function
                End If
            End If
        Next
    Next
    
End Function

Open in new window

You would then call this function in the BeforeUpdate event of your form, something like:

Private Sub Form_BeforeUpdate(Cancel as Integer)

    SELECT Case IsDup(me.Text1, me.Text2, me.Text3, me.Text4)
        Case 0
             'do nothing
        Case 1
             me.text1.setfocus
             Cancel = true
        Case 2
             me.text2.setfocus
             Cancel = true
        Case 3
             me.text3.setfocus
             Cancel = true
        Case 4
             me.text4.setfocus
             Cancel = true
     End Select

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
micky runningAuthor Commented:
Dale Fye (Access MVP)

your solution sound like it could be
be don't know how put in work

what is SomeVal?

if you can attach an little sample it could guide me !
Dale FyeOwner, Developing Solutions LLCCommented:
Micky,

The code I provided assumes that you are using a form to enter your data.

Don't know what version of Access you are using, but you might be able to create a data macro to run the code, but I don't have much experience with those.
micky runningAuthor Commented:
Dale Fye (Access MVP)

I have access 2013

Just copy and paste ?

and where it say txt1 i put the field name ?

where it say (someval) i leave like that ?
Dale FyeOwner, Developing Solutions LLCCommented:
Yes, leave SomeVal() alone;  it is an array of variant data type.  When you declare a function with a paramarray as the argument, it means that you can pass in multiple values, of any data type.  This could be numbers, text, dates, basically anything, and basically any number of values.

Yes, Text1, Text2,... refer to the controls on your form and should be changed to the appropriate control names.
micky runningAuthor Commented:
Dale Fye thanks very much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.