I have a form that selects a client from a combo box.  There is a sub-form 
with a tree list control that I want to populate info applicable to the 
client selected by the user.  I have the the following code in the Form Load 
event to do this, but it is not working:

    'Build recordset for Categories (from a pre-selected client) at top level
  SQL = "SELECT Categories.catID, Categories.pID, Categories.catName " & _
        "FROM Categories " & _
        "WHERE Categories.pID =  [Forms]![frmComponents]![cbopID] " & _
        "ORDER BY Categories.catName"

Does the problem reside in the code or the event that handles the code?



-- 
Kro

Hi,

Is that al the code you have?
Where do you assign the SQL to the sub form RecordSource?

Solution:
Just base the RecordSource of the sub form with this SQL without using the 
code on the OnLoad event, 
and on the after update event of the combo "cbopID" run the code

Me.[SubFormControlName].Requery

to refresh the data

-- 
Good Luck
BS"D


"Kro" wrote:

> I have a form that selects a client from a combo box.  There is a sub-form 
> with a tree list control that I want to populate info applicable to the 
> client selected by the user.  I have the the following code in the Form Load 
> event to do this, but it is not working:
> 
>     'Build recordset for Categories (from a pre-selected client) at top level
>   SQL = "SELECT Categories.catID, Categories.pID, Categories.catName " & _
>         "FROM Categories " & _
>         "WHERE Categories.pID =  [Forms]![frmComponents]![cbopID] " & _
>         "ORDER BY Categories.catName"
> 
> Does the problem reside in the code or the event that handles the code?
> 
> 
> 
> -- 
> Kro

The 'Public Sub Polulate Tree ()' is called at 'Private Sub Form_Load()' & at 
'Private Sub fmeView_AfterUpdate()'.  Here is more code used to populate the 
tree list control:

Public Sub PopulateTree()
  
  Dim rstCategory As New ADODB.recordset, rstComponent As New ADODB.recordset
  Dim rstSubComponent As New ADODB.recordset
  Dim tvwTree As Object
  Dim nodX As Node
  Dim I As Integer
  Dim blnAllRecs As Boolean
  Dim SQL As String
  
  Set tvwTree = Me.tvwItems
  
  'Renumber all items
  RenumberItems
  
  'Clear out all current items
  tvwTree.Nodes.Clear
  
  'Set blnAllRecs to use in query building
  If Me.fmeView = 1 Then
    blnAllRecs = False
  Else
    blnAllRecs = True
  End If
  
    'Build recordset for Categories (from a pre-selected client) at top level
  SQL = "SELECT Categories.catID, Categories.pID, Categories.catName " & _
        "FROM Categories " & _
        "WHERE Categories.pID =  '[Forms]![frmComponents]![txtpID] " &
        "ORDER BY Categories.catName"

  rstCategory.Open SQL, CurrentProject.Connection, adOpenStatic, 
adLockReadOnly
Note- Code stops running here!  

  If rstCategory.BOF And rstCategory.EOF Then
    'Do Nothing
  Else
    rstCategory.MoveFirst
    Do Until rstCategory.EOF
      tvwTree.Nodes.Add , , "Cat " & CStr(rstCategory("catID")), 
BuildItemString(True, rstCategory("catID"))

Note- More code adds any child nodes for components & subcomponents.  The 
recordsets are then closed & set to nothing.



-- 
Kro


"Ofer Cohen" wrote:

> Hi,
> 
> Is that al the code you have?
> Where do you assign the SQL to the sub form RecordSource?
> 
> Solution:
> Just base the RecordSource of the sub form with this SQL without using the 
> code on the OnLoad event, 
> and on the after update event of the combo "cbopID" run the code
> 
> Me.[SubFormControlName].Requery
> 
> to refresh the data
> 
> -- 
> Good Luck
> BS"D
> 
> 
> "Kro" wrote:
> 
> > I have a form that selects a client from a combo box.  There is a sub-form 
> > with a tree list control that I want to populate info applicable to the 
> > client selected by the user.  I have the the following code in the Form Load 
> > event to do this, but it is not working:
> > 
> >     'Build recordset for Categories (from a pre-selected client) at top level
> >   SQL = "SELECT Categories.catID, Categories.pID, Categories.catName " & _
> >         "FROM Categories " & _
> >         "WHERE Categories.pID =  [Forms]![frmComponents]![cbopID] " & _
> >         "ORDER BY Categories.catName"
> > 
> > Does the problem reside in the code or the event that handles the code?
> > 
> > 
> > 
> > -- 
> > Kro