AfterUpdate event does NOT update record source for another control(s)

[skydivetom]

Experts:

I need some assistance with modifying a few queries that will **prevent** me to selecting the "same value" (in combo boxes) multiple times.

To keep this thread short & concise, I will NOT cover the specific purpose of the DB. Instead, I'll jump right into the process and what I need some help with:

Background for DB "Version 01":
- Contains 2 tables, 4 queries, and 2 forms (main form and sub form).
- Mainform "F01_MainMenu" opens by default (with 2 sample records).
- In the top left (yellow), I can enter up to three keywords. In this case, I have entered "One", Two", and "Three" for record #1.
- Based on those 3 keywords, the subform displays matching records that **contain** any of those 3 keywords.
- For example, in record #1, subform displays "Field_One", "Field_Three", "Field_Twenty_One", "Field_Twenty_Three", "Field_Twenty_Two", "Field_Two".
- All of the above works flawlessly and does NOT require modification.

Current Issue for DB "Version 01":
- In the top right (green), I have 4 drop-down menus.


- Each of the 4 drop-down contain all of the 6 values listed in the subform.
- Let's assume I select value = "Field_One" in the 1st combo [CANDIDATE #1].
- Now, I move to the 2nd combo. Again, I see all 6 values listed in the subform. This, however, could create an issue with the actual data set (where distinguishing between values is not as obvious... thus, it's easy to pick the same value again).
- Given I selected "Field_One" in combo #1, I only want to see the remaining 5 values in combo #2. This would ensure that I will NOT re-select the same value multiple times in the other 3 combos.
- Same applies for combo #3. Let's say I selected "Field_Three" in combo #2, I only want to see the remaining 4 values in combo #3.
- Same logic applies for combo #4 which should only show 3 values assuming combo #1 through combo #3 have values.

Background for DB "Version 02":
- This version is a copy of v01.
- I started tweaking the queries and I made some progress on, e.g., query "qry99_Candidate_2" via the "AfterUpdate" event.
- That is, I included the "Is Null" in field [Candidate_1]

Current Issue for DB "Version 02":
- Once I selected a value in combo #1 and immediately click on combo #2, that same value is still available. It shouldn't be!
- At this time, the selected value (stored in combo #1) only disappears from the available values in combo #2 when I close and re-open the main form.
- Naturally, this is infeasible... instead, I want a selected value to immediately disappear from the other 3 combos.

My question:
- What is missing in the AfterUpdate event function for combo #1 (candidate #1) so that a value selection would immediately remove the value from the other 3 combos?
- Assuming all values for all of the 4 combos are chosen in top-down order, what additional changes would have to be made that combo #3 won't show those values stored in both combo #1 AND combo #2?
- Same applies for combo #4... anything selected in #1, #2, #3, should now be removed from the available options in the last combo.

Thoughts/recommendations?

[June7]

Change a combobox RowSource when it gets focus.

What may be missing is record is not yet committed to table when combo 2 gets focus.

This should be simple with normalized structure where each candidate is a record, not a field.

I am getting dizzy trying to figure out relationships and queries based on queries.

[skydivetom]

In another thread, Vlad (aka Gicu) provided this solution. It works great!!

I'm just tweaking some of the elements to prevent mistakes made by the user.

Thank you... I'll continue to investigate what could be done... is there a way to include the "commit to table" in AfterUpdate function? Like a "Save" record now?

[skydivetom]

Quick update...

adding "DoCmd.RunCommand acCmdSaveRecord" before the Requery line appears to work. I'll try it for the remaining combos now. Thank you June7.

//

Update ... it works for the 2nd combo. Once I get to the 3rd combo, I show too many records again.

[Gicu]

Here you are Tom, please review the attached file. There is some flickering when using the combos that I believe is caused by the conditional formatting you added to those controls, you can try to take it out and see if it improves.

Cheers,

[skydivetom]

Wow... totally amazing!! Your solutions are always based on "outside the box thinking". This works great!!!

Thank you,
Vlad

P.S. I may need to post a follow-up on in the other thread... I just noticed there's an issue w/ the number of records loaded in my subform.