Validation on identical text question

[edrahl]

I am working on a database where there are three fields named "Meeting Reason 1," "Meeting Reason 2," and "Meeting Reason 3."

The information in all three fields is identical, that is to say, that I created a drop-down list with that information. The users want the ability to list 3 Meeting Reasons, and I don't think that Access has the capability to eliminate the options that match when the user selects the first reason, and then the second reason.

Example: Choices for the field are: "Planning," "Production," "Information," and "Party."

If I select "Planning" for the first field, I can't force Field 2 to have a drop-down with that choice gone. Field 2 will still have the same four choices.

**

Therefore, I decided that I would create a Validation Rule. My hope would be that the user could select any option for the first field, but if they selected the same option for the second field or the same option for Field 3 that matched 1 or 2, they would get an error (or Validation Rule) message when they tried to save/exit the record.

Example: User selects "Planning" for Field 1, "Information" for Field 2, and "Planning" for Field 3. That should invoke an error and an error message.

**



However, when I type what I believe should be the validation rule:

[Meeting Reason 2]<>[Meeting Reason 1]

and then test it out, it reacts as if I haven't even typed the rule.

Is this because you can't do text-based validations in Access (or at least, Access 2000)?

In all of the online user's manuals and the MS Knowledgebase, they only give examples that involve number calculations or dates.

Any help on this would be appreciated.

[jya]

First off, I haven't used Validation Rules in the tables, but I think you can get what you want by a different method.

By using vba, you can use whatever validation criteria you want. You could enter some vba code on the After Update event of your combo box. If the values match, you could show a message box asking the user to select a different choice and then reset the focus back to the combo box.

For instance,

If Me!Combo0 = Me!Combo2 Then
Msgbox "Please select a different choice"
Combo2.setfocus
End If

For what its worth...