Multiple Combo Boxes

[spock1971]

Hi guys

I'm creating a database where there will be multiple combo boxes on a form. When a user chooses an option from the first combo box, I want the available choices in the 2nd combo box to be reduced and so on.

I thought I'd create a navigation table so the first column has multiple occurances of each option, but then the second column has the available choices for the first option etc. Then, I thought I'd use the chosen value in combo box 1 and filter my navigation table depending on the result.

However, the field doesn't appear to be storing the value. When I set up the query on the combo box to select distinct navigation.[cause] from navigation where navigation[type]=forms!form name[type] it doesn't work.

Any ideas

Cheers

 

[RuralGuy]

Maybe something here will offer some clue as to the problem.

 

[spock1971]

Thank you but it still doesn't appear to be working. It says it's expecting an expression after the first equals sign and not the word select.

My code is:

cboCause.RowSource=Select Distinct Navigation.Cause from Navigation Where Navigation.Claim type=cboClaimType.Value order by Navigation.Cause

Help please.

 

[RuralGuy]

I don't know if the RowSource is correct but the syntax for what you posted should be:

cboCause.RowSource = "Select Distinct Navigation.Cause " & _
"FROM Navigation " _
"WHERE Navigation.[Claim type] = '" & Me.cboClaimType & "' " & _
"ORDER by Navigation.Cause;"

If [Claim Type] is a text field, or:

cboCause.RowSource = "Select Distinct Navigation.Cause " & _
"FROM Navigation " & _
"WHERE Navigation.[Claim type] = " & Me.cboClaimType & _
" ORDER by Navigation.Cause;"

If [Claim Type] is numeric. You should really *not* use spaces in any names in Access, use CamelFontNames instead.

 

[spock1971]

Still doesn't like the syntax.

Are my combo boxes to be called cboCause or Cause etc?

Is the cbo prefix there to tell it to look in a combo box?

I'll try re-building the form to see if I've managed to scupper it by messing about for the last 3 days.

Cheers again

 

[nateobot]

Still doesn't like the syntax.

Are my combo boxes to be called cboCause or Cause etc?

Is the cbo prefix there to tell it to look in a combo box?

I'll try re-building the form to see if I've managed to scupper it by messing about for the last 3 days.

Cheers again

The cbo prefix to make your code easier to read and understand. If you have an object called Cause, you might know what it is since you developed it, but what happens if you don't support it in the future or someone else takes over the project and has to implement some enhancements. Now that person has no idea if Cause is a textbox, combobox, label, etc...

Here is a good link that you should try following when naming objects:
http://www.acc-technology.com/namconv.htm