accessing a subform's current set of records

[nishantd]

Hello,

Is it possible to assign a subform's current recordset to a listbox or combo box?

For example, there is a main form that contains a combo or listbox. An item is selected, and then the corresponding subform is populated with those records associated to the item in the main form (simple relationship). I essentially just want to specify the rowsource for the combo or listbox on the subform, to be the current set of records on the subform.

Additional notes:
I know you can use the wizard to create a combo or listbox that "finds a record based on what I select", however, the items on the subform change as an item on the main form is selected.

Thanks,
Nishant

 

[dcx693]

Just use some code in the AfterUpdate event of the combo box on the main form to set the RecordSource of the subform. Setting the rowsource of a combo box on the subform is a bit more complicated depending on what you want to set it to (and what version of Access you're running). Can you give an example?

 

[nishantd]

Here's an example:

I'm running Access 2000 that is connected to a SQL Server backend (ver 8.x) that houses all tables.

There is a one to many relationship between items on the main form and items on the sub form.

It used to be the case in an MDB setting, you can specify form elements in the Access Queries, should you require dynamic query strings. In this case, you can specify "[Forms]![form1]![item]" in a query and specify that query as the listbox's RowSource. However, it seems (seems to me at least) that in an ADP setting, you can't quite do it that way since all data is housed on the SQL server.

So I've created a stored procedure to take care of this. The sub form still pulls the appropriate "many" records based upon the "one" record selection on the main form. However, there is now the problem of updating the listbox on the subform to reflect these "many" records (i believe this could be done using a stored procedure and populating the listbox with the results from this stored procedure).

If I could use the current set of records as a source for the list box, then the code in the After_Update() method from the main form should work just fine.

 

[dcx693]

I honestly don't work with ADP files, so I can't help you there. I don't see why the reference to a form parameter wouldn't work, though - it's just searching for a local value in a form.

You can get at a form's (or subform's) recordsource using the Forms("formname").RecordSource property. However, you cannot directly assign the recordsource to a listbox (at least, not in Access 2000). You can assign the SQL string, however to the rowsource property. Perhaps that will work.