Prevent same data being input to multiple fields

I have 10 fields called ToolAppliedTo1, ToolAppliedTo2, ToolAppliedTo3, etc
I need it so that when a value (from another table) is selected in "ToolAppliedTo1", then it cannot be selected in the other 9 fields.

I've tried validation rules but cannot seem to get it to work.

Many thanks
Thomas ReidMaintenance TechnicianAsked:
Who is Participating?

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

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.

ste5anSenior DeveloperCommented:
Well, this looks like a table design issue. Normally you would have two columns ToolNumber and  ToolAppliedTo instead of your repeating columns. Then it is a simple unique constraint. Otherwise it is a pretty long check constraint.

Thus: What is your use-case? Can you post an example? What about your context?
John TsioumprisSoftware & Systems EngineerCommented:
If it was  a couple of comboboxes then it would easy to filter their rowsource by using the IN operator like
VALUEToInsert NOT IN (Combo1,Combo2)

Open in new window

but with 10 comboboxes you need to write some code to check all the comboboxes for their rowsources and filter out the values...something like the AfterUpdate Event of each combobox
Dim ctl as Control
For each ctl in Me.Detail.Controls()
    If ctl.ControlType = acComboBox then
     If they have a Value Gather The Values  that are entered
     End If
'*****  Again ******
For each ctl in Me.Detail.Controls()
    If ctl.ControlType = acComboBox then
        If they don't have a value Filter Out the Previous Values
        manipulate the RowSource
     End If

Open in new window

Olaf DoschkeSoftware DeveloperCommented:
From the perspective of the user interface you either need combo boxes adapting their list items to remove already picked ones, or use a single list to pick from and populate 10 columns you fill up sequentially, or another list you fill from top to bottom. And indeed everything speaks for a table design having a single ToolAppliedTo 1:n related to the main table. Simply because from the perspective of database/table constraints a unique constraint is the best to implement on that level.

Bye, Olaf.
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Seconding ste5an's comment... the table design for something like this would typically involve multiple tables, defining a parent-child relationship.  

You haven't mentioned 'what' is being applied to the tools, so the following is just a guess for the purpose of showing a table design:

MaintProcessID: Autonumber/PrimaryKey
MaintProcessTitle:  Short Text
MaintProcessDescription: Long Text

ToolID:  AutoNumber/PK
ToolName:  Short Text
ToolDescription: Long Text
DateAcquired: Date/Time

tblToolMaintenance  (This table records which processes have been applied to which tools, and can be configured so that each process/tool combination is unique.)
ToolMaintenanceID: AutoNumber/PK
MaintProcessID:  Foreign Key (relates to tblMaintenanceProcesses)
ToolID:  Foreign Key (relates to tblTools)
Thomas ReidMaintenance TechnicianAuthor Commented:
ToolID: Short Text/PK (as each tool always has a different model name and number combination)
Section: Short Text
Model: Short Text

ModID: Short Text/PK (as each modification has a different modification name and number combination)
ModSummary: Long Text (used for user to type in a summary of the modification carried out)
Originator: Short Text (pulled from another table which has a list of engineers)
ToolAppliedTo1: ShortText (pulls ToolID from tblTooling)
DateCompleted1: Date (to input the date that the modification was completed to 'ToolAppliedTo1')
ToolAppliedTo2: ShortText (pulls ToolID from tblTooling)
DateCompleted2: Date (to input the date that the modification was completed to 'ToolAppliedTo2'
ToolAppliedTo#: ShortText (pulls ToolID from tblTooling)
DateCompleted1: Date (to input the date that the modification was completed to 'ToolAppliedTo#') etc up to 10

So each modification could be carried out on any tools
But I don't want users to be able to accidently select the same tool twice for a single modification.

Does this make sense?
Olaf DoschkeSoftware DeveloperCommented:
OK, before I start to normalize this (you had suggestions about that), let me notice - and please confirm, that I get this correct: The tools can be applied over a long period of time, many days, even months perhaps, and each edit of the data might only add one more tool applied?

Is that so?

Does your form for entering the next modification only adds one more step?

Bye, Olaf.
Dale FyeOwner, Developing Solutions LLCCommented:
you could create a function for the AfterUpdate event of each combo:  You would call the function like, doing this from the afterupdate event of each combo, and changing the parameter in the function call according to the control name:

AfterUpdate:  = IsDuplicate(Forms!Formname!cbo_ToolAppliedTo1)

The function, place this in the forms code module, IsDuplicate() would look something like (untested air code):
Private Function IsDuplicate(ctrl as control) as boolean

    Dim varValue as variant

    varValue = ctrl.Value

    for intLoop = 1 to 10
        if ctrl.Name = "cbo_ToolAppliedTo" & intLoop Then
            'ignore this item
       elseif ctrl.Value & "" = "" then
           'ignore null or blank values
       elseif ctrl.Value = me.controls("cbo_ToolAppliedTo" & intLoop).Value Then
            strmsg = "Invalid value, same as 'ToolAppliedTo" & intLoop & "'"
            msgbox strmsg, vbcritical + vbOkOnly
            ctrl.value = NULL
            Exit Function
       end if

End Function

Open in new window

This would check the value you just entered against each of the other values, and if it matches one of the other values, it would display a message, set the value of the just entered field to NULL, and set the focus back to that fields.

Obviously, you will need to change the names of the controls to match what you are using in your application.
Thomas ReidMaintenance TechnicianAuthor Commented:
Hi Olaf,

Tools are used for 5+ years so they never change, even when they are obsolete, the records and history is kept for each tool.
Modifications are done to the tools many, many times over their life cycle. A modification sheet is issued; this is the information that I am inputting in to the database. So we can track what tools that modification has been done to and what date it was done (for traceability purposes).

I have a form for adding a new modification, which adds a new record.
I have a form for editing an existing modification, where the user can input when another tool has had the modification done (ToolAppliedTo1-10) and the date that it was completed (DateCompleted1-10). All of this is stored within the table tblMods.

Dale I will give your suggestion a go and let you know how it goes!

Many thanks guys
Dale FyeOwner, Developing Solutions LLCCommented:
Still think you should get rid of fields 1-10 and create a new table (tbl_ModificationAppliesTo) with fields:


As I mentioned yesterday, this would give you the ability to apply this modification to more than 10 tools, whereas your current structure does not.  Then, instead of 20 fields (ToolAppliedTo1-10, DateCompleted1-10) you would simply have a subform, which could display as many records as you need.
Thomas ReidMaintenance TechnicianAuthor Commented:
Dale I couldn't get your suggestion to work, probably something I am doing wrong.

It's not critical that this has to be in the database, I just thought it would prevent users adding the same tool more than once to a modification.

Anyway, thanks for all your input guys.
Dale FyeOwner, Developing Solutions LLCCommented:
what does "I couldn't get your suggestion to work" mean?  Were you getting an error message?  Was the code not being called?

Post a screenshot of your form, in design view, preferably with the one of these controls selected, the properties dialog open, and the data tab visible.  Then copy the code you are using and post it back here.  We can get this to work.

Olaf DoschkeSoftware DeveloperCommented:
You still didn't answer: Does your form for entering the next modification only adds one more step?

I understand that you have up to 10 modifications per record. That should be reduced to 1, but before that's done you might ned to change a lot existing forms and code, so while it's less ideal I could accept that as given.

The reason I ask about the number of modification is about how the form has to be done with just one or 10 combo boxes and how they interact with each other and the data.

If you only add one step per edit session, then you'll have one combobox and populate it with all tools except the ones already used in columns 1-N used so far. That prevents double entry of the same tool.
If you have one form allowing to enter all 10 steps in one go, then you may disable all but one combobox and only populate the next with all tools not yet used.

Besides, if you allow more than 10 steps overall by adding new records, you would even more so better kill those multiple columns, have an extra table with ModID, ToolAppliedTo and DateCompleted as Dale suggested. I see seqno as optional, as the DateCompleted is a natural sequence order already.

Anyway, once that is done the constraint is that all combinations of ModID and ToolAppliedTo are unique.

Bye, Olaf.

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
You now have TWO threads going on the same problem.  Please, close one of them and stick with a single thread going forward.

I strongly agree with Dale.  This problem should be solved by normalizing your schema.  I posted a sample database in the other thread for you to look at.
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

From novice to tech pro — start learning today.