On-the-fly subform

[Dugantrain]

I have a form which accepts user-input and builds a pass-through query and its SQL based on parameters that the user inputs. If the query is successful, it pulls the results into a table. What I would like now is a subform datasheet with which to view those results (a report won't cut it as the data needs to be updateable). I just realized that I'll have to make this subform on the fly as there is no way to tell ahead of time how many fields the table will contain or what those fields would be. So, if anyone has done something odd like this before, take me through the steps to build a form, populate it with text boxes, and bind the boxes to the control source. Or, if you prefer, how to build a form and I'll figure out the rest.

 

[dcx693]

My suggestion: present your search results in a subform - but in datasheet view only. It would be unmanageable to create a subform in "single form" view. There are too many variables (IMHO).

With datasheet view, you can have as many fields as you want in your dynamic query. True, you won't have the functionality of combo boxes etc.... If you must have those, then create a subform with all the fields you want and just set your query results to be the subform recordsource.

 

[Dugantrain]

Right, that's my intention, but I still have to create that datasheet on the fly as I won't know my fields until creation.

 

[dcx693]

It's easier than you think

Right, that's my intention, but I still have to create that datasheet on the fly as I won't know my fields until creation.

Yes, that is a problem. I always have a set choice fields in my datasheets. How many fields do you have? Maybe place all fields into the datasheet, pre-select certain ones to be visible, then allow the user to fine-tune that selection?

 

[Dugantrain]

Well, by playing around with it, I've gotten:

Public Function fCreate_Frm()
Dim frm As Form
Dim subfrm As Control
Set frm = CreateForm()
With frm
    .Caption = "My Form"
    .RecordSource = "qryNDRS_Results"
    .DefaultView = 2
End With
DoCmd.Save
DoCmd.Close acForm, "form1", acSaveYes
Set frm = Nothing
DoCmd.OpenForm "frmResults_Container", acDesign
Set subfrm = CreateControl("frmResults_Container", acSubform, acDetail)
subfrm.SourceObject = "form1"
Set subfrm = Nothing
End Function

which basically creates a subform and puts it on the frmResults_Container Parent form. Now, it's just a matter of accessing the record source (a query), looping through the fields, creating a number of bound controls equal to the number of fields, and binding the controls. Luckily, since it's a Datasheet, I won't have to worry too much about formatting. Oh yeah, I'll also have to kill the subform every time the Parent is closed so that this code can be run every time the Parent is opened without error. I can't seem to rename the form after creation from "form1", though; odd that that would be the tricky part!

 

[dcx693]

Why do you need to delete the subform? You can save a form with a subform even if the subform has a blank source object property. You can then use this line of your code:
subfrm.SourceObject = "form1"
to set the source.

 

[Dugantrain]

You're right; I meant the SourceObject form will need to be destroyed and recreated.

 

[Dugantrain]

ok, this really wasn't that difficult, I tooled around for a couple of hours and got it working. I'll post the code and some keywords if, on some distant day, some other egghead thinks that this would be a good idea:

Option Compare Database
Option Explicit

Public Function fCreate_subfrm()
Dim frm As Form
Dim subfrm As Control
Call fDelete_Form 'If subform already exists, delete it
Set frm = CreateForm() 'Create subform in memory
With frm
    .Caption = "My Form"
    .RecordSource = "qryNDRS_Results"
    .DefaultView = 2 'Datasheet View
End With
Call fGet_Result_Columns 'Determine which fields will be populating the subform
DoCmd.Save
DoCmd.Close acForm, "form1", acSaveYes 'Needs to be unloaded to place it on the parent form
Set frm = Nothing
DoCmd.OpenForm "frmResults_Container", acDesign 'Open parent form
Set subfrm = Forms!frmResults_Container!subfrmResults
subfrm.SourceObject = "form1"
DoCmd.Save
Set subfrm = Nothing
End Function

Public Function fGet_Result_Columns()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qry As DAO.QueryDef
Dim fldcount, i As Long
Set db = CurrentDb
Dim ctl As Control
Set rs = db.OpenRecordset("SELECT TOP 1 * FROM qryNDRS_Results") 'Get a small recordset
fldcount = rs.Fields.Count                                       'so that we can Count
For Each qry In db.QueryDefs                                     'Column Headings
    If qry.Name = "qryNDRS_Results" Then  'Find the query, get the Column Names
        For i = 0 To (fldcount - 1)
            Set ctl = CreateControl("form1", acTextBox, acDetail, , _
                db.QueryDefs("qryNDRS_Results").Fields(i).Name) 'Create controls equal to
            ctl.Name = db.QueryDefs("qryNDRS_Results").Fields(i).Name 'Number of query fields
            ctl.Visible = True
        Next
    End If
Next
Set ctl = Nothing
Set rs = Nothing
Set db = Nothing
End Function

Public Function fDelete_Form() 'Find subform if it exists by looping through
Dim frm As Object              'the forms collection and then delete it
For Each frm In Application.CurrentProject.AllForms
    If frm.Name = "Form1" Then
        DoCmd.DeleteObject acForm, "Form1"
        Exit For
    End If
Next
Set frm = Nothing
End Function

*keywords*
dynamic subform
dynamic form
on the fly
on-the-fly