Microsoft Access 2000 Dependant Combo Box Value Relationships

[ArffMarine]

I am trying to build a database that will allow me to track students and instructors at a Department of Defense formal training academy.

I have seen this repeatedly on this site but am not grasping the concept. I have a basic knowledge of how to put together a database but all my knowledge comes from trial and error. I have no formal schooling in MS Access but learn quickly when steered in the right direction. On top of all that I am a U.S. Marine and as such am probably not valued for my intelligence.

That being said the more barney the response the easier for me. The other examples are not easy to comprehend.

Here is what I want to do….

The academy has students and instructors from all four branches of the DoD. I want to make a combo box that will read the Branch of Service (i.e. Army, Marines, Navy, Air Force) from a separate combo box and a value list of the selected branch’s ranks.

So if I select army in the first box, the second box will only allow me to choose from SGT, SSG, SFC or if I choose Marines in the first box I will only be able to choose from Sgt, SSgt, GySgt….and so on.

If this can be done with an option button group so much the better. Sorry for the long post I just wanted to leave no room for question on my uh…well…question.

Thanks

ArffMarine

 

[bhoran]

Ok here is the idea.

You build a table we will call it tblForces with 3 fields
ID, Rank, Branch

and you populate with required info. e.g.
ID Rank Branch
1 Sgt Army
2 Sgt Marine
3 SSg Army
4 SSgt Marine

so every row has both fields populated when you have all the ranks in you should have branches also. (If more than one branch has that rank you will have more than one record for that rank). I have added the field ID as it is good practice to always have a unique field in every table - in the property of this field you can make it auto number so it will allocate a number automatically when you enter data into the other 2 fields.

Create a query using table tblForces as the source. Choose on ly the Branch field in the query. Make it an aggregate table by hitting the sigma (backwards E) button on the toolbar, When you run this query you should get a list of the branches e.g. Army, Marines, etc etc but they should be listed only once. This is the source for your 1st combo box.

You need to create a form for the list box to go on. Create the combo box (on the new form) using the wizard and select the query you just created as the source.

Now make a 2nd query over the same table tblForces and select both fields Branch and Rank also aggregate this query. Then using the build wizard in the criteria, reference the combo box on your form.

Now you have a form with a combo box that lists the branch, and the source to list the rank.

hat do you want to do with the rank list? Just display it? or have it available for selection for some other purpose?

If you wish to have another combo box display this then you simply ceate one using the last query we built as the source. If you want to just display it it is probably best to use a subform. Please get back to me with what you are using the list of rankings for.

I am in Aus so if I don't get back to you today I will tommorrow.

 

[Rolliee]

Build two tables - one is the service. The other is the ServiceID and the ranks associated with that ID. Combo Box one shows the service. Combo Box two is then populated with the ranks associated with that ServideID.I will send you an example if you wish.
rollie@bwsys.net

 

[ArffMarine]

BHoran,

Where in Aus? I was deployed to Darwin in Oct 2001. BAck to the combobox...I got it...almost. I can't quite get the second combo box to recognize the first. I don't know if I am using the build tool properly. What will the table be used for? It will be used to enter an instructor's name, rank and work shift (Day or Night) into our database. We have 5 branches of service and 15 ranks so I was trying to get this combo box thing to work to save time. Thanks for your help

 

[boprat]

Rolliee:

While not the originator of the request, I am encountering a problem that mirrors this one. Would you mind sending along an Access 97 example to me at pine_tar@yahoo.com ?

 

[bhoran]

Rollie in relation to your 1st question I am in Sydney at the moment.

It is pretty warm and not a bad place to live with clean beaches all around.

So you have the 1st combo box working now with the second one setup the second combo box on the report and go through the same process as the 1st.

Using your wizard create the combo box and choosing the 2nd query as the List source for this combo. You must have the 1st conbo box selected before the second one will give any details.

If that isn't working I suggest making a selection in the 1st box then run the 2nd query to check that the reference criteria is working correctly.

If that is working then try rebuilding the 2nd combo box. If it is not try rebuilding the criteria to = the combo box on the form.

Once you have them both working properly you can link them to the input table (data source) and they will automitically update the table as you go.

 

[bhoran]

boprat

sorry I don't use '97. But the same principle as above will apply.

 

[Rolliee]

I am getting all the questions and questioners mixed up. boprat, your email bombed and the sample I sent returned undeleverable.

It is okay to allow the wiz to set up combo boxes. Then you tweek them to do what you wish. As far as a second combo box 'showing anything,' it will generally show something unless you give it an original false query - for example WHERE 2 <> 2; This leaves it vacuous or empty. A select (or on_click) of the first then sets the data source of the second combo box with a WHERE condition based on the first. This is sometime tricky and you should use a msgbox in development to be sure you have the right column in the combo box. Not all columns in a combo box need be seen by a user.

This is the code that does it in one of my mdb's.

Dim SQL As String

SQL = &quot;SELECT * FROM Table1 WHERE Table1.city = &quot; & Chr(34) & Me.Combo0.Column(1) & Chr(34) & &quot;;&quot;

Me.Combo2.RowSource = SQL


me.combo2.

Trial and error is the real name of the game.

Rollie

 

[bhoran]

Sorry Rollie I accidentally addressed my last post to you instead of ArffMarine

ArffMarine my earlier post to Rollie was meant for you.

 

[ArffMarine]

BHoran,

No problem. Me and a co-worker got most of the bugs worked out. We are having a problem with the combo box not refreshing when we want to enter a new record. Also, do you know if there is a way to use a checkbox or option group instead of a combo box to query the branch of service? Thanks again your help was invaluable.

ARFF Marine

 

[bhoran]

You can set the default of the combo box to something like Select Branch then make sure the form is refreshed after update. When you go to the next record and that entry is stored on thetable it should revert to the default.

Othewise the combo box will only requery when it is opened.

You could setup the branches as check boxes. Then the check box only results in a Y/N answer so you would setup a new table Branches, with 2 fields Branch & selected (Y/N)

Then you would have to change your query to link the 2 tables by Branch and add the selected field then have the criteria of Selected = Y.

You still have the issue of the 2nd combo box not refreshing, you can do a requery of the combo in code (you would attached it to the after update event)

the code would go something like

Me.Combobox2.Requery

then the query would run each time the check box is updated.

When i am referring the check box but it prob should be a radio button, it would be in the group so only one can be chosen at once.

I am off for a long weekend.

Good luck and get back to me if you need more info. But I won't be back until Tues my time.