2 VBA questions - how display data from dynamically built(sql) que

I want to display all the records from a select query in a "window". The
user will only be allowed to view the results - not to change them.

The query selects several fields from a table in the mdb.

The user needs to have the flexibility to vary the list of the fields to be
retrieved by the query. So, I have to build the query "dynamically" -
building a form containing controls that will allow the user to
specify which optional fields he wants to retrieve. No problem there.

Then, when the user
clicks the "retrieve" button that I put on the form, using VBA access code, I
dynamically build the SQL query and retrieve the recordset containing the
matching records with the fields specified. That works fine.

Then, I'm stuck.

The user needs to be able to view the recordset's data in two optional ways:
1- in tabular form (fields left to right, records up and down)
or
2- in a pivot chart view.

I don't know how to:
A- "open" a window (a form?) and then
B- how to fill it with either
the tabular view data or the pivot chart view data.

Any help is GREATLY appreciated.

Here's my code that retrieves the recordset.

Dim dbs As database
Dim strSQLsearch As String
Dim rst As DAO.Recordset
Set dbs = CurrentDb
..
..
strSQLsearch = "SELECT tbl05_06Data.Dept, tbl05_06Data.[YR/QTR], & _
Count(tbl05_06Data.TAN) AS CountOfTAN " & _
"FROM tbl05_06Data " & _
"WHERE(((tbl05_06Data.AuditType) = ""CA"" Or (tbl05_06Data.AuditType) =
""DA"") " & _
"And ((tbl05_06Data.[Std3-Sec/X]) = 0)) " & _
"GROUP BY tbl05_06Data.Dept, tbl05_06Data.[YR/QTR];"

Set rst = dbs.OpenRecordset(strSQLsearch)

cross-posted on Access programming & forms groups

Instead of opening a recordset, apply the sql to the recordsource of a
form or report. Format it anyway desired and if in form, turn off all
ability to edit, add or delete.

A Flex Grid Control will probably do what you want. Have a look at -

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex%20Grid%20Demo.m db'

for some examples.

Peter Hibbs.

On Fri, 23 Mar 2007 09:01:52 -0700, fry2
wrote:

I want to display all the records from a select query in a "window". The
user will only be allowed to view the results - not to change them.

The query selects several fields from a table in the mdb.

The user needs to have the flexibility to vary the list of the fields to be
retrieved by the query. So, I have to build the query "dynamically" -
building a form containing controls that will allow the user to
specify which optional fields he wants to retrieve. No problem there.

Then, when the user
clicks the "retrieve" button that I put on the form, using VBA access code, I
dynamically build the SQL query and retrieve the recordset containing the
matching records with the fields specified. That works fine.

Then, I'm stuck.

The user needs to be able to view the recordset's data in two optional ways:
1- in tabular form (fields left to right, records up and down)
or
2- in a pivot chart view.

I don't know how to:
A- "open" a window (a form?) and then
B- how to fill it with either
the tabular view data or the pivot chart view data.

Any help is GREATLY appreciated.

Here's my code that retrieves the recordset.

Dim dbs As database
Dim strSQLsearch As String
Dim rst As DAO.Recordset
Set dbs = CurrentDb
.
.
strSQLsearch = "SELECT tbl05_06Data.Dept, tbl05_06Data.[YR/QTR], & _
Count(tbl05_06Data.TAN) AS CountOfTAN " & _
"FROM tbl05_06Data " & _
"WHERE(((tbl05_06Data.AuditType) = ""CA"" Or (tbl05_06Data.AuditType) =
""DA"") " & _
"And ((tbl05_06Data.[Std3-Sec/X]) = 0)) " & _
"GROUP BY tbl05_06Data.Dept, tbl05_06Data.[YR/QTR];"

Set rst = dbs.OpenRecordset(strSQLsearch)

cross-posted on Access programming & forms groups