Validation Question - Input Data Can't Equal Other Data

[nico]

I'm making a Form from a Table and want the user to be able to select 4 choices from 4 combo boxes. Basically, they are 'ordering' 4 different machines in priority from 1st to 4th. So the 4 combo boxes are labeled as "1st Priority, 2nd Priority, 3rd Priority, and 4th Priority". So if the order of the machines inputted was Machine 3, Machine 2, Machine 1, Machine 4, then the user would input:

1st Priority: Machine 3
2nd Priority: Machine 2
3rd Priority: Machine 1
4th Priority: Machine 4

For the 4 combo boxes I have listed the 4 values: "Machine 1, Machine 2, Machine 3, Machine 4". But, I would like the user to only be allowed to select 1 Machine for each combo box. For example, I don't want them to be able to select Machine 1 more than once. Is there a way to input a validation rule in the Table that states something for combo box 1 like: "field 1 does not equal field 2 or 3 or 4" and then for combo box 2: "field 2 does not equal field 1 or 3 or 4", etc. Or if there was a way to 'gray out' an already selected option so the user couldn't select it twice. Thanks!

 

[Marbles]

It's a bit of an overkill, but you could try this in the After Update of your form and the fields themselves, with A,B,C and D being your 4 Priority Fields.

Option Compare Database

Private Sub A_AfterUpdate()
If Me.A = Me.B Or Me.A = Me.C Or Me.A = Me.D Then Me.A = "": MsgBox "No Duplicates", , "Help"
End Sub

Private Sub B_AfterUpdate()
If Me.B = Me.A Or Me.B = Me.C Or Me.B = Me.D Then Me.B = "": MsgBox "No Duplicates", , "Help"
End Sub

Private Sub C_AfterUpdate()
If Me.C = Me.A Or Me.C = Me.B Or Me.C = Me.D Then Me.C = "": MsgBox "No Duplicates", , "Help"
End Sub

Private Sub D_AfterUpdate()
If Me.D = Me.A Or Me.D = Me.B Or Me.D = Me.C Then Me.D = "": MsgBox "No Duplicates", , "Help"
End Sub

Private Sub Form_AfterUpdate()
If Me.A = Me.B Or Me.A = Me.C Or Me.A = Me.D Then Me.A = "" _
: If Me.B = Me.A Or Me.B = Me.C Or Me.B = Me.D Then Me.B = "" _
: If Me.C = Me.A Or Me.C = Me.B Or Me.C = Me.D Then Me.C = "" _
: If Me.D = Me.A Or Me.D = Me.B Or Me.D = Me.C Then Me.D = "": MsgBox "No Duplicates", , "Help"
End Sub

 

[nico]

That worked great! Thanks alot for your help.