Finally here's the grouping solution (work got in the way of play)
Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
' Populate the TableList List Box with all table names
Dim db As Database, Tbl As TableDef
Dim s As String
Set db = CurrentDb
s = ""
For Each Tbl In db.TableDefs
With Tbl
If Left$(.Name, 4) = "MSys" Then
' Skip system tables
Else
s = s & .Name & ";"
End If
End With
Next Tbl
Me!TableList.RowSource = s
End Sub
Private Sub TableList_AfterUpdate()
' Populate the FieldList List Box with all field names from the selected
table
Me!FieldList.RowSource = Me!TableList
End Sub
Private Sub btnGroupQuery_Click()
' Build the SQL to display the selected fields
' Create DynamicQ manually as, say, SELECT ID FROM Table1; and save it.
' This code will replace the SQL with each usage.
Dim db As Database, qd As QueryDef, ctl As Control, s As String, Item As
Variant
Dim Tbl As TableDef, Fld As Field, FieldName As String, Tail As String
Set db = CurrentDb
Set qd = db.QueryDefs("DynamicQ")
If IsNull(Me!TableList) Then
MsgBox "No table selected"
Else
Set Tbl = db.TableDefs(Me!TableList)
' Field Data type values
' 1 dbBoolean Boolean
' 2 dbByte Byte
' 3 dbInteger Integer
' 4 dbLong Long
' 5 dbCurrency Currency
' 6 dbSingle Single
' 7 dbDouble Double
' 8 dbDate Date / Time
' 9 dbBinary Binary
' 10 dbText Text
' 11 dbLongBinary Long Binary (OLE Object)
' 12 dbMemo Memo
' 15 dbGUID Guid
' 16 dbBigInt Big Integer
' 17 dbVarBinary VarBinary
' 18 dbChar Char
' 19 dbNumeric Numeric
' 20 dbDecimal Decimal
' 21 dbFloat Float
' 22 dbTime Time
' 23 dbTimeStamp Time Stamp
' Note that numeric types range from 2 to 7
s = "SELECT "
Tail = " GROUP BY "
' SELECT SalesPerson, Region, Sum(Amount) AS SumOfAmount
' FROM Sales
' GROUP BY SalesPerson, Region;
Set ctl = Me!FieldList
For Each Item In ctl.ItemsSelected
FieldName = ctl.ItemData(Item)
If Tbl.Fields(FieldName).Type > 1 And Tbl.Fields(FieldName).Type
< 8 Then
s = s & "Sum([" & FieldName & "]) AS [SumOf" & FieldName &
"], "
Else
s = s & "[" & FieldName & "], "
Tail = Tail & "[" & FieldName & "], "
End If
Next Item
If s = "SELECT " Then
MsgBox "No fields selected"
Else
s = Left$(s, Len(s) - 2) & " FROM [" & Me!TableList & "]"
If Tail <> " GROUP BY " Then s = s & Left$(Tail, Len(Tail) - 2)
qd.SQL = s & ";"
DoCmd.OpenQuery "DynamicQ"
End If
End If
End Sub
Private Sub btnSelectQuery_Click()
Dim db As Database, qd As QueryDef, ctl As Control, s As String, Item As
Variant
Set db = CurrentDb
Set qd = db.QueryDefs("DynamicQ")
s = "SELECT "
Set ctl = Me!FieldList
For Each Item In ctl.ItemsSelected
s = s & "[" & ctl.ItemData(Item) & "], "
Next Item
If s = "SELECT " Then
MsgBox "No fields selected."
Else
qd.SQL = Left$(s, Len(s) - 2) & " FROM [" & Me!TableList & "];"
DoCmd.OpenQuery "DynamicQ"
End If
End Sub
Replace all the code in the original example with this.
Jimbo Wrote:[color=blue]
> One last question for you - is it possible to do this
> with a 'group by' query instead of a 'select' query?
> The sales folks would like their sales units and gross
> sales in every query and summed up (the dynamic parts
> would still be dynamic - sales by customer, territory,
> product, etc.)[/color]
*** Sent via Developersdex
http://www.developersdex.com ***
