data report

[anne]

Hi,

I have an access table including these fields:

Name TestName Results%

I would like to produce the following report:

TestName1 TestName2 TestName3...
Name1 % % %
Name2
Name3
....
...

This can be produced easily in Access using a crosstab query but when trying
this with VB the fun begins!

I can copy the SQL into a command in a data environment and use this to produce
the data report, which is fine.
But the two major problems I just can't get around are:
1) I don't know until run-time which names I want in the report.
2) I don't know until run-time which tests to include in the report.

any idea as to how to get round this
Thanks

Anne

[Alder]

I know no way of putting variables into the data environment queries on the fly so I would go around the data environment for what you are trying to do.

If you want to display the query results on a datagrid, put a datagrid object/control (whatever its called) on the form (in this example I will use dgdItems as the datagrid name. Also place an ado object/control (whatever its called, its on the toolbar) on the form (I will name it adoItems).

Private cnItems AS ADODB.Connection
Private mstrSQL AS String

-----------------------
under whichever sub
------------------------

Set cnItems = New ADODB Connection
cnItems.ConnectionString = "Provider = " _
& "Microsoft.Jet.OLEDB.3.51; " _
& "Data Source =" & App.Path & "\dbItems.mdb"
cnItems.Open
mstrSQL = "SELECT.. <insert your SQL here> ..."
adoItems.ConnectionString = cnItems.ConnectionString
adoItems.CommandType = adCmdText
adoItems.RecordSource = mstrSQL
Set dgdItems.DataSource = adoItems
adoItems.Refresh

** Microsoft.Jet.OLEDB.3.51 most likely will work, I believe it works with the Microsoft ActiveX Data Objects 2.0 Library which as far as I know is used by default (probably) by the Data Environment. If you look at references and the Microsoft ActiveX Data Objects 2.1 Library is used instead, change the line with "OLEDB.3.51;" to "OLEDB.4.0;"

I think that should work..

[Marco Antonio]

I read an article from knowledgebase's Micrososft that perhaps can help you:
http://support.microsoft.com/suport/.../Q244/7/79.ASP

The subject of this site is HOWTO: REFRESH A PARAMETRIZED DATAREPORT.
I hope so...

[gauravmarwaha]

Hii,
Just type Ur command object as Select (blah blah) then WHERE TestName = ?.

This will prompt U for parameters. Go to the parameter tab and enter the name and datatype of the parameters. Then at run time just in report initialize enter the name of the parameter as
DataEnvironment1.Command1 <parameter value>
Then say datareport.show.

Hope U got that if not mailme [email protected]