Try again! Combobox list from table result of another combobox

[hunterjj]

I have a table of golf courses. Each table entry contains room for up to 3 tee boxes(ie. Blue, White, Red). I use a combobox to select a particular golf course. In the next combobox, I want to be able to select which one of the three tee boxes will be used. Note: These colors are not fixed, so I can't use a value list. They are variable, so I need to extract the colors from the table fields and display them in a combobox so I can select a single one.

 

[allanon]

Ok. I did what you asked for:

I created the following table:

GolfCourse

Key GlofCourse Tee1 Tee2 Tee3
1 Course1 Red Blue White
2 Course2 Green Yellow Purple

I created a form with two combo boxes:

Combo0 and Combo2

Combo0 is the golf courses. Make sure you have a unique key in you golf course table (mine is Key) and then use the combo wizard to set it up.

In the change event for Combo0 I placed this code:

Dim strSQL As String

strSQL = "Select GolfCourse.Tee1 FROM GolfCourse "
strSQL = strSQL & "WHERE GolfCourse.Key = " & Me.Combo0.Value & " "
strSQL = strSQL & " UNION "
strSQL = strSQL & "Select GolfCourse.Tee2 FROM GolfCourse "
strSQL = strSQL & "WHERE GolfCourse.Key = " & Me.Combo0.Value & " "
strSQL = strSQL & " UNION "
strSQL = strSQL & "Select GolfCourse.Tee3 FROM GolfCourse "
strSQL = strSQL & "WHERE GolfCourse.Key = " & Me.Combo0.Value & " "

Me.Combo2.RowSource = strSQL

That's all there is to it. If you need more help let me know.

 

[JimboSHU]

Ok I have a similar table; just sub Golf course for Pline and Tee1, etc. for Finish1-Finish 6 and ID instead of Key; used the code you posted with my information and keep on getting a syntax error. The name of the table is MProductLines2
Here is the code:
Dim strSQL As String

strSQL = "Select MProductLines2.Finish1 FROM MProductLines2 "
strSQL = strSQL & "WHERE MProductLines2.ID = " & Me.Combo0.Value & " "
strSQL = strSQL & " UNION "
strSQL = strSQL & "Select MProductLines2.Finish2 FROM MProductLines2 "
strSQL = strSQL & "WHERE MProductLines2.ID = " & Me.Combo0.Value & " "
strSQL = strSQL & " UNION "
strSQL = strSQL & "Select MProductLines2.Finish3 FROM MProductLines2 "
strSQL = strSQL & "WHERE MProductLines2.ID = " & Me.Combo0.Value & " "
strSQL = strSQL & "Select MProductLines2.Finish4 FROM MProductLines2 "
strSQL = strSQL & "WHERE MProductLines2.ID = " & Me.Combo0.Value & " "
strSQL = strSQL & "Select MProductLines2.Finish5 FROM MProductLines2 "
strSQL = strSQL & "WHERE MProductLines2.ID = " & Me.Combo0.Value & " "
strSQL = strSQL & "Select MProductLines2.Finish6 FROM MProductLines2 "
strSQL = strSQL & "WHERE MProductLines2.ID = " & Me.Combo0.Value & " "

Me.Combo2.RowSource = strSQL

Any help would be appreciated!

 

[hunterjj]

The reason for the syntax error is that the initial response dealt with numberic keys. To support alpha keys you have to enclose the Me.Combo.Value in single quotes.
Do this by inserting a single quote before the double quote which is before and after the Me.Combo.Value.

 

[JimboSHU]

If what you're referring to is my ID field data, the data is numeric just like your Key field. I'll try what you said anyway though. Thanks.

 

[allanon]

Also forgetting the keyword UNION after the 3rd, 4th, and 5th select statement

 

[JimboSHU]

Got it to work, it was the missing UNIONs. I feel like an idiot but that's what you get for cutting and pasting! Thanks.