Combo boxes that use the same data source

[dave_mere]

I'm trying to build a form for patients who have multiple diagnoses. There are up to seven diagnoses and they need to be ordered from 1 to however many there are. Numbers must not be repeated or missed out; so it's 1; 1,2,3; 1,2,3,4,5; however many.

I'm trying to think of the best way to design the form.

I have tried seven combo boxes called DIAG_ONE, DIAG_TWO all of which have the seven diagnosis as options. But I then need to add code to control the selection of the content in each of these boxes, depending on what is previously selected - this is really complex, and seems too much.

I also tried creating boxes for each of the conditions; DIAG_DEPRESSION, DIAG_DEMENTIA, etc, and populating them with list values 1, 2, 3, 4 etc. But I need to manage the content in the same way; again really complex.

I'm sure there must be a simple way of doing this but I can't see it right now. Can anyone advise?

 

[sneuberg]

What's magic about seven? Why not put these diagnoses in a related table and then present them in a subform on your form. You wouldn't be limited to seven this way.

 

[dave_mere]

Hi sneuberg, that bit isn't the problem. The diagnoses are already in a related table, and there's nothing magic about seven - that just happens to be the scope of this project.

The bit I'm stuck on is the form design. I need to ensure that each diagnosis is chosen once and only once, and that DIAG_ONE is chosen first, DIAG_TWO is chosen second, etc etc. Whatever functionality is in place to enable/disable these controls, and also to restrict the diagnoses available to these controls, needs to implement both when a new patient record is loaded and also as the data is changed; ie. when diagnosis details are updated.

I've seen this kind of thing before; a collection of dropdowns which functionally relate to each other. Implementing this seems complex though and I'm wondering if there is an easy solution, or if this problem can be solved perhaps by another method? I'm sure someone will have solved this before!

 

[Uncle Gizmo]

If you imagine a completely ludicrous situation:- the first diagnosis is that the patient doesn't have any legs.

However options for the second and third diagnosis are for the patient has athlete's foot the patient has a dislocated knee obviously this can't be true so how do you restrict those diagnosis from later selections?

Sent from my SM-G925F using Tapatalk

 

[dave_mere]

The diagnoses I need to restrict are those that have already been chosen. So if the user chooses Depression for Diagnosis 1, then this option is unavailable for the rest of the selections. Should the choice of Diagnosis 1 change, then the options available for Diagnoses 2-7 change accordingly.

 

[Grumm]

Here is a little demo of what I think you want :
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=64542&stc=1&d=1479807975

So basically you have to select the first one first. Then for all the next options you don't have the previous options. So if you pick first 4 the next choice can only be 5 or more.

The selection in the combo-box is done with a query. I just select all the records greater than the previous combo-box value.
What I hope is that there is a limit to the combo-boxes. Or are there unlimited diagnoses for each patient ?

 

[Uncle Gizmo]

The diagnoses I need to restrict are those that have already been chosen. So if the user chooses Depression for Diagnosis 1, then this option is unavailable for the rest of the selections. Should the choice of Diagnosis 1 change, then the options available for Diagnoses 2-7 change accordingly.

Ah! I see...

I think I answered a similar question HERE:-

http://www.access-programmers.co.uk/forums/showpost.php?p=1467041&postcount=34

 

[dave_mere]

Or are there unlimited diagnoses for each patient ?

Hi Grumm, yes that's getting there. The box enabling/disabling functionality is just what I need, that's a good start. And we have the basis of changing the content as well.

Changing the content based solely on numbers (in this case remove all > previous box) won't work for me though. The values aren't numeric, and can be chosen in any order, I can't use something as simple as >.

The good news is that the number is limited - there are only seven diagnoses.

What I'm thinking now is a separate sub procedure that analyses the content of all active boxes at once, and then remove those options from the disabled boxes. It will fires, as you have done, on the After_Update event of each and every box.

What do you think?

 

[Grumm]

Well you said : "Numbers must not be repeated or missed out; so it's 1; 1,2,3; 1,2,3,4,5; however many. "

For me they are in order
If you don't care about the order, just add the condition of the previous box to the where.
So
where Daig_number <> DAIG_1 for the second box
where DIAG_number <> DIAG-1 and DIAG_number <> DIAG_2 for 3rd box and so on.
I used numbers because it is easier. I understand you will have text. But you can still add a number as ID and use that to filter. The text can be anything you want. It is just for display.

 

[moke123]

heres an example using both a combobox and listboxes.
the rowsources of the combo and lists include a query which excludes those ailments that have already been selected.
have a look and see if it points you in the right direction.

 

[Grumm]

I like that kind of solution. But I followed this instruction :
"I need to ensure that each diagnosis is chosen once and only once, and that DIAG_ONE is chosen first, DIAG_TWO is chosen second, etc etc"
It is not really clear what the OP expect us to do.
Maybe you can try something that you need and come back to us if you have any problem with it.
With the 2 examples you can already make a solid fancy form.

 

[dave_mere]

That's twice you've pointed that out Grumm.

Thanks moke for your excellent solution.

 

[moke123]

your welcome Dave.
feel free to post back if you have any questions on how its done.
Good luck with your project.