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
re******@pcdata sheet.com
"Jimbo" <jp******@hotma il.com> wrote in message
news:gU******** *********@twist er.nyroc.rr.com ...

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

[Arno R]

"PC Datasheet" <no****@nospam. spam> schreef in bericht news:zl******** **********@news read3.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
re******@pcdata sheet.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(Cance l 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.Ro wSource = s
End Sub

Private Sub TableList_After Update()
' Populate the FieldList List Box with all field names from the selected
table
Me!FieldList.Ro wSource = Me!TableList
End Sub

Private Sub btnOpenQuery_Cl ick()
' 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("D ynamicQ")

s = "SELECT "
Set ctl = Me!FieldList
For Each Item In ctl.ItemsSelect ed
s = s & "[" & ctl.ItemData(It em) & "], "
Next Item

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

Jimbo wrote:

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

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

[Jimbo]

Thank you Steve! I will give this a try at work tomorrow.
"steve.minn aar" <st*****@concis e.com> wrote in message
news:PZ******** *******@news.us west.net...

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(Cance l 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.Ro wSource = s
End Sub

Private Sub TableList_After Update()
' Populate the FieldList List Box with all field names from the selected
table
Me!FieldList.Ro wSource = Me!TableList
End Sub

Private Sub btnOpenQuery_Cl ick()
' 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("D ynamicQ")

s = "SELECT "
Set ctl = Me!FieldList
For Each Item In ctl.ItemsSelect ed
s = s & "[" & ctl.ItemData(It em) & "], "
Next Item

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

Jimbo wrote:

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

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

[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.minn aar" <st*****@concis e.com> wrote in message
news:PZ******** *******@news.us west.net...

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(Cance l 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.Ro wSource = s
End Sub

Private Sub TableList_After Update()
' Populate the FieldList List Box with all field names from the selected
table
Me!FieldList.Ro wSource = Me!TableList
End Sub

Private Sub btnOpenQuery_Cl ick()
' 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("D ynamicQ")

s = "SELECT "
Set ctl = Me!FieldList
For Each Item In ctl.ItemsSelect ed
s = s & "[" & ctl.ItemData(It em) & "], "
Next Item

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

Jimbo wrote:

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

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

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

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

[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.minn aar" <st*****@concis e.com> wrote in message
news:_V******** *********@news. uswest.net...

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.

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

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

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

[steve.minnaar]

Finally here's the grouping solution (work got in the way of play)

Option Compare Database
Option Explicit

Private Sub Form_Open(Cance l 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.Ro wSource = s
End Sub

Private Sub TableList_After Update()
' Populate the FieldList List Box with all field names from the selected
table
Me!FieldList.Ro wSource = Me!TableList
End Sub

Private Sub btnGroupQuery_C lick()
' 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("D ynamicQ")
If IsNull(Me!Table List) 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.ItemsSelect ed
FieldName = ctl.ItemData(It em)
If Tbl.Fields(Fiel dName).Type > 1 And Tbl.Fields(Fiel dName).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("D ynamicQ")

s = "SELECT "
Set ctl = Me!FieldList
For Each Item In ctl.ItemsSelect ed
s = s & "[" & ctl.ItemData(It em) & "], "
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.)

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