Dynamic Subform control source (1 Viewer)

[DCrake]

I have a main form that contains a sub form and a list box, the list box will contain a list of tables from a database selected by the user.

When the user clicks on a table in the list box I want the subform to show the contents of the chosen table in datasheet view.

The issue I have is I need to set the subforms control source on the fly. I cannot prepopulate the subform with fields as I do not know what the name of the table is going to be and how many and what type of fields are going to be populated.

I have got this working by populating a listbox dynamically but I really want more flexibility with the column widths etc.

I can populate a third party ocx listbox but the end result is going to be posted here as part of a database comparison wizard and as such do not want to deploy 3rd party ocx's.

Tried Microsoft Flexgrid but Access 2007 do not allow me to reference it as it states it does not support it in this version. Another solution is to use a VSFlexgrid but again I really want to keep it purely in Access.

Does anyone have a solution that would enable me to feed control source information to an empty subform?

 

[JamesMcS]

I did this simply in VBA a while ago - the only problem was the order that you had to change the link fields in, I think it went

subform.linkmasterfields=""
subform.linkchildfields=""
subform.sourceobject=[new sub name]
subform.linkmasterfields=[new link field]
subform.linkchildfields=[new link field]

Otherwise you'd get a "setting isn't valid for this property" when the subform object changed. Although, I did do this with forms, not tables....

 

[DCrake]

That's no longer an issue now as I have hit another brick wall I have managed to create a dummy form with 25 unbound controls and when the user selects the table it set the subforms recordsoure to the selected table. It then enumerates throught the textboxs on the form and sets the control source to the first 25 fields in the table, less if fewer than 25 field in table.

You would think Yeah Solved!!!! No!!!!! The issue is that the mdb that is the source of the table is not a linked table, simply an OpenDatabase() Recordset().
So when you try to apply the RS or the Table name to the Subforms Recordsource Access cannot find the table. Which is correct.

So my question is how do your bind controls to a disconnected mdb?

Set TmpDb = OpenDatabase(obDB.Name)
Set rstemp = TmpDb.OpenRecordset(sstr)

    
    Forms!FrmMain!FrmTableSubForm.Form.RecordSource = [B]rstemp[/B]

    For x = 0 To fldCnt
        Forms("FrmTableSubForm")("Field" & x + 1).ControlSource = rstemp(x).Name
        Forms("FrmTableSubForm")("Field" & x + 1 & "_Label").Caption = rstemp(x).Name
        Forms("FrmTableSubForm")("Field" & x + 1).Visible = True
        
        If x > 25 Then Exit For
    Next

 

[JamesMcS]

Bit inexperienced with DAO, have just begun scratching the surface... can you debug.print rstemp, and have it come back with something that's valid for the recordsource property? Something about tabledefs is ringing a tiny bell in my tiny mind... I'll do some googling and see if anything comes up

 

[boblarson]

I don't have experience using it (you might try Banana as I think he's done it or knows how) - but you can use an ADO recordset as a disconnected recordset.

 

[DCrake]

I have got around it in a way I did not want to go, but it works.

What happens is when the user selects a table from the list it links it to the front end. then it can be referred to locally. When a new table is selected it drops the link to the earlier one. Thi smeans there will only ever be one linked table at any one time.

 

[Banana]

If you have a recordset, then all you have to do is:

Set Me.Recordset = rs

(This works whether rs is an DAO or ADO recordset). You can then bind a form to a source that wasn't linked.

As for controlsource themeselves... I'm not clear on whether when you change source, do you have same columns or they'd be named differently?

Two approaches to solve this problem is to either write a common query:

SELECT ... AS myColumn1, ... AS myColumn2, ... AS myColumn3
FROM myTable

then have your controls' controlsource set to "myColumnX"

or as you already done, loop through Recordset's fields and assign the field name to the control's controlsource.

 

[DCrake]

Banana

are you saying that

Forms!FrmMain!FrmTableSubForm.Form.RecordSource = RsTemp

should be

set Forms!FrmMain!FrmTableSubForm.Form.Recordset = RsTemp

 

[Banana]

Yes, that'd work too.

 

[DCrake]

Tried that but got the error msg

The Object you entered is not a valid Recordset Property

Dim rstemp As DAO.Recordset
Set TmpDb = OpenDatabase(obDB.Name)
Set rstemp = TmpDb.OpenRecordset(sstr)

Set Forms!FrmMain!FrmTableSubForm.Form.Recordset = rstemp

 

[Banana]

The error is basically saying it's not right kind of recordset - Since you're opening a recordset from a db opened in OpenDatabase, it may be actually a Table-Type Recordset - only acceptable DAO recordset to bind to form is Dynaset. (Maybe snapshot but I've not tried it before).

Try this

Dim rstemp As DAO.Recordset
Set TmpDb = OpenDatabase(obDB.Name)
Set rstemp = TmpDb.OpenRecordset(sstr, dbOpenDynaset)

Set Forms!FrmMain!FrmTableSubForm.Form.Recordset = rstemp

 

[DCrake]

That worked Fine, Thanks Banana

 

[stopher]

I can populate a third party ocx listbox but the end result is going to be posted here as part of a database comparison wizard and as such do not want to deploy 3rd party ocx's.

Is the Listview activeX any good to you. I think it is standard with Windows but I'm not 100% sure.

Chris