Validate Field based on Previous Field's contents

[MariaAux]

Hi,

I've been trying for days to come up with a solution and I wonder if you could please help.

I have got a Student Report Card maker whereby the user (teacher) simply selects the comments from drop down menus. The Categories are as follows;

General Positive comments

General Negative comments

Reading

Writing

Grammar etc etc...

For each category there are two drop down menus: The same phrases but the second menu is preceded with ' and' meaning that it must be the second part of a sentence. For example;

Reading Menu 1: "He has a very good level of reading"

Reading Menu 2: " and he has a very good level of reading"

This allows the teacher to mix any two parts of a sentence from any of the categories which is then displayed in a calculated text field box that combines all the the selected phrases from the drop down menus.

The only catch is that the first part of a sentence must come from one of the Menu 1's and the second part of the sentence must come from one of the menu 2's

As the designer of the database, I just know to always adhere to this.

However, I have other teachers writing the report cards and I want to create a validation that if a phrase from a Menu 1 has just been selected and the user selects another phrase from another Menu 1, a pop-up window stops the user and tells them to select a phrase from Menu 2.

I have tried several solutions but am just not even coming close. Please open my eyes if you know of a way validate the fields in this way. I have attached a screenshot of the database to depict what I am trying to do.

Thanks so much in advance

[comment]

IMHO, both phrases should come from the same value list. Combining the selected phrases into a single sentence can be handled separately by a calculation, and there is no reason to complicate the selection on this account. This way you can validate the fields simply by =

SelectionA ≠ SelectionB

You could also use a "dwindling" value list, so that selecting a value for A would remove the item from the subsequent selection for B.

BTW, your Categories could (and probably should) be separate records in a related table.

[MariaAux]

Thanks for the reply,

The problem is each category has a different list of phrases, so it's not a matter of using ≠ because none of the phrases are the same. Let me put it another way;

Category A Menu 1

Category A Menu 2 (These are the same phrases as menu 1, except preceded by " and......")

Category B Menu 1

Category B Menu 2 (These are the same phrases as menu 1, except preceded by " and......")

Category C Menu 1

Category C Menu 2 (These are the same phrases as menu 1, except preceded by " and......")

etc.

Menu 1 MUST be followed by Menu 2 which MUST be followed by MENU 1 etc. (121212121212)

I want to create a validation that if Menu 1 is chosen and the user selects another Menu 1 (From any of the categories), a validation errors pops up.

The categories are a separate table as you suggested. I could for example have a check field with each phrase whereby all the menu 1 phrases are a 1 and and all the menu 2 phrases are a 2. Then, a calculation field concatenating all the chosen phrases from the menus would produce a result like 12121212. And if this calculation returned 12122 for example, I could come up with a formula that found two identical digits alongside each other, and validate that as an error. However, it seems that calculation fields cannot be validated?

Shall I post a Runtime version of my little database for you to have a look at to understand better what I am trying to do?

Thanks so much.

[comment]

Category A Menu 1

Category A Menu 2 (These are the same phrases as menu 1, except preceded by " and......")

What I am suggesting is:

Category A Menu 1

Category A Menu 1 (These are the same phrases as menu 1)

It will be easier to add the "and' at another point than trying to compare similar, but not identical, phrases. It will also allow the user to select any two phrases in any order.

[efen]

Does the attached help?

ReportComments.zip

[MariaAux]

Efen,

You champion! Your file was not what I was trying to do, but within your scripts I saw the magic key to my problem;

Patterncount!!!!!

I created a validation field that puts a 1 (If a phrase is chosen from a List 1) and a 2 (If a phrase is chosen from a List 2) calculating 121212 etc. I set a script trigger (on Modify) in each field that if ::Validation = 11 or 22, show a custom dialogue alerting the user to make a change. All seems to work beautifully now except one little quirk.

The script / script trigger works fine for the first few fields but not for the fields further down. I put a 'Commit record/request' to start the script and then all worked perfectly. The drawback of putting a 'Commit record/request' is that I cannot then 'Clear' the field. The user has to do it manually. I tried;

'Go to previous field' intending to navigate to the field that was just entered and validated but it went to a totally different field. Mmmm. Any ideas?

Thanks a million for your golden key - PatternCount