Combo box to filter a combo box

[svrich]

Its the same criteria I used to select the row source for the first combo box (Just a different column name). Below is the second combo box's set-up.

Property Sheet > Data > Row Source



SELECT DISTINCT [L_I_GEO].[LONG] FROM L_I_GEO ORDER BY [LONG]

[svrich]

Here's the cascade code. Country is Box 1 of the cascade and Region is Box 2 of the cascade

Private Sub Country_AfterUpdate ()
Dim strSource As String

strSource = "SELECT Long_Name " &_
"FROM L_I_GEO " &_
"WHERE Short_Name = '" & Me.Country & "' ORDER BY Long_Name"
Me.Region.RowSource = strSource
Me.Region = vbNullString
End Sub

[svrich]

Oops. Found a spelling error. That fixes why its showing blank. Still not sure why its not showing the DISTINCT though.

My second box is returning all values associated with the first instead of just a single of each DISTINCT.

[pbaldy]

The DISTINCT is not included in your cascade code.

[svrich]

Where would the DISTINCT go in the cascade code? Would it look like the code below?

Private Sub Country_AfterUpdate ()
Dim strSource As String

strSource = "SELECT Long_Name " &_
"FROM L_I_GEO " &_
"WHERE Short_Name = '" & Me.Country & "' ORDER BY Long_Name"
Me.Region.RowSource = DISTINCT strSource
Me.Region = vbNullString
End Sub

[svrich]

Omg. I feel like an idiot. At least the VBA editor has the compile tool for trial and error. Thank you for all your help Paul.

strSource = "SELECT DISTINCT Long_Name " &_

Quick question

Is there a "count record" (or value) for records meeting a certain criteria?