Cascading combo Boxes

[lcfc]

Hi,

I am getting very confused, so need some help! I have 3 combo boxes. When a user picks from the first box, this needs to limit the options in the 2nd box. Then when the user selects an option from the 2nd box, this will limit the options in the third box.

At the moment i have a main table and the three combo boxes row sources are obtained from 3 separate tables. Can somebody tell me how achieve this?

 

[RickSpr]

See "How do I limit the contents of one combo box based on the selection in another?" faq702-681

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[kjv1611]

Well, if you use some VBA and SQL (DoCmd.RunSQL), you could just have the code to say something like:

Public strCombo1 as String
Public strCombo2 as String
Public strCombo3 as String

Private Sub Combo1_AfterUpdate()
  Dim strSQL as String
  strCombo1 = String
  strSQL = "Your Query here in SQL format, using strCombo1  
  to filter the results"
  [green]
  'Then something like this[/green]
  Combo2.RecordSource = strQuery [green]'or the 
            recordsource for the query (you may have to set 
            a recordset equal to the query in order to 
            use.. don't remember for sure[/green]
  Combo2.Requery  [green]/maybe need this line??[/green]
End Sub
Private Sub Combo2_AfterUpdate()
  [green]'basically the same as the first, except you are 
  setting the source for Combo3 here, and using both Combo1 
  and Combo2 as the filtering agents in the SQL code.[/green]
End Sub
Private Sub Combo3_AfterUpdate()
  [green]'any code here would not have to do with this 
  whole filter thing, but instead whatever you want to 
  happen after all the choices have been set.[/green]
End Sub

Sorry not extremely specific, but I believe this type of method would work. I'm sure there are a couple different ways to do it, but I believe this one should work pretty well. There are some FAQ's, I believe, that cover this very subject in the Access VBA Coding forum..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV