Multiple Comboboxes using same source data but requiring unique input

[andy_dyer]

Ok - need to explain what I am trying to do and why...

I tried and failed to get this to work using a multiselect listbox...

I have a list of departments in tblFunctionalArea

My main table is tblStatic

I want to be able to for each record select multiple departments affected by a record and store them in the tblStatic.

After looking around i couldn't find many people successfully maanging to store listbox values in a table...

I decided to create 5 fields in tblStatic and in my form create multiple combo boxes cboFunctionalArea1, cboFunctionalArea2 etc etc which are bound to these fields

It is not likely from the list of 15 dept's that more than 5 would be affected at any one time

So to my problem...

I want to be able to ensure the list for any combo box requeries and takes out any selection in the other boxes.

I have this working in a strict cascade fashion i.e. in cbo1 all dept's visible, in cbo2 it takes off whatever was selected in cbo1 etc etc

But if someone then jumps back and deletes the content of cbo3 then the whole thing breaks or if they amend in the wrong order it breaks...

Any ideas how to fix this?

 

[pr2-eugin]

Hello Andy, I am just picking up what I read through your post..

After looking around i couldn't find many people successfully maanging to store listbox values in a table...

That is because, it is not meant to be.. This goes against the Rules of Normalization.. Which dictates that each entity should be atomic, which mean multiple values in one row is a NO NO..

I decided to create 5 fields in tblStatic and in my form create multiple combo boxes cboFunctionalArea1, cboFunctionalArea2 etc etc which are bound to these fields

Again, this would not be the right structure.. They need to be Row values.. not Columns..

So to my problem...

If it is possible for you to explain again, simply what you want to do.. We might be able to help out..

 

[andy_dyer]

Thanks for the response - I apologise if my proposed solution isn't ideal...

I want to achieve a list of depts in a single field in a query that can be exported to excel and displayed alongside the rest of the record.

I couldn't work out how creating a seperate table with multiple rows could then be brought back together and displayed...

If there is a neater way of doing this then I would be grateful for any advice?

My current solution is trying to use 5 combo boxes which all look up the same base data but only allow one instance of an answer in any of the boxes.

I have this working if you input in combo order 1,2,3 etc and delete in reverse order, but if you try and mix it up it falls over and i was looking for a more robust code to include in my combo...

Does that help at all?