By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,095 Members | 1,340 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,095 IT Pros & Developers. It's quick & easy.

Data Environment help please

P: 16
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim intMonth As Integer
  3. Dim intYear As Integer
  4.  
  5. ' is a combo box that select month
  6.  
  7. If cboMonth.ListIndex = 0 Then
  8.     intMonth = 1
  9. ElseIf cboMonth.ListIndex = 1 Then
  10.     intMonth = 2
  11. ElseIf cboMonth.ListIndex = 2 Then
  12.     intMonth = 3
  13. ElseIf cboMonth.ListIndex = 3 Then
  14.     intMonth = 4
  15. ElseIf cboMonth.ListIndex = 4 Then
  16.     intMonth = 5
  17. ElseIf cboMonth.ListIndex = 5 Then
  18.     intMonth = 6
  19. ElseIf cboMonth.ListIndex = 6 Then
  20.     intMonth = 7
  21. ElseIf cboMonth.ListIndex = 7 Then
  22.     intMonth = 8
  23. ElseIf cboMonth.ListIndex = 8 Then
  24.     intMonth = 9
  25. ElseIf cboMonth.ListIndex = 9 Then
  26.     intMonth = 10
  27. ElseIf cboMonth.ListIndex = 10 Then
  28.     intMonth = 11
  29. ElseIf cboMonth.ListIndex = 11 Then
  30.     intMonth = 12
  31. End If
  32.  
  33.  is a combo box that select year
  34.  
  35. If cboYear.ListIndex = 0 Then
  36.     intYear = 2007
  37. ElseIf cboYear.ListIndex = 1 Then
  38.     intYear = 2008
  39. ElseIf cboYear.ListIndex = 2 Then
  40.     intYear = 2009
  41. ElseIf cboYear.ListIndex = 3 Then
  42.     intYear = 2010
  43. ElseIf cboYear.ListIndex = 4 Then
  44.     intYear = 2011
  45. End If
  46.  
  47.  
  48.  
  49. ' how should i pass a value to a Data Environment sql query ?
  50. or got other way to do ?
  51. ' i need view that month and year that i have selected. 
  52. 'P_Payslip.Show
  53.  
  54.  
  55.  
  56.  
Apr 3 '07 #1
Share this Question
Share on Google+
10 Replies


Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim intMonth As Integer
  3. Dim intYear As Integer
  4.  
  5. ' is a combo box that select month
  6.  
  7. If cboMonth.ListIndex = 0 Then
  8.     intMonth = 1
  9. ElseIf cboMonth.ListIndex = 1 Then
  10.     intMonth = 2
  11. ElseIf cboMonth.ListIndex = 2 Then
  12.     intMonth = 3
  13. ElseIf cboMonth.ListIndex = 3 Then
  14.     intMonth = 4
  15. ElseIf cboMonth.ListIndex = 4 Then
  16.     intMonth = 5
  17. ElseIf cboMonth.ListIndex = 5 Then
  18.     intMonth = 6
  19. ElseIf cboMonth.ListIndex = 6 Then
  20.     intMonth = 7
  21. ElseIf cboMonth.ListIndex = 7 Then
  22.     intMonth = 8
  23. ElseIf cboMonth.ListIndex = 8 Then
  24.     intMonth = 9
  25. ElseIf cboMonth.ListIndex = 9 Then
  26.     intMonth = 10
  27. ElseIf cboMonth.ListIndex = 10 Then
  28.     intMonth = 11
  29. ElseIf cboMonth.ListIndex = 11 Then
  30.     intMonth = 12
  31. End If
  32.  
  33.  is a combo box that select year
  34.  
  35. If cboYear.ListIndex = 0 Then
  36.     intYear = 2007
  37. ElseIf cboYear.ListIndex = 1 Then
  38.     intYear = 2008
  39. ElseIf cboYear.ListIndex = 2 Then
  40.     intYear = 2009
  41. ElseIf cboYear.ListIndex = 3 Then
  42.     intYear = 2010
  43. ElseIf cboYear.ListIndex = 4 Then
  44.     intYear = 2011
  45. End If
  46.  
  47.  
  48.  
  49. ' how should i pass a value to a Data Environment sql query ?
  50. or got other way to do ?
  51. ' i need view that month and year that i have selected. 
  52. 'P_Payslip.Show
  53.  
  54.  
  55.  
  56.  
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=1I7SUNA

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

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
Apr 3 '07 #2

100+
P: 375
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim intMonth As Integer
  3. Dim intYear As Integer
  4.  
  5. ' is a combo box that select month
  6.  
  7. If cboMonth.ListIndex = 0 Then
  8.     intMonth = 1
  9. ElseIf cboMonth.ListIndex = 1 Then
  10.     intMonth = 2
  11. ElseIf cboMonth.ListIndex = 2 Then
  12.     intMonth = 3
  13. ElseIf cboMonth.ListIndex = 3 Then
  14.     intMonth = 4
  15. ElseIf cboMonth.ListIndex = 4 Then
  16.     intMonth = 5
  17. ElseIf cboMonth.ListIndex = 5 Then
  18.     intMonth = 6
  19. ElseIf cboMonth.ListIndex = 6 Then
  20.     intMonth = 7
  21. ElseIf cboMonth.ListIndex = 7 Then
  22.     intMonth = 8
  23. ElseIf cboMonth.ListIndex = 8 Then
  24.     intMonth = 9
  25. ElseIf cboMonth.ListIndex = 9 Then
  26.     intMonth = 10
  27. ElseIf cboMonth.ListIndex = 10 Then
  28.     intMonth = 11
  29. ElseIf cboMonth.ListIndex = 11 Then
  30.     intMonth = 12
  31. End If
  32.  
  33.  is a combo box that select year
  34.  
  35. If cboYear.ListIndex = 0 Then
  36.     intYear = 2007
  37. ElseIf cboYear.ListIndex = 1 Then
  38.     intYear = 2008
  39. ElseIf cboYear.ListIndex = 2 Then
  40.     intYear = 2009
  41. ElseIf cboYear.ListIndex = 3 Then
  42.     intYear = 2010
  43. ElseIf cboYear.ListIndex = 4 Then
  44.     intYear = 2011
  45. End If
  46.  
  47.  
  48.  
  49. ' how should i pass a value to a Data Environment sql query ?
  50. or got other way to do ?
  51. ' i need view that month and year that i have selected. 
  52. 'P_Payslip.Show
  53.  
  54.  
  55.  
  56.  
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.Connection
' 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.ConnectionString = _
"Provider=Microsoft.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.DataSource = m1
DataReport1.WindowState = vbMaximized
DataReport1.Show vbModal
DataReport1.Caption = "Invoice"
m1.Close
Mycon1.Close
Mycon1.Open
Set Mycon1 = Nothing
End If


This code works for me
Good Luck
Apr 3 '07 #3

Dököll
Expert 100+
P: 2,364
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.Connection
' 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.ConnectionString = _
"Provider=Microsoft.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.DataSource = m1
DataReport1.WindowState = vbMaximized
DataReport1.Show vbModal
DataReport1.Caption = "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:

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.
Apr 3 '07 #4

vijaydiwakar
100+
P: 579
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.Connection
' 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.ConnectionString = _
"Provider=Microsoft.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.DataSource = m1
DataReport1.WindowState = vbMaximized
DataReport1.Show vbModal
DataReport1.Caption = "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
Apr 3 '07 #5

P: 16
Error: Object Requiered.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim intMonth As Integer
  3. Dim intYear As Integer
  4.  
  5. If cboMonth.ListIndex = 0 Then
  6.     intMonth = 1
  7. ElseIf cboMonth.ListIndex = 1 Then
  8.     intMonth = 2
  9. ElseIf cboMonth.ListIndex = 2 Then
  10.     intMonth = 3
  11. ElseIf cboMonth.ListIndex = 3 Then
  12.     intMonth = 4
  13. ElseIf cboMonth.ListIndex = 4 Then
  14.     intMonth = 5
  15. ElseIf cboMonth.ListIndex = 5 Then
  16.     intMonth = 6
  17. ElseIf cboMonth.ListIndex = 6 Then
  18.     intMonth = 7
  19. ElseIf cboMonth.ListIndex = 7 Then
  20.     intMonth = 8
  21. ElseIf cboMonth.ListIndex = 8 Then
  22.     intMonth = 9
  23. ElseIf cboMonth.ListIndex = 9 Then
  24.     intMonth = 10
  25. ElseIf cboMonth.ListIndex = 10 Then
  26.     intMonth = 11
  27. ElseIf cboMonth.ListIndex = 11 Then
  28.     intMonth = 12
  29. End If
  30.  
  31. If cboYear.ListIndex = 0 Then
  32.     intYear = 2007
  33. ElseIf cboYear.ListIndex = 1 Then
  34.     intYear = 2008
  35. ElseIf cboYear.ListIndex = 2 Then
  36.     intYear = 2009
  37. ElseIf cboYear.ListIndex = 3 Then
  38.     intYear = 2010
  39. ElseIf cboYear.ListIndex = 4 Then
  40.     intYear = 2011
  41. End If
  42.  
  43. Dim Mycon1 As New ADODB.Connection
  44. Dim m1 As New ADODB.Recordset
  45. Dim strCNString As String
  46.  
  47. strCNString = "Data Source=" & App.Path & "\PayrollBakeryDB.mdb"
  48. Mycon1.Provider = "Microsoft Jet 4.0 OLE DB Provider"
  49. Mycon1.ConnectionString = strCNString
  50. Mycon1.Open
  51.  
  52.     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)
  53. ' Connect the Recordset to the DataReport.
  54.  
  55.     Set DataReport1.DataSource = m1 ' error: object required
  56.  
  57.  
  58.     DataReport1.WindowState = vbMaximized
  59.     DataReport1.Show vbModal
  60.     DataReport1.Caption = "Invoice"
  61.     m1.Close
  62.     Mycon1.Close
  63.     Mycon1.Open
  64.     Set Mycon1 = Nothing
  65.  
  66.  
Apr 3 '07 #6

vijaydiwakar
100+
P: 579
Error: Object Requiered.

[code]
pls sho the place where u got the error
Apr 3 '07 #7

P: 16
error occur at:


Set DataReport1.DataSource = m1
Apr 3 '07 #8

100+
P: 375
error occur at:


Set DataReport1.DataSource = m1
Sorry Gillian for the trouble
Fogot to tell you that one should Declare m1 intially
Dim m1 As New ADODB.Recordset
Apr 4 '07 #9

P: 3
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
May 12 '07 #10

Dököll
Expert 100+
P: 2,364
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:

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
May 12 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.