Issues with table structure / dependent combo box and drop down list

[Carkcark]

It's my first post on these forums so I am open to editing and changing this post as required by the forum rules.

I have created a database on Microsoft Access 2007 (might be getting access to a newer version in the next few days, so solutions using newer software might still be applicable) in order to organise a project for a flight simulator game (I'm hoping to work in flight simulation later on in life). My strengths lie in game design and not in database management as you will soon see.

What I currently have so far is a database of aircraft types and registrations of real aircraft which I am trying to model. I have a form which has 2 combo boxes on it where one is independent and the other is dependent on the first. I used the following to learn from

http://www.blueclaw-db.com/download/dependent_combo_box.htm

and I pulled and edited the parts to make it work in my database. I believe I have correctly gotten the framework for what I want to achieve, the issue I am having is that when I click the drop down on the independent combo box and try selecting my chosen aircraft type, the selection defaults to one of 3 options. It defaults to one of 5 options based on which aircraft type I am trying to select as my database has 5 aircraft types so far. 737-300, 737-800 and 757-200 and a couple of extras. From the screenshots below you will see that when I tried selecting G-CELH (a Boeing 737-300 aircraft) it defaulted to EC-JSJ (which as part of my database is a 737-300). I was able to select the options on my dependent combo box, but I was unable to change my independent one. I believe this has something to do with the fact my Advisor_ID column has multiple values/records (sorry my Access nomenclature is poor) relative to different registrations and that EC-JSJ is top of the list so it defaults to that one.

The name for the column Advisor_ID is purely taken from the previously mentioned BlueClawDB example and I copied the data from my ModelLink column over to the Advisor_ID. The Student column (another ripped name from BlueClawDB) is a list of 3-d part/model names that I have been creating. What I am wanting to achieve is when I select an aircraft registration, only select parts will be available for selection in the drop down list. For example G-CELA a Boeing 737-300 will only have the option of 737-300 parts and not those part that you would have on a Boeing 757-200. The Student Table which I have seems to be working perfectly fine and I have assigned the respective identifiers to match up with the Advisor_ID column. My issue seems to be lying with the Advisor_ID having multiple options part. Is there a way to group them?

I'll regularly look back to check for replies and would be happy to clear up any issues and also to clean up this post and make a nice tutorial to help others in the future

Thanks

 

[alansidman]

Here is a link on how to do cascading combo boxes.

Access Tips: Cascading Lists for Access Forms - Printer Friendly Version

 

[Carkcark]

Hi Alan,

Thanks for replying, those links are useful, however I am struggling to get around how to get Access to know which aircraft registrations apply to 737-300s 737-800s and 757-200s as I would prefer not to have a 3rd drop down box where I have to select the aircraft type from a drop down box of 3 e.g 737-300 737-800 757-200 prior to selecting the aircraft registration which would then give me the restricted part list that I want in the third box. This is due to the fact I eventually want to get non-aviation guys involved in the project and they won't want to invest the time in learning which aircraft registrations apply to each type.

Do you know how I could go about achieving this?

 

[welshgasman]

Your table should look something like this

Aircraft
AircraftID

 

[welshgasman]

Please ignore post 4. I was writing a reply and got disturbed in work. When I came back and posted, I lost all my alterations, so will not recreate.

Look up database structure/relationships before attempting this, otherwise it is going to be extremely difficult.

 

[alansidman]

crossposted: Issue With Table Structure / Dependent Combo Boxes With Drop Down Lists - UtterAccess Forums
and Creating groups and having multiple drop down list options
and http://www.access-programmers.co.uk/forums/showthread.php?p=1514607