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

[Guest]

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

 

[Alex Dybenko]

Hi,
once you have built strSQLsearch - you can just assign Form's recordsource
to it - and from will be populated.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

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