populate list box dynamically from selected value

[ugly]

My form has two combo boxes, combo6 and combo7. Combo6 contains a list of values when the user clicks an entry the following code runs. I have previously created a stub query called duff. All this works fine the query opens with the correct returned results. What I would like to do ideally is when a selection is made in combo6, the query fires and the returned values populate combo7. I am not sure how to associate the returned values with combo7. Thanks for any help.

Private Sub Combo6_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("duff")
Dim strSQL As String
strSQL = Me.Combo6.Column(0)
strSQL = "SELECT Table1.name " & _
"FROM Table1 " & _
"WHERE Table1.name = '" & Me.Combo6.Column(0) & "'" & _
"ORDER BY Table1.order;"
qdf.SQL = strSQL
DoCmd.OpenQuery "duff"
End Sub

 

[genomon]

You could open a recordset on the query (select * from duff...) and loop through it, adding each data item to the other list box.

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[TheAceMan1]

How are ya ugly . . .

Try this in the [blue]AfterUpdate[/blue] event of combo6:

[blue]   Me.Combo7.RowSource "SELECT Name FROM Table1 " & _
                       "WHERE [Name] = '" & Me.Combo6 & "'" & _
                       "ORDER BY Table1.order;"[/blue]

Ultimately you can set the RowSource of Combo7 to the SQL and just requery combo7 from combo6 . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884: