Subform dynamically populated

a_ud · #1 (**permalink**) 01-11-12, 05:52

I've got a subform that falls out of the 'usual categories'.

Pls have a look at the attached DB. It has:

2 main tables, S1 and S2 (3 fields each, TraitA to C for S1, and TraitD to F for S2).
A form with a combo where you select one of the fields (say, Trait_A).
A subform where I want to show all the current values of the chosen field (Trait_A) and the # of occurrences for each value of the field (an example of the needed query -just for TraitA- is included too). That is, a simple Group by query.

Question is: how do you modify dynamically the query that populates the subform? It's easy to define a fixed query that populates the subform, but: How do you modify the data source of the subform so it does use the query for the chosen field on the main form? Through VBA?

Ideally the solution would do 2 things:

1) Define a query for the subform (I guess, through VBA and setting up a Recordsource).
2) Link (or create) the 2 fields in the subform to the said query (one showing the variable, the other showing the # of occurrences).

Any ideas?

Thanks in advance, a.

PS: ideally, the solution would 'build' a query for the subform, not choose a predesigned query (the reason being that the real problem doesn't have 6 fields in total, but hundreds...).

Sinndho · #2 (**permalink**) 01-11-12, 07:05

Quote:

Originally Posted by a_ud

Question is: how do you modify dynamically the query that populates the subform? It's easy to define a fixed query that populates the subform, but: How do you modify the data source of the subform so it does use the query for the chosen field on the main form? Through VBA?

You can create the query that is used as the RecordSource for the form (or change its SQL statement) before opening this form. The following VBA procedure creates a new query in the current database or changes the SQL statement of an existing query:

Code:

ub CreateQuery(ByVal QueryName As String, ByVal SQL As String)

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set dbs = CurrentDb
    For Each qdf In dbs.QueryDefs
        If qdf.Name = QueryName Then Exit For
    Next qdf
    If Not qdf Is Nothing Then
        qdf.SQL = SQL
    Else
        Set qdf = dbs.CreateQueryDef(QueryName, SQL)
    End If
    dbs.QueryDefs.Refresh
    Set qdf = Nothing
    Set dbs = Nothing
        
End Sub

a_ud · #3 (**permalink**) 01-11-12, 10:23

Hi Sinndho,

Thanks, this helps. I'm not completely sure of how it works in detail, but this is sort of what I'm looking for.

Now, the solution I was after was:

Let's say you have 200 vars in table1.
You want to check one by one (values and # of occurrences) using my form.

For this to happen, I'd need the code to generate a SQL string and populate the subform with something like:

SELECT Field1, Count(Field1) FROM table1 GROUP BY Field1

but instead of Field1 use sth like: Me.comboName

Would your code do that? Problem I see is that is feasible to 'change' dynamically from one query to another (and populate the subform with it). But still would have to design previously 200 queries (all exactly the same!) to actually see the results in the subform..... And one more question: should this code be added to the AfterUpdate event of the top combo?

Thanks in advance, a.

Sinndho · #4 (**permalink**) 01-11-12, 12:15

Quote:

Originally Posted by a_ud

But still would have to design previously 200 queries (all exactly the same!) to actually see the results in the subform

Precisely not: There is one and only one query but its SQL statement (i.e. the data set it yields) changes on demand. If the names of the columns are subject to change, you can use aliases (that remain the same), so that the names of the fields in the resulting Recordset never change.

Using this technique, you do not need to change anything in the "target" form. You simply modify the SQL statement of the query. It can even be done while the "target" form is open. In such a case all you need to do is to requery the form:

Code: