Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2011
    Posts
    8

    Unanswered: Subform dynamically populated

    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...).
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by a_ud View Post
    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
    Have a nice day!

  3. #3
    Join Date
    Jun 2011
    Posts
    8
    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.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by a_ud View Post
    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:
    Forms("TargetFormName").Requery
    Have a nice day!

  5. #5
    Join Date
    Jun 2011
    Posts
    8
    I've implemented your suggestion and it works, thanks very much.

    I upload the file again (now with the changes) in case anyone wants to use it.
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •