Data Environment help please

**Dököll** · Apr 3 '07, 06:13 AM

Originally posted by gillian3114

i need to view salary payslip (data report) based on that particular month and year.

in Data Environment sql :

SELECT[emp_id], [emp_name], [emp_position], [emp_bs], [month_paid], [year_paid], [total_addition], [gross_pay], [total_deduction], [total_pay] FROM salary WHERE month_paid = ? and year_paid = ?

parameter is monthler

parameter is yearler

in vb code:

Code:

Dim intMonth As Integer
Dim intYear As Integer

' is a combo box that select month

If cboMonth.ListIndex = 0 Then
    intMonth = 1
ElseIf cboMonth.ListIndex = 1 Then
    intMonth = 2
ElseIf cboMonth.ListIndex = 2 Then
    intMonth = 3
ElseIf cboMonth.ListIndex = 3 Then
    intMonth = 4
ElseIf cboMonth.ListIndex = 4 Then
    intMonth = 5
ElseIf cboMonth.ListIndex = 5 Then
    intMonth = 6
ElseIf cboMonth.ListIndex = 6 Then
    intMonth = 7
ElseIf cboMonth.ListIndex = 7 Then
    intMonth = 8
ElseIf cboMonth.ListIndex = 8 Then
    intMonth = 9
ElseIf cboMonth.ListIndex = 9 Then
    intMonth = 10
ElseIf cboMonth.ListIndex = 10 Then
    intMonth = 11
ElseIf cboMonth.ListIndex = 11 Then
    intMonth = 12
End If

 is a combo box that select year

If cboYear.ListIndex = 0 Then
    intYear = 2007
ElseIf cboYear.ListIndex = 1 Then
    intYear = 2008
ElseIf cboYear.ListIndex = 2 Then
    intYear = 2009
ElseIf cboYear.ListIndex = 3 Then
    intYear = 2010
ElseIf cboYear.ListIndex = 4 Then
    intYear = 2011
End If



' how should i pass a value to a Data Environment sql query ?
or got other way to do ?
' i need view that month and year that i have selected. 
'P_Payslip.Show

Just posted the below, gillian3114, greetings...

Give it a whirl. I also posted another that deals with Data Environment. Will find it for you:

http://www.google.com/search?q=Data.. .ie7&rlz=1I7SUN A

+++++++++++++++ +++++++

Please search a bit deeper here if this is no help. I added "Data Report without Data Environment " to Google to fetch info included there.

Good luck with the project!

Dököll

**cmrhema** · Apr 3 '07, 06:19 AM

Originally posted by gillian3114

i need to view salary payslip (data report) based on that particular month and year.

in Data Environment sql :

SELECT[emp_id], [emp_name], [emp_position], [emp_bs], [month_paid], [year_paid], [total_addition], [gross_pay], [total_deduction], [total_pay] FROM salary WHERE month_paid = ? and year_paid = ?

parameter is monthler

parameter is yearler

in vb code:

Code:

Dim intMonth As Integer
Dim intYear As Integer

' is a combo box that select month

If cboMonth.ListIndex = 0 Then
    intMonth = 1
ElseIf cboMonth.ListIndex = 1 Then
    intMonth = 2
ElseIf cboMonth.ListIndex = 2 Then
    intMonth = 3
ElseIf cboMonth.ListIndex = 3 Then
    intMonth = 4
ElseIf cboMonth.ListIndex = 4 Then
    intMonth = 5
ElseIf cboMonth.ListIndex = 5 Then
    intMonth = 6
ElseIf cboMonth.ListIndex = 6 Then
    intMonth = 7
ElseIf cboMonth.ListIndex = 7 Then
    intMonth = 8
ElseIf cboMonth.ListIndex = 8 Then
    intMonth = 9
ElseIf cboMonth.ListIndex = 9 Then
    intMonth = 10
ElseIf cboMonth.ListIndex = 10 Then
    intMonth = 11
ElseIf cboMonth.ListIndex = 11 Then
    intMonth = 12
End If

 is a combo box that select year

If cboYear.ListIndex = 0 Then
    intYear = 2007
ElseIf cboYear.ListIndex = 1 Then
    intYear = 2008
ElseIf cboYear.ListIndex = 2 Then
    intYear = 2009
ElseIf cboYear.ListIndex = 3 Then
    intYear = 2010
ElseIf cboYear.ListIndex = 4 Then
    intYear = 2011
End If



' how should i pass a value to a Data Environment sql query ?
or got other way to do ?
' i need view that month and year that i have selected. 
'P_Payslip.Show

I have used datareports BUT WITHOUT data environment, and I pass the query in form code itself
You must be having a control where you will be prompting the user to print report
Try out the below code in that control

Dim db_file As String
Dim Mycon1 As New ADODB.Connectio n
' Get the data.
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "emp.mdb"
' Open a connection.
Mycon1.Connecti onString = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
Me.Refresh
Mycon1.Open
Set m1 = Mycon1.Execute( "SELECT emp_id, emp_name, emp_position, emp_bs, month_paid, year_paid, total_addition, gross_pay, total_deduction , total_pay FROM salary WHERE month_paid = " & intMonth & " and year_paid =" & intYear & "", , adCmdText)
' Connect the Recordset to the DataReport.
Set DataReport1.Dat aSource = m1
DataReport1.Win dowState = vbMaximized
DataReport1.Sho w vbModal
DataReport1.Cap tion = "Invoice"
m1.Close
Mycon1.Close
Mycon1.Open
Set Mycon1 = Nothing
End If

This code works for me
Good Luck

**Dököll** · Apr 3 '07, 06:31 AM

Originally posted by cmrhema

I have used datareports BUT WITHOUT data environment, and I pass the query in form code itself
You must be having a control where you will be prompting the user to print report
Try out the below code in that control

Dim db_file As String
Dim Mycon1 As New ADODB.Connectio n
' Get the data.
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "emp.mdb"
' Open a connection.
Mycon1.Connecti onString = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
Me.Refresh
Mycon1.Open
Set m1 = Mycon1.Execute( "SELECT emp_id, emp_name, emp_position, emp_bs, month_paid, year_paid, total_addition, gross_pay, total_deduction , total_pay FROM salary WHERE month_paid = " & intMonth & " and year_paid =" & intYear & "", , adCmdText)
' Connect the Recordset to the DataReport.
Set DataReport1.Dat aSource = m1
DataReport1.Win dowState = vbMaximized
DataReport1.Sho w vbModal
DataReport1.Cap tion = "Invoice"
m1.Close
Mycon1.Close
Mycon1.Open
Set Mycon1 = Nothing
End If

This code works for me
Good Luck

Good job, cmrhema!

Here is the link I was looking for, gillian3114. You can query your database and furnish a report:

404 Not Found

http://www.dreamincode.net/forums/showtopic13544.htm

Give above time to load, snapshots included. Continue asking questions, we'll ge there...

Take cmrhema also, see how that works for you.

**vijaydiwakar** · Apr 3 '07, 06:33 AM

Originally posted by cmrhema

I have used datareports BUT WITHOUT data environment, and I pass the query in form code itself
You must be having a control where you will be prompting the user to print report
Try out the below code in that control

Dim db_file As String
Dim Mycon1 As New ADODB.Connectio n
' Get the data.
db_file = App.Path
If Right$(db_file, 1) <> "\" Then db_file = db_file & "\"
db_file = db_file & "emp.mdb"
' Open a connection.
Mycon1.Connecti onString = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
Me.Refresh
Mycon1.Open
Set m1 = Mycon1.Execute( "SELECT emp_id, emp_name, emp_position, emp_bs, month_paid, year_paid, total_addition, gross_pay, total_deduction , total_pay FROM salary WHERE month_paid = " & intMonth & " and year_paid =" & intYear & "", , adCmdText)
' Connect the Recordset to the DataReport.
Set DataReport1.Dat aSource = m1
DataReport1.Win dowState = vbMaximized
DataReport1.Sho w vbModal
DataReport1.Cap tion = "Invoice"
m1.Close
Mycon1.Close
Mycon1.Open
Set Mycon1 = Nothing
End If

This code works for me
Good Luck

Nice answer so far
keep it up

**gillian3114** · Apr 3 '07, 08:06 AM

Error: Object Requiered.

Code:

Dim intMonth As Integer
Dim intYear As Integer

If cboMonth.ListIndex = 0 Then
    intMonth = 1
ElseIf cboMonth.ListIndex = 1 Then
    intMonth = 2
ElseIf cboMonth.ListIndex = 2 Then
    intMonth = 3
ElseIf cboMonth.ListIndex = 3 Then
    intMonth = 4
ElseIf cboMonth.ListIndex = 4 Then
    intMonth = 5
ElseIf cboMonth.ListIndex = 5 Then
    intMonth = 6
ElseIf cboMonth.ListIndex = 6 Then
    intMonth = 7
ElseIf cboMonth.ListIndex = 7 Then
    intMonth = 8
ElseIf cboMonth.ListIndex = 8 Then
    intMonth = 9
ElseIf cboMonth.ListIndex = 9 Then
    intMonth = 10
ElseIf cboMonth.ListIndex = 10 Then
    intMonth = 11
ElseIf cboMonth.ListIndex = 11 Then
    intMonth = 12
End If

If cboYear.ListIndex = 0 Then
    intYear = 2007
ElseIf cboYear.ListIndex = 1 Then
    intYear = 2008
ElseIf cboYear.ListIndex = 2 Then
    intYear = 2009
ElseIf cboYear.ListIndex = 3 Then
    intYear = 2010
ElseIf cboYear.ListIndex = 4 Then
    intYear = 2011
End If

Dim Mycon1 As New ADODB.Connection
Dim m1 As New ADODB.Recordset
Dim strCNString As String

strCNString = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
Mycon1.Provider = "Microsoft Jet 4.0 OLE DB Provider"
Mycon1.ConnectionString = strCNString
Mycon1.Open

    Set m1 = Mycon1.Execute("SELECT emp_id, emp_name, emp_position, emp_bs, month_paid, year_paid, total_addition, gross_pay, total_deduction, total_pay FROM salary ", , adCmdText)
' Connect the Recordset to the DataReport.

    Set DataReport1.DataSource = m1 ' error: object required
    
   
    DataReport1.WindowState = vbMaximized
    DataReport1.Show vbModal
    DataReport1.Caption = "Invoice"
    m1.Close
    Mycon1.Close
    Mycon1.Open
    Set Mycon1 = Nothing

**vijaydiwakar** · Apr 3 '07, 08:53 AM

Originally posted by gillian3114

Error: Object Requiered.

[CODE]

pls sho the place where u got the error

**gillian3114** · Apr 3 '07, 09:04 AM

error occur at:

Set DataReport1.Dat aSource = m1

**cmrhema** · Apr 4 '07, 03:53 AM

Originally posted by gillian3114

error occur at:

Set DataReport1.Dat aSource = m1

Sorry Gillian for the trouble
Fogot to tell you that one should Declare m1 intially
Dim m1 As New ADODB.Recordset

**Arvindkumar** · May 12 '07, 12:19 PM

Hello All

I have the similar problem in data environment. Though the code works well to set the data source with report.

How can I show the data in Reports. Say I have text1 as a text box in report and I want to display the data in the textbox.

Also I want to display all the records that matching the criteria :
say
select * from emp where sal >5000

Please help I am in trouble

Thanks

**Dököll** · May 12 '07, 02:07 PM

Originally posted by Arvindkumar

Hello All

I have the similar problem in data environment. Though the code works well to set the data source with report.

How can I show the data in Reports. Say I have text1 as a text box in report and I want to display the data in the textbox.

Also I want to display all the records that matching the criteria :
say
select * from emp where sal >5000

Please help I am in trouble

Thanks

Greetings and salutations, Arvindkumar!

Get a load of this link here found through Google:

Page not found | CodeGuru

http://www.freevbcode.com/ShowCode.asp?ID=6307

Use this to get an idea how to modify your code to do what you need.

Please write if you are still uncertain.

In a bit, and good luck...

Dököll

Data Environment help please

Data Environment help please

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment