Various Combobox lists.

[NavyJoe]

I fixed the query issue. I had to create a form using the query and modify it the way I want. Anyway, I'm working on the form data entry now and I have a question. I have 2 combo boxes (Branch and Rank). What I want is:

1. If combo box = Army or Army Reserve or Army National Guard, then I want the Rank Combo box to list all the ranks in a list. (I know I have to enter the list myself)

2. If combo box = Navy or Naval Reserve, then list that branches ranks.

3. Same for each rank to include: USA, USANG, USAR, USAF, USAFR, USCG, USCGR, USMC, USMCR, USN, USNR.

So that's a total of 11 different combo boxes. Please help.

 

[Norie]

So that's a total of 11 different combo boxes

Why?

Is it not 1 combobox which lists all the branches and another combox box for the ranks that is dependent on the first?

Do you have a table that lists rank and branch?

If not it would probably be a good idea to create one and use it for the source for the comboboxes.

Also if you create the table you can update any changes in rank/branch and they will be reflected automatically in the comboboxes.

Can you give a further example of the data?

 

[NavyJoe]

I meant technically there are 11 different combo boxes I want to see in one combo box.

I just created a table where the fields were each branch and I listed each enlisted rank for starters.

I did look up this:

http://www.mrexcel.com/board2/viewtopic.php?t=117630&highlight=combo

Is this what I need to do?

 

[Norie]

Joe

I took a quick look at the link and yes I think that is the correct approach.

I've created a small example using dummy data and sent it to you.

 

[NavyJoe]

I did all that. It doesn't like my requery code

Private Sub Branch_AfterUpdate()
Requery Rank
End Sub

It gives me a:

Compile Error:
Wrong Number of arguments or invalid property assignment.

It highlites Requery...

Is that wrong?

Norie: hotmail blocks mdb too .

 

[Norie]

Is Rank the combobox?

If so it should be Rank.Requery.

Norie: hotmail blocks mdb too

No luck!

 

[NavyJoe]

I got it to work. I forgot to add the doCmd function in front of Requery. But now it only gives me the ranks under USN

My criteria for the block is:
[Forms]![Award Log]![Branch]

Then I have in the AfterUpdate on the Branch Combobox
DoCmd.Requery (Rank)


Is something wrong?


EDIT: It gives me the list for the first choice made. I tried putting the Requery command in the OnChange event also...no luck.

 

[Norie]

I don't think you need the Requery method of DoCmd. The help file says that it is only included for backward compatibility.

BTW The expected argument for DoCmd.Requery is a string.

What is Rank the name of the form or the combobox?

I used this:

Private Sub Combo0_Change()
    Me.Combo2.Requery
End Sub

Where Combo2 was the combobox for the rank, Combo0 was the combobox for the branch and Me references the form.

The criteria looks OK.

This is the SQL I used for the branch combo:

SELECT DISTINCT Ranks.Branch
FROM Ranks
ORDER BY Ranks.Branch;

And for the rank combo:

SELECT Ranks.Rank
FROM Ranks
WHERE (((Ranks.Branch)=[Forms]![Ranks]![Combo0]));

Ranks is a table with 2 fields - Branch and Rank.