ListBox Not Populating from SQL Statement

[Fattire]

I need a listbox to instantly populate with data after a value is choosen from a combo box, both combo and listbox reside on the same form.

The code below produces no errors, no results in list box.
SQL produces results in an access query so I know it works.

Private Sub cbo_Listing_AfterUpdate()
    
    Dim varName As String
    Dim rec1 As New ADODB.Recordset
    Dim con1 As New ADODB.Connection
    Set con1 = CurrentProject.Connection
    
    sqlstr = "   SELECT tbl_Services.ServicesName " & _
             "     FROM tbl_Programmers, tbl_Services " & _
             "    WHERE tbl_Programmers.ProgrammerID = tbl_Services.ProgrammerID " & _
             "      AND tbl_Programmers.ProgrammerID = " & Me.cbo_Listing.Column(1) & _
             " ORDER BY tbl_Services.ServicesName "
    
    Me.lst_Services.RowSource = sqlstr
    
End Sub

 

[RivetHed]

Why are you respecifying the source every update? If the rowsource for the listbox is:

Select ..... WHERE tbl_Programmers.ProgrammerID=[Forms]![MyForm]![cbolisting]

Then all you need to call on the change event is lst_Services.requery

 

[Fattire]

I did a compact/repair, that fixed it - looks like code wasn't tied to form or something - didn't need to change any code.

I'll look at the requery - might be better then what I'm doing, thanks RivetHed!

 

[jordanking]

requery is better, rivethed is right

.....
I'd rather be surfing