Cascading Combobox

[hinchi1]

i have a scenario which i cannot resolve. rooms within our campus buildings can be occupied by multiple research groups. so on my form i was thinking of placing up to 3 cascading comboboxes.



box 1 to show all research group names. when the user selects a name from the list, box 2 will become active.
box 2 to will show all research groups names, minus the one selected in box 1. and so on.

i think my question is, is this possible or is there a more efficient way of doing this?
i have the database, i have the tables with the data. i am just looking for a way to manipulate the user input as described above.

thanks in advance for your support.

[ranman256]

Yes, use a listbox (or subform) and select ALL the groups you want to use.

the cascade combo would be used like:
box1, choose state
box2, choose city (in that state from box1)

[Gicu]

You could have a small local (front-end) table that lists all your groups along with a Yes/No IsSelected field. Build a subform based on that and place it on your form instead of the combos you mention (when loading the form you can run an update statement to clear the old selection or leave it as default). To use it simple join the table in any needed query and put IsSelected =True in the criteria row.

Cheers,

[hinchi1]

Hi both
Thanks for the replies. My database is already spilt. If you have examples, VBA. That would help a lot. I would prefer to use drop-down boxes, but open to multiselect listbox

[hinchi1]

Hi Vlad
To be honest not sure how to execute your suggestion, but sounds interesting.

[Gicu]

Can you upload a small sample of your db with no sensitive data?

Cheers,

[ArviLaanemets]

here is a simplest example how to do this with subforms (2 different ways - with single form for rooms, and with continuous one).

To make research groups previously selected for room excluded from combo box list, you have to edit subform combo's RowSource property in rooms form Current event, and in subforms AfterUpdate event (to add aa according WHERE clause to query), and then requery the combo.

[hinchi1]

Thanks for this. I will take a look later today

[hinchi1]

i will try to upload the parts i need to make the particular form work as standalone. so i need to extract the table and queries associated with it.

[hinchi1]

Hi

Thanks for all your feedback. I have attached the database in skeleton form, stripping out sensitive data, parts of the DB which are not relevant to this request, etc.

When you start the DB, frmBuildingSearch is set to start. Just double click any item in the list and frmBuildingRoomDetails will load. The DB has several access levels so this form is read only. I have taken out the access level code for his example. You will see 3 Rsh Grp textboxes, on the original form there was only one. I don't think I will have more than three research groups sharing a room. I that changes then no big issue. This may not look the cleanest way to show the data, but for now it will do.

If then you click on "Edit Room Details" button, frmRoomDataEntry will open. on here you will see amongst other thing three Rsh Grp comboboxes. Again, not pretty but should work for me. This is where I would like to select from the dropdowns in a cascading fashion to select which research groups use the room. Select item from box 1, then that item is not shown, or is disabled in box 2 to avoid duplicate selection, and so on.

I am sure there are several ways of achieving this and I am open to all ideas. I know I will need to modify the relevant tables to capture this data but for now getting something to work would be amazing.putonline.zip

[Gicu]

Hi,
Please have a look at the updated file, I have replaced your combos with a subform bound to a new join table that tracks the roomid/researchgroupID (added a date range in there if you need to add a temporal twist to it).
Cheers,
Vlad

[CJ_London]

put this as the rowsource to combo226

SELECT * FROM [Research Groups] WHERE RGID <>nz([Combo247]) and RGID<>nz([Combo249]);

similarly put this in combo247

SELECT * FROM [Research Groups] WHERE RGID <>nz([Combo226]) and RGID<>nz([Combo249]);

sure you can work out what to do for combo 249

then in the afterupdate event of combo226 put

combo247.requery
combo249.requery

repeat for the other two combos

and in the form current event, requery all three combos

Note you have moved away from a normalised structure, so searching for what rooms are used by a department for example will become much more complex

better to have your rsh data in a separate table and use a subform for this part of the form

Edit - I see Vlad has done just that

[hinchi1]

Hi,
Please have a look at the updated file, I have replaced your combos with a subform bound to a new join table that tracks the roomid/researchgroupID (added a date range in there if you need to add a temporal twist to it).
Cheers,
Vlad

Hi Vlad
Will have a look tomorrow, much appreciated for the feedback. I am sure I will be asking more questions over there next few days.

[hinchi1]

put this as the rowsource to combo226

SELECT * FROM [Research Groups] WHERE RGID <>nz([Combo247]) and RGID<>nz([Combo249]);

similarly put this in combo247

SELECT * FROM [Research Groups] WHERE RGID <>nz([Combo226]) and RGID<>nz([Combo249]);

sure you can work out what to do for combo 249

then in the afterupdate event of combo226 put

combo247.requery
combo249.requery

repeat for the other two combos

and in the form current event, requery all three combos

Note you have moved away from a normalised structure, so searching for what rooms are used by a department for example will become much more complex

better to have your rsh data in a separate table and use a subform for this part of the form

Edit - I see Vlad has done just that

Hi Ajax
That's for the feedback. I have been working on this dB for three years now and some of what you see is from the offset. I would love to redesign this at some point to follow proper standards of dB design. For now it serves its purpose. It was only supposed to be used by two or three people but it has grown. So making it more robust is in the pipeline.

[CJ_London]

the changes suggested have no impact on the number of users. What does matter is to have a split db, the back end containing only tables and located on a server that users have access to and the front end which contains everything else and a copy located on each users local drive and linked to the back end. If you are not doing this then you are asking for trouble.