Simple Data Enviroment Question

[pinkpanther2070]

This is my first time using the Data Environment

All I would like to do is instead of right clicking connection1 and going down to properties, and establishing a connection through there to my database.

I would like to write the code out so that I will be able to use app.path command

My purpose fo doing this is so that no matter where my project is it will never have problems finding the database.

Thanks
PinkPanther2070

[robertx]

Set the database path in the data environment and change it at runtimeas follows:

Dim sDatabaseName As String
Dim sDatabaseFilePath As String
Dim sConnectString As String
Dim SQL As String

SQL = "SELECT...................................."
sDatabaseName = "........................mdb"
sDatabaseFilePath = App.Path & sDatabaseName

sConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & sDatabaseFilePath

Set DataEnvironment1 = Nothing
DataEnvironment1 .Connection1.ConnectionString = sConnectString
DataEnvironment1.rsQuery1.Open SQL
Report1.Refresh
Report1.Show
DataEnvironment1 .rsQuery1.Close
Set DataEnvironment1 = Nothing

[robertx]

I would imagine that you could reset the connection string at run time by putting

DataEnvironment1 .Connection1.ConnectionString = sConnectString

in Private Sub DataEnvironment_Initialize() but this will not change the database to which the data environment points to at design time.

The database to which the data environment points to at design time is hard-coded by right-clicking on the yellow connection icon and selecting "properties".

[pinkpanther2070]

so all of this has to be done during runtime, correct?

sorry for being such a beginner, but how am I going to use the command1 to then make a report?

I didn't understand your rsQuery, is this another name for command1?

thanks for you patience with me....

Oh by the way i was able to get a connection, however I got an error that said the connecion string was too long here is what I got

Dim path As String
Dim SQL As String

Private Sub DataEnvironment_Initialize()
SQL = "Select * From nothing"

path = App.path & "\Name.mdb"
With Connection1
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & path
.CursorLocation = adUseClient
.Open
.Execute (SQL)
End With

End Sub

Can you see anything wrong :-(

thanks
Pinkpanther

[robertx]

Dear Pink Panther,

You need to understand the key objects associated with the data environment.

You have the data environment object itself and the connection and recordset objects under it.

Do the following and it should give you a good understanding of how VB data reports operate. I am assuming that you are using VB6.

1.Select "Project" from the drop down list at the top of the VB IDE

2.Select "More ActiveX Designers" then select "Data Environment". This will create a new data environment with a new connection (Connection 1).

3.Right click on Connection 1 and select "properties". Add your database path/database type.

4.Again, right click on Connection 1 and select "Add Command".

5.Right click on "Command1" and you will notice that there is a field called "Command Name". VB prefixes this command name with "rs" ( for recordset) and the resulting name (eg.rsCommand1) is active as the recordset object that is needed to display data in a report at run time. The name without the prefix is required as the "Data Member" property of the data report that you are trying to generate. The "Data Source" property is the data environment itself.

6.Select the option button labelled "SQL Statement" and type your SQL statement and click OK.

Now go back and look at the code that I posted initially. I have changed rsQuery1 to rsCommand1 as that is the default name that VB provides for the recordset object. I have never put code in the data environment's code window as I have never had a purpose for it. This code would go in a command button's click event procedure with caption "Generate Report".

The purpose of the refresh method is that (although you cannot add new fields to SQL at run time) you can take parameters (eg. a date range) from the user and filter the recordset so that it only produces records within that date range.

Dim sDatabaseName As String
Dim sDatabaseFilePath As String
Dim sConnectString As String
Dim SQL As String

SQL = "SELECT...................................."
sDatabaseName = "........................mdb"
sDatabaseFilePath = App.Path & sDatabaseName

sConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & sDatabaseFilePath

Set DataEnvironment1 = Nothing
DataEnvironment1 .Connection1.ConnectionString = sConnectString
DataEnvironment1.rsCommand1.Open SQL
Report1.Refresh
Report1.Show
DataEnvironment1 .rsCommand1.Close
Set DataEnvironment1 = Nothing

I hope that this is of assistance to you. Otherwise, please feel free to ask again....

[pinkpanther2070]

YEHHHHHHH.....

I think I've got it (It is working......but I'm still testing it)

I am going to spend the rest of my week getting the reports together on this project

So that I can do it's implementaion some time next week

I am extremely thankful for your time and patience....I couldn't find this any where

and I'm not sure how long it would have taken me to think of doing it that way if at all.....

thanks a bunch you have saved me alot of time i really appreciate your time and patience
Sincerely
PinkPanther

P.S
I guess this was not that simple a question after all, I belong to 2 forums and you were the only one to respond out of both

[robertx]

Just another useful hint, you can access the properties of data report objects such as labels and text boxes. Notice how data reports are split into 5 sections - each one numbered. If I had a label in the report header ("Section4") called "Label1" that I wanted to read:

"Customer Purchase Report for 6-Dec-2001" (ie.today's date)

You would do this by:

Report1.Sections("Section4").Controls("Label1").Caption = "Customer Purchase Report for " &
Format(Date, "dd-mmm-yyyy")

This goes just prior to calling Report1.Refresh


Good luck.....................

[pinkpanther2070]

Thanks Robert, you have been a ton of help.....

Im sure that will come in handy!!!!!

Sincerely
Pink Panther


got a final today....stressed out

[pinkpanther2070]

Hi Robert,

I had a programming interruption, and now I can pick up where I left off (testing the connection with SQL statements)


I've been having problems getting my report to refresh....

here it is:
_________________________________________
Private Sub Command1_Click()
Dim sDatabaseName As String
Dim sDatabaseFilePath As String
Dim sConnectString As String
Dim SQL As String

sDatabaseName = "\YeehawNB.mdb"
sDatabaseFilePath = App.Path & sDatabaseName
sConnectString = "Provider=Microsoft.Jet.oledb.4.0;Data Source =" & sDatabaseFilePath

SQL = "Select Cash From FormTender"
Set DataEnvironment2 = Nothing
DataEnvironment2.Connection1.ConnectionString = sConnectString
DataEnvironment2.rsCommand1.Open SQL
DataReport2.Refresh
DataReport2.Show
DataEnvironment2.rsCommand1.Close
Set DataEnvironment2 = Nothing
End Sub
______________________________________________
When the Debugger tries to execute the:

DataReport2.Refresh

Line I get an error that says:

Runtime Error '8578':
Data type mismatch function object 'Function1'

I haven't been able to fix this and I was hoping that maybe you could give me some advice, or maybe point me in the right direction

Thanks
PinkPanther

[robertx]

It would appear that you have a function on your data report which is adding the numbers in the field "cash". I would suggest that either there is a non-numeric value or a null value in one of the cells which is causing a type-mismatch error

[pinkpanther2070]

OOOOpss.....

have you ever experienced any problems while refreshing a query during runtime


I am trying to get the SQL statement to reflect the conditions of the text1.txt . However when I press the command button for the 2nd time it query's the sql statement that is in the dataenvironment under rscommand1 (in that little box for sql statements) which is usually Select * From FormTender. So it then just pulls all the records and ignores the condition.

I want to make sure that I did say that it does use the SQL statement the 1st time the command1 button is pressed

but there after it uses the rscommand1 sql statement in that little box in the dataenvironment?

__________________________________________________

Private Sub Command1_Click()
Dim sDatabaseName As String
Dim sDatabaseFilePath As String
Dim sConnectString As String
Dim SQL As String

sDatabaseName = "\YeehawNB.mdb"
sDatabaseFilePath = App.Path & sDatabaseName
sConnectString = "Provider=Microsoft.Jet.oledb.4.0;Data Source =" & sDatabaseFilePath

SQL = "Select * From FormTender Where receipt = '" & Text1.Text & "'"
Set DataEnvironment2 = Nothing
DataEnvironment2.Connection1.ConnectionString = sConnectString
DataEnvironment2.rsCommand1.Open SQL
DataReport2.Refresh
DataReport2.Show
DataEnvironment2.rsCommand1.Close
Set DataEnvironment2 = Nothing

End Sub



______________________________________________

[robertx]

Yes I have experienced problems refreshing data environment queries!

I have found that sometimes if you try running a report when it is already visible that it does not filter the "WHERE" clause and just displays all records.

I would disable the command button to make sure that the user does not click on it twice:

Private Sub Command1_Click()
Dim sDatabaseName As String
Dim sDatabaseFilePath As String
Dim sConnectString As String
Dim SQL As String

Command1.Enabled = False

sDatabaseName = "\YeehawNB.mdb"
sDatabaseFilePath = App.Path & sDatabaseName
sConnectString = "Provider=Microsoft.Jet.oledb.4.0;Data Source =" & sDatabaseFilePath

SQL = "Select * From FormTender Where receipt = '" & Text1.Text & "'"
Set DataEnvironment2 = Nothing
DataEnvironment2.Connection1.ConnectionString = sConnectString
DataEnvironment2.rsCommand1.Open SQL
DataReport2.Refresh
DataReport2.Show
DataEnvironment2.rsCommand1.Close
Set DataEnvironment2 = Nothing

Command1.Enabled = True

End Sub

[pinkpanther2070]

Thanks again Robert, your replies have been extremely helpful

Pinkpanther

[pinkpanther2070]

If I could only express my anguish, my deadline has creeped up on me, and I'm not done with the reports

I was hoping that you could help me.....

I would like to ask 2 questions about the datareport:

1. I have noticed that it looks like you can only have 8-10 fields on a datareport horizontally (because, and this is only my theory the report has a 6 inch width), Am I wrong, hopefully I am.

Note: Im picturing a report with the fields running across the top horizontally

What do you usually do to create more space or insert fields, do you just carefully situate everything, or is there a way to switch the report from the PORTRAIT to LANDSCAPE ?

2. This is the biggest problem of all, I'm trying to take a parent command (This would be the one part of the relationship examjple: CustomerID)and a child command (This would be the many part of the relationship example: orders) and do a SQL Query (on my form under the command button using the parent rscommand1 ) with a conditional statement, and then do a datareport1.show

Is this possible,or do I have everything mixed up.......


Thanks in advance Robert
you've been great