One step forward, two back

[DubboPete]

Hi all,

Millions of searches through the newsgroup didn't answer my question, which
is:

If I click on text0, I want it to populate List6 with all corresponding
entries!

Here's the drum.

tblTeams contains [teamid] and [team]
[teamid] is number
[team] is the Team name

on FrmFixtures I have a [combo10] with value list 1 thru 5 (which indicates
divisions, Division 1, Division 2, etc)

when I select '2' in [combo10] , I would like [List6] to populate with all
teams [team] from tblTeams that are in Div 2 ([teamid] = 2).

if ([combo10] = 2 then
List6 =

Lakers
Wildcats
Glory
United
Tigers

!!!

Please try to be gentle, and not too condescending with your answers -
although I probably deserve it

thanks in anticipation all

The Goat Herder

 

[Allen Browne]

Presumably tblTeams has a field named Division as well?

If so, you can use the AfterUpdate event procedure of the combo to assign
the RowSource property of the list box. Something like this:

Private Sub combo10_AfterUpdate()
Dim strSql As String
If Not IsNull(Me.combo10) Then
strSql = "SELECT [teamid], [team] FROM [tblTeams] " & _
"WHERE [Division] = " & [combo10] & ";"
Me.List6.RowSource = strSql
End If
End Sub


Note: If Division is a Text type field (not a Number type field), you need
extra quotes:
"WHERE [Division] = """ & [combo10] & """;"

 

[DubboPete]

Allen,

I owe you a beer.... or something! ([ps]=1)
(see end of message...)

that works fantastic
and just to clear up the presumption question in your reply...
(for the benefit of others who seek knowledge too)

Presumably tblTeams has a field named Division as well?

yes, it has a Division field, numeric, with values 1 thru 5

chers mate

DubboPete

me.[ps] = 1 "Shepherd Neame's 'Bishops' Finger' or Tetley's Bitter?"

Presumably tblTeams has a field named Division as well?

If so, you can use the AfterUpdate event procedure of the combo to assign
the RowSource property of the list box. Something like this:

Private Sub combo10_AfterUpdate()
Dim strSql As String
If Not IsNull(Me.combo10) Then
strSql = "SELECT [teamid], [team] FROM [tblTeams] " & _
"WHERE [Division] = " & [combo10] & ";"
Me.List6.RowSource = strSql
End If
End Sub


Note: If Division is a Text type field (not a Number type field), you need
extra quotes:
"WHERE [Division] = """ & [combo10] & """;"