Cascading Combobox code to update multiple combo boxes 1

[r6rh]

I am looking for some help in my custom Access Database.

I have 24 cascading combo boxes (12 independent and 12 dependent/cascading) that I would like to code in one place to keep it nice and neat so in future I can easily edit it.

I have had some responses to use a sub / function to do this. I am looking for some help / or an example.

I attached a link to youtube of a video I made to clarify. I was thinking about running the code in the form_afterupdate event. What your thoughts / opinions / suggestions.

Thank you for your time.

PLEASE POST!!!!

http://www.youtube.com/watch?v=0YVeLvkUxKo&feature=autoplay&list=UL0YVeLvkUxKo&playnext=1

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r6rh]

I understand all the code inside which would be inside the loop. I attached an example I found that is very similar.

http://fontstuff.com/access/acctut10.htm

I could go and do this 12 times but it would be a pain in the butt to edit down the road. I want to learn how to make the code simple easy and lean.

I'm really just stuck on how to approach the sub/function for multiple comboboxes. Whats the format? Is their any examples of functions that I can look at. How can I index my loop for each combo box.

The combo boxes on the left names are comboboxtool#1_type (generic: comboboxtool#""_type). The comboboxes on the right names are comboboxtools#1_detail (generic: comboboxtools#""_detail).
How can I index these variable with a loop? Turn them into strings and add a 1-12 in the designated location? I just need some guidance on a function to get me rolling.

Let me know if your confused at all. I appreciate you answering my question. If their is an easier way please lead down the path.

 

[PHV]

A starting point:

For i = 1 To 12
  Debug.Print i, Me.Controls("comboboxtool#" & i & "_type").Value, Me.Controls("comboboxtool#" & i & "_detail).RowSource
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r6rh]

I think I've conceptually figured it out with your hints. I just need help with the syntax. In my Select Case writing am I writing the "then" statement correctly? I'm getting a compiler error and it is trying to find a list separator or). Below is the exact code I am using with. Thank you for your help. I am so very close to getting my project done!

Private Sub Form_AfterUpdate()
For i = 1 To 12
      
  Debug.Print i
  
  Select Case Me.Controls("comboboxtool#" & i & "_type").Value
    Case "Turning Tool"
         Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblTurningToolOptions"
    Case "Top-Notch Grooving Tool"
         Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblTop-Notch GroovingToolOptions"
    Case "Thinbit Grooving Tool"
         Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblThinbitGroovingToolOptions"
    Case "Top-Notch Threading Tool"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblODThreadingToolOptions"
    Case "Parting Tool"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblPartingToolOptions"
    Case "Tap"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblTapOptions"
    Case "Stop"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblStopOptions"
    Case "Drill"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblDrillOptions"
    Case "Boring Bar"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblBoringBarOptions"
    Case "Endmill"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblEndmillOptions"
    Case "ID Grooving Tool"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblIDGroovingToolsOptions"
    Case "Center Drill"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblCenterDrillOptions"
    Case "Spot Drill"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblSpotDrillOptions"
    Case "Face Grooving Tool"
        Me.Controls("comboboxtool#" & i & "_detail).RowSource = "tblFaceGroovingToolOptions"
    Case Else
        End Select
       
   End Select
   
Next
End Sub

 

[SkipVought]

Don't forget to "thank" PHV with a little purple star, if you feel he provided valuable help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Replace this:
[tab]Case Else
[tab][tab]End Select
[tab]End Select

with this:
[tab]Case Else
[tab][tab]Me.Controls("comboboxtool#" & i & "_detail).RowSource = ""
[tab]End Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r6rh]

Ok I figured out the syntax. Now I'm having problems getting the code to run in the right event. I tried using the forms_afterupdate() but it runs the code after you press save. Is their a event that I could use that would trigger after a user enters/changes anything on the form?

 

[PHV]

I'd try the Dirty event.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886