Passing a VB variable into a report using a Data Report

[Guest_imported]

I am using the data enviroment and Data report to generate a report from a access database. I need to be able to select a variable which is generated in the Vb code into the SQL code. Currently I have the SQL statement set to:
select * FROM History_Query1 where Reference='Joh502'
which selects the record from History_Query1 where the column Reference = Joh502.
I want to be able to pass this "where" condition in as a visual basic string so I can control the output of the report. This is the first time I have used the Data report and I cannot see a way of doing this but I am sure it is possible.
Any help would be appreciated.

Happy New Year

John

 

[goatsaregreat]

I don't know exactly what you mean, but I used the following line of code to place the value of a label on a main form into the label on the Data Report. Maybe you can modify it to fit your needs. Let us know if it helps.

DataReport1.Sections("sectionname&quot.Controls("controlname&quot.Caption = Form2.Label5.Caption

 

[elibb]

you can asign the command text to the dataenvironment from the code of your form, before it shows the datareport, that way you can control the sql statement you use, go to the command of the dataenvorinemt, and right click on it, click on the last option (it says something about info) and copy all the sql statement, then put it like this:

Dim strSQL as string

strSQL= paste

and on the string, you can edit the where part of the sql query, to make it a variable, then assign to the dataenvirnment:

DataEnvironment2.Commands.Item(1).CommandText = strSQL


Set DataPBM.DataSource = DataEnvironment2

DataPBM.DataMember = "Command1_Grouping"


i hope you understand what i meant, and it helps you

Eli

 

[johnmacleod10]

Thanks Eli
I could not get that to work correctly I kept getting some strange errors. Where do I place the code. I do not have a child Command so the Hierarchy info is not avalable.

 Dim strSQL as string
 strSQL=paste

Where do I place the SQL code?
Do I need to do the Grouping?

But I did get something working by doing the following.
Setting the SQL statement to
Select * FROM History where Reference =?
Then using the parameters tab and setting Param1 up to pass the string variable. Data_type abBSTR and Host Data type String(VT_BSTR)
Then before running the report
Assigning the variable by doing the following:

reference="Joh952"
DataEnvironment1.Historyquery reference

This works apart from one problem. I am using an ODBC driver form the Access database and when I run it I get the following error

Runtime Error'-2147217833(80040e57)':
[Microsoft][ODBC Access Driver] string data, right truncated(null)

Using the Jet 3.51 driver I do not get this error. The report runs correctly.]

Any help would be appreciated.

 

[elibb]

mmm im not sure about this, but when i use a ODBc driver, ni never get an error, i usually make it a variable and then assign it to the data environment or to the datareport.
so i would recoment that you check the connection to the database in the odbc driver, mayber there problem is there.

i hope it helps.

happy new year
Eli

 

[Guest_imported]

Found the problem
In the Parameter tab I had the size as zero Bytes. Changing this to 10 cleared the error.

 

[Guest_imported]

Found the problem
In the Parameter tab I had the size as zero Bytes. Changing this to 10 cleared the error.

 

[Guest_imported]

Thanks John! I had the exact same problem and knew exactly
what you were going through in your earlier messages. I'm very glad you posted your findings because I was getting ready to change occupation! You don't need to change the size field when accessing MySQL using the same queries, but as soon as they are Access based, you need size settings. Thanks again!