Dynamic Query via User-Specified Fields

[Jimbo]

Hello,

I have a user request to build a form in an Access database where the user
can check off specific fields to pull in a query. For example, let's say I
have 10 fields in a table. The user wants to be able to check off anywhere
between 1 and all 10 fields in a form and have it return a select query with
just the fields that were checked off. There are multiple users, so not all
users will be checking off the same fields. Some users will want 3 fields,
others will want 2 and they could all be different.

Thanks,
Jim

[PC Datasheet]

Look up QueryDef in the Help file. You can use a multiselect listbox to
select the fields. From the fields selected in the listbox, build a dynamic
SQL string. Using the QueryDef object, associate the SQL string with the
stored query that is the recordsource for the form.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1100 users have come to me from the newsgroups requesting help
reso...@pcdatasheet.com

"Jimbo" <jpet...@hotmail.com> wrote in message
news:gUdFf.9139$1N5....@twister.nyroc.rr.com...

[Arno R]

"PC Datasheet" <nos...@nospam.spam> schreef in bericht news:zleFf.12470$vU2....@newsread3.news.atl.earthlink.net...

> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> Over 1100 users have come to me from the newsgroups requesting help
> reso...@pcdatasheet.com

--
To Steve:
Over 350 users from the newsgroups have visited the website to read what kind of a 'resource' you are...

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html (updated)

Arno R

[steve.minnaar]

Jim

I've taken it a little further. You can select a table then select the
fields to display as follows:

Create a form with two List Boxes
TableList (Row Source Type - Value List, Multi Select - None)
FieldList (Row Source Type - Field List, Multi Select -
Simple)
A Command Button, btnOpenQuery

Create a Query - DynamicQ "SELECT ID FROM Table1;" and save it. The VBA
code will modify this with each usage.

===Code behind the form===
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 btnOpenQuery_Click()
' Build the SQL to display the selected fields

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

qd.SQL = Left$(s, Len(s) - 2) & " FROM " & Me!TableList & ";"
DoCmd.OpenQuery "DynamicQ"
End Sub

> Thanks,
> Jim

*** Sent via Developersdex http://www.developersdex.com ***

[Jimbo]

Thank you Steve! I will give this a try at work tomorrow.

"steve.minnaar" <ste...@concise.com> wrote in message
news:PZrFf.42$sN4....@news.uswest.net...

[Jimbo]

Steve, THANK YOU!!!!! This worked exceptionally well! I built the form and
the query, then copied your code into the VB editor for the form. It worked
immediately! Thanks very much again!

I actually modified it very slightly. I really only need one table to be
shown, so I put the name of the table into the code and removed the
instances for all tables where necessary.

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

"steve.minnaar" <ste...@concise.com> wrote in message
news:PZrFf.42$sN4....@news.uswest.net...

[steve.minnaar]

Jimbo Wrote:
> 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.)

I'm not sure what you need. If your sales table was like the simple
example below:

SalesPerson Region Grade Amount
John North A 10
John North B 11
John South A 12
John South B 13
John West A 14
John West B 15
Mary North A 10
Mary North B 11
Mary South A 12
Mary South B 13
Mary West A 14
Mary West B 15

Should SalesPerson by Grade produce?

SalesPerson Grade Amount Total
John A 36
John B 39
Mary A 36
Mary B 39

This would require distinguishing fields which can be Summed, Averaged
etc. from those which cannot like text fields. A Check Box on the form
would indicate whether the query should return detailed or summary
values. I can do this.

If the requirement is more complex then the simple solution might be to
teach your sales staff enough Access to build their own queries.

[Jimbo]

Your example is exactly right Steve. If it's too complex or too much
trouble, I'll pursue the idea of teaching the sales staff some basic
querying. I actually considered that earlier as the form already returns a
query that they can edit, but figured I'd run it by you first in case it
wasn't too complex.

Thanks again for all of your help and advice!!

"steve.minnaar" <ste...@concise.com> wrote in message

news:_VVFf.610$nt5....@news.uswest.net...

[steve.minnaar]

Jimbo, I'll take it to the next stage. It's an interesting problem and
should be useful.

Jimbo Wrote:
> Your example is exactly right Steve. If it's too complex
> or too much trouble, I'll pursue the idea of teaching the
> sales staff some basic querying. I actually considered
> that earlier as the form already returns a query that
> they can edit, but figured I'd run it by you first in
> case it wasn't too complex.

[steve.minnaar]

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:
> 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.)