Crating a Data Report using the Data Environment in VB 6.0 1

[HaworthBantam]

I have a VB 6.0 front end linking to an Access97 back end.

I'm able to create a simple report in VB 6.0 based on a particular table. I can "hard code" a value to pull out specific data but I cannot work out how to pass a variable to the "query" to pull out specific data from the table.

I'm probably missing something simple, but any help in getting this simpleton back on track would be appreciated.

Thanks.

 

[johnwm]

Give us an example of your existing query and we can go from there
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[HaworthBantam]

It's not a query as such.

Using the DataEnvironment I've created a connection to the back end database and one particular table. I've then created a report based on that connection which works fine.

If I change the connection properties from a table to a SQL string I can pull out specific data, but this SQL string is effectively "hard coded". I need to know how to change the properties of the connection (I think) via code so that my users can choose what data the report should be based on.

Does this make sense ?

 

[johnwm]

Give us an example of your existing SQL string and we can go from there
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[HaworthBantam]

The SQL string that's effectively "hard coded" was created using the SQL Builder and works just fine.

My problem is being able to change that SQL string, via code, at run time so that my users can choose what is displayed on the report.

I'm not sure where the code should be sited (in a seperate mode, behind a form, on the initialise event of the report etc) or the exact syntax I should be using.

Let me know if you need any more info johnwm, and thanks for taking the time to look at this.

 

[johnwm]

Example:
Your existing SQLstring:
"Select * from tblClients WHERE clientname = 'Smith'"

If you want to use the text from Text1.text then on the _click event of your search button you can construct your SQLstring thus:

SQLstring = "Select * from tblClients WHERE clientname = " & Trim(Text1.text) & ";"

I could have given you a more appropriate example if I had your SQL string!
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[HaworthBantam]

Thanks johnwm, but the problem isn't with writing the SQL string.

Obviously I've not described my problem correctly.

The problem is where do I put the code to base the data report on the SQL string that I write, and the syntax involved.

Thanks.

 

[johnwm]

I've always struggled to get decent control of Dataenvironment, so for this sort of stuff I use either a straight ADO recordset, or ADO via ADODC control.

Add an ADODC control and set its connection properties as required, test the connection and set its recordsource to the table your data comes from.

In your event code to show the datareport, change the bits you need:

Dim mySQl As String
Dim rst As ADODB.Recordset
mySQl = "SELECT ClientName, Add1, Add2 FROM tblClients WHERE (tblClients.clientname) Like '" & Text1.Text & "%';"
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = mySQl
Adodc1.Refresh
Set rst = Adodc1.Recordset
Set dr1.DataSource = rst
dr1.Show
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[HaworthBantam]

Thanks, I have a couple of workarounds that I've worked out but they are a little "dirty".

I have to admit that this is the first time that I've had to use the DataEnvironment so I'm fumbling around in the dark at the moment.

Thanks for all your help though, I shall probably use your method as it seems somewhat cleaner than my workarounds.