subform combobox rowsource update - how update drop down list

Question

I have a treeview control on an MS Access form. A subform combo box control's list of values depends on the node selected in the treeview on the main form. I am trying to achieve refresh of the combo box drop down list contents on the subform as follows:

Public Sub TreeView1_nodeClick(ByVal node As Object)
    subForm.Controls("Bid").RowSource = "... newquery depending on tree node values ..."
    subForm.Controls("Bid").Requery
End Sub

But oddly enough, this does not update the list of values. A second click on the same node does update the list to the expected values; when another node is clicked the list again is wrong (it contains the list related to the next to last assigned rowsource, instead of related to the last one assigned).

Is there some delay in activating the combo box' rowsource?
What is the solution for this obnoxious problem?

Maestro13 Maestro13 · Accepted Answer · 2012-03-06T20:16:12

OK found a solution for this myself - I don't particularly like it (*), but it does work, so I regard it as a temp workaround, until somebody else provides a solution using the royal route (setting rowsource and somehow make this have the desired effect).

I checked msdn site and found that it is also possible to use a self-defined function for the combobox list population. The easy part of this is the specification of the fact that Access will have to use that function: just enter the function name in the row Source Type property of the combo box, just the plain function name, without any = in front or () behind it.
Now for the less easy part - MS requires a specific format for the function and a specific content. Mine looks as follows:

Private Function customFuncName(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(... new query ...)

    Select Case code
        Case acLBInitialize
            customFuncName = True
        Case acLBOpen
            customFuncName = 1
        Case acLBGetRowCount
            customFuncName = rs.RecordCount
        Case acLBGetColumnWidth
            customFuncName = -1
        Case acLBGetValue            
            customFuncName = rs.GetRows(rs.RecordCount)(col, row)
    End Select

End Function

See msdn.microsoft.com rowSourceType property and the link specific function code arguments on it.

(*) because of the obvious recordset overhead causing performance to drop - but since the list I show is max 40 rows it works fine for me

subform combobox rowsource update - how update drop down list

1 Answers