Help? Complicated Drop-Down

[Pick9811]

Hello all,
I am brand new to Access (don't know anything, for the most part). I am trying to put together a database that is way beyond my ability (to be quite honest). I hope to get it up and running on 4 months or so, and learn A LOT along the way. Hopefully in a couple months this database is not beyond me.



Anyway, my question... I am trying to create a database for my unit in the military. I want to be able to take it with me to any unit, and I want to be able to keep people in it, and use it to track our administrative tasks. One of the things I want in it is rank. However, the branches of service all have different rank structures, and to further complicate things there are also civilians. So I want a drop down menu that will first determine what their "status" is (Active duty, Civilian, etc) and then determine what their "branch of service" is (Air Force, Navy, etc) which will pull up the right ranks. I already have a table with status entries, and another with ranks for each branch. How do I make it decide which to use?

Thanks for helping out the super-newbie!

[Access_Blaster]

First off thank you for your service! If you could post your fields in your "main table(s)" that would be helpful. Identify the primary key and any foreign keys you may have.



Richard

[Pick9811]

Thank you! All of us appreciate knowing that we we are supported by the community we live in.

Status is the first hurdle... the status table has an autonumber as the primary key, and "Active Duty - Enlisted", "Active Duty - Officer", "IMA - Enlisted", "IMA - Officer", "Reserve - Enlisted", "Reserve - Officer", "National Guard - Enlisted", "National Guard - Officer", "Civilian", and "Contractor".

Then Branch of Service is also an autonumber PK, and the fields are each of the branches of service, plus DoD (which will only apply to some civilians in some areas).

Then (if you are not familiar with the rank structure), Officers are much easier than enlisted because their are only two variations. Navy and Coast Guard are the same. Then Air Force, Marines, and Army are the same. However, on the enlisted side I have to have a different table for every branch except Navy and Coast Guard.

So, I want to try to figure out how to make access understand if they are an Enlisted Marine then have this table in the drop down vs. an Air Force Officer which uses a different drop down.

Again thanks for the help!

On a separate note... if anyone lives in the Omaha area, I'd be willing to pay for Access lessons

[Access_Blaster]

Pick9811,

A combo box with a drop down list “showing” a persons status is simple. You would base the combo box row source on your status table or query. You could even have cascading combos. When you choose a status the next combo box updates to reflect new choices.

A couple of things jump out at me right from the start. Are you going to identify the person? Example Cpl John Smith Ft Bragg N.C.? I don't see fields for that.

I don't know how far along you are in this project but if your field names have spaces and hyphens and other symbols its going to make for a coding nightmare. Reserve - Officer should be ReserveOfficer in the table. Then you display a label on a form/report next to the field that has correct visual Reserve Officer.

if anyone lives in the Omaha area, I'd be willing to pay for Access lessons

If you post your request for Paid help some place like "water cooler" or new thread someone just might take you up on it.

As you are starting to realize Access has a steep learning curve.
http://www.youtube.com/user/LearnAccessByCrystal
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
http://allenbrowne.com/links.html#Tutorials

I am going to keep looking at your table design. I am sure this fourm can help you.

Regards,

Richard

[Pick9811]

Richard,
That's exactly what I'm talking about, the "cascading combos". When they say "I'm an Active duty Navy Officer", I want it to take them to the Navy Officer rank table to pick their rank, and likewise when they say "I'm an Air Force Civilian" I want it to take them to the civilian grades table as a drop down to select their grade.

To answer your question, am I going to identify the person... yes. I just didn't include that because it wasn't applicable to my question (or at least I didn't think so). My people table has everything from Home phone number to Spouse's first name, and next of kin contact information. So, I'm trying to make it as all inclusive as possible. I just want it to have some of the "bells and whistles". Eventually, I want it to be able to export rosters to Excel and all that kind of thing.

So, how does the "cascading combo" work? I'm pretty sure that's exactly what I'm trying to do.
Brent

[Access_Blaster]

Hey Brent,

I mocked up a small sample for you to look at. I didn't include cascading combo samples, but I will give you a link or a example soon.


http://www.baldyweb.com/CascadingCombo.htm

Richard

[Pick9811]

That's really cool! And the form can also update those other tables... neat. I'm going to have to learn how to do all this! Where's the water cooler?
Brent

[Pick9811]

I tried to attach what I have so far, but it's too big. I tried to code the cascading combo for rank, but I haven't been able to figure it out yet.

I have a table for each rank, and then put the following code in for after update on the branch field:

Private Sub cboBranch_AfterUpdate()
On Error Resume Next
Select Case Branch.Value
Case "USAF"
Rank.RowSource = "AFRank"
Case "USMC"
Rank.RowSource = "MarineRank"
Case "USN"
Rank.RowSource = "NavyRank"
Case "USA"
Rank.RowSource = "ArmyRank"
Case "Civilian"
Rank.RowSource = "CivilianRank"
Case "USCG"
Rank.RowSource = "NavyRank"
End Select
End Sub

Keep in mind, this is my first attempt at VBA, so I probably jacked it all up. I also tried adding the same code for after update to the rank field. Neither was successful. I got the code off the internet, and tried to adjust it for what I am trying to do.

Thanks for any help you can give,
Brent