Making Reports

Take a look at the data report designer.  You can simply bind it to recordsets to produce reports.

I understand data reporter.  What I want to be able to do is this...

I want the user to have to input three variables..Region, Start Date, and Stop Date.  Then based on these three variables create the report.

I do see how that is possible with Data Reports.  Am I correct in this assumption?

Thanks

Absolutely.

If you create a recordset using these variables you can then assign the datasource property of the report to the recordset and the datafield property of the controls to the fields in the recordset.

Dim rsEmp As ADOR.Recordset

If lstEmp.ListIndex = 0 Then

    Set rsEmp = mRpt.getRptAll(dtStart(0).Value, dtEnd(0).Value)

ElseIf lstEmp.ListIndex > 0 Then

    Set rsEmp = mRpt.getRptEmp(lstEmp.ItemData(lstEmp.ListIndex), dtStart(0).Value, dtEnd(0).Value)

Else

    MsgBox "Please make a valid selection from the list.", vbOKOnly, strTitle
    Exit Sub
End If

Set rptTest.DataSource = rsEmp

rptTest.Sections(1).Controls(1).Caption = "Employee activities for given time period."
   
    rptTest.Sections(3).Controls(1).DataField = "empName"
    rptTest.Sections(3).Controls(2).DataField = "pTitle"
    rptTest.Sections(3).Controls(3).DataField = "wDate"
    rptTest.Sections(3).Controls(4).DataField = "wDesc"

rptTest.Show
rptTest.PrintReport

Set rsEmp = Nothing

----------------------------------------

If you need code to create your recordset with the three variables then let me know.  

tkuppinen-

This is great, but you are going to have to simplify a little more (sorry).

I am a network engineer playing a programmer and have just recently started learning VB and really (today) just started learning the concepts of Data reports.

Thanks

Do you have any experience creating recordsets ?

What exactly do you need help with?

Adjusted points from 100 to 150

Yes I am going to need help with the recordset to I am afraid.

Todd

My original idea was:

Have Datacombo boxes for each field I want on the report with numbers ranging from 1 to 7, and depending on the users preference it would generate a report with sorted by #1 field, then there #2 field, etc....I don't think that would be possible so....

What I want to do is have a pop form which would have a datacombo box for Region and to DT Picker controls one from start date and the other for stop date.

Based on the users choices on these three controls it will generate a report.

Does that make sense?

You can build your SQL query according to user choces in your combos
Try following:
If DataEnvironment1.rsCommand1.State = adStateOpen Then
        DataEnvironment1.rsCommand1.Close
    End If
dataenvironment.rscommand1.open="select * from YourTable where YourDate>=StartDate and YourDate<=LastDate"
....
datareport1.show

tdove,

Here is some pseudo-code for you.


You need to look at ADO, specifically the command and connection objects.

Once you have established the connection to the database you can return a recordset to the application and set your reports datasource property = to that.


Public cnData As ADODB.Connection
Public cmData As ADODB.Command



Public Function getData(strCriteria As String) As ADOB.Recordset

---------------------------------------
'  Function takes a SQL statement as arguement

Dim rsData As ADOB.Recordset
Set rsData = New ADOB.Recordset


--------------------------------
' open database using a dsn
cnData.Open ("aps_test")

With cmData
    .ActiveConnection = cnData
    .CommandText = strCriteria
    .CommandType = adCmdText
End With


With rsData
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmData
End With

Set rsData.ActiveConnection = Nothing

cnData.Close

Set getData = rsData

End Function

---------------------------------------

Now to create that SQL statement you would use something like

dim strSQL as string, rsRegion as ADODB.recordset

strSQL = "SELECT * from [tablename] where region ='" & cboregion.text(cboregion.listindex) & "' and createdDate BETWEEN #" & datepicker1.value & "# and #" & datepicker2.value & "#"

pass the value bak to the recordset with something like

set rsRegion = getData(strSQL)


Then using the code above you can set this recordset to the report.

This is all Pseudo code and will need to be modified for your purposes.

Remember a couple of things:

You need to make a reference to ADO in the project references menu (Microsoft Active X Data Objects - in the references menu).

The SQL is based on an MSACcess database.

The help menu in VB has many great examples and a wealth of good information.

Hope this puts you on the right track.

See above

Thanks tkuppinen-

However I am not using dsn so this didn't work.

Adjusted points from 150 to 300

Actually I am completely lost again, I thought I understood what we were trying to do, but now I am unsure.

What don't you understand?

Actually if it would be OK, start from the beginning, I am sitting here going through the MSDN trying to figure this out.  I am trying to learn as we go here by example.

In my table (Employees) I have the following field Names: EmpName (Employee Name), DteSub (Date Submitted), CatName (Category Name), ProjName (Project Code), DteExp (Date Of Expense), PDNName (Charge Code), CityName (City Name), AmtSpt (Amount Spent), RegionCat (Region Category), DescName (Description Name).  I want to be able to make reports manipulating these fields i.e expenses between certain dates, all expenses to a certain city, all expenses by a certain employee, etc.

Like I said earlier I am a Network Engineer learning to program so be gentle.

Thanks,
Todd

Actually if it would be OK, start from the beginning, I am sitting here going through the MSDN trying to figure this out.  I am trying to learn as we go here by example.

In my table (Employees) I have the following field Names: EmpName (Employee Name), DteSub (Date Submitted), CatName (Category Name), ProjName (Project Code), DteExp (Date Of Expense), PDNName (Charge Code), CityName (City Name), AmtSpt (Amount Spent), RegionCat (Region Category), DescName (Description Name).  I want to be able to make reports manipulating these fields i.e expenses between certain dates, all expenses to a certain city, all expenses by a certain employee, etc.

Like I said earlier I am a Network Engineer learning to program so be gentle.

Thanks,
Todd

Actually if it would be OK, start from the beginning, I am sitting here going through the MSDN trying to figure this out.  I am trying to learn as we go here by example.

In my table (Employees) I have the following field Names: EmpName (Employee Name), DteSub (Date Submitted), CatName (Category Name), ProjName (Project Code), DteExp (Date Of Expense), PDNName (Charge Code), CityName (City Name), AmtSpt (Amount Spent), RegionCat (Region Category), DescName (Description Name).  I want to be able to make reports manipulating these fields i.e expenses between certain dates, all expenses to a certain city, all expenses by a certain employee, etc.

Like I said earlier I am a Network Engineer learning to program so be gentle.

Thanks,
Todd

Have you been able to work with recordsets at all yet?  Have you been able to access the database?  Is the reports the final step?

What don't you understand about the code?

The reports are my final steps.

This is the code on the form that inputs the data into the database.


Option Explicit

Private mcn As ADODB.Connection


Private Sub Command1_Click()
  Dim rst1 As ADODB.Recordset
 
If Text1.Text = "" Then
 MsgBox "Must Enter Employees Name"
 Text1.SetFocus
 Exit Sub
End If
If Text9.Text = "9" Then
 MsgBox "Must Enter Employee ID"
 Text9.SetFocus
 Exit Sub
End If
If DataCombo1.Text = "" Then
 MsgBox "Must Pick A Region"
 DataCombo1.SetFocus
 Exit Sub
End If
If Text4.Text = "" Then
 MsgBox "Must Fill in the Chargable Code Center or PDN"
 Text4.SetFocus
 Exit Sub
End If
If Text5.Text = "" Then
 MsgBox "Must Fill in a Chargable Location"
 Text5.SetFocus
 Exit Sub
End If
If Text6.Text = "" Then
 MsgBox "Must Fill in A Project ID"
 Text6.SetFocus
 Exit Sub
End If
If Text7.Text = "" Then
 MsgBox "Must Fill In a Charge Amount"
 Text7.SetFocus
 Exit Sub
End If
If DataCombo2.Text = "" Then
 MsgBox "Must Pick A Category"
 DataCombo2.SetFocus
 Exit Sub
End If
If Text8.Text = "" Then
 MsgBox "Must Fill In A Short Description"
 Text8.SetFocus
 Exit Sub
End If

  mcn.Execute "INSERT INTO Employees(EmpName, EmpID, DteSub, ProjName, CatName, PDNName, CityName, AmtSpt, RegionCat, DteExp, DescName) VALUES ('" & Replace(Text1.Text, "'", "''") & "', '" & Replace(Text9.Text, "'", "''") & "', '" & Format(DTPicker1.Value, "mm/dd/yyyy") & "', '" & Replace(Text6.Text, "'", "''") & "', '" & Replace(DataCombo2.Text, "'", "''") & "', '" & Replace(Text4.Text, "'", "''") & "', '" & Replace(Text5.Text, "'", "''") & "', '" & Replace(Text7.Text, "'", "''") & "', '" & Replace(DataCombo1.Text, "'", "''") & "', '" & Format(DTPicker2.Value, "mm/dd/yyyy") & "', '" & Replace(Text8.Text, "'", "''") & "')"
  MsgBox "Employees Expense Added To Database", vbDefaultButton1
 
  'Feed the data grid
    Set rst1 = New ADODB.Recordset
    With rst1
        .ActiveConnection = mcn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select * from Employees"
        Set DataGrid1.DataSource = rst1
        DataGrid1.Refresh
    End With

 
  Text9.Text = ""
  Text4.Text = ""
  DataCombo2.Text = ""
  Text1.Text = ""
  DataCombo1.Text = ""
  Text5.Text = ""
  Text8.Text = ""
  Text6.Text = ""
  Text7.Text = ""
   
 
End Sub

Private Sub Command2_Click()
Unload Me
AdminMenu.Show
End Sub

Private Sub Command3_Click()
End
End Sub

Private Sub Form_Load()
Dim rst1 As ADODB.Recordset

    'Create a connection
    Set mcn = New ADODB.Connection
    With mcn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\atfweb\expense\expense.mdb;Persist Security Info=False"
        .Open
    End With
     
    'Feed the data grid
    Set rst1 = New ADODB.Recordset
    With rst1
        .ActiveConnection = mcn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select * from Employees"
        Set DataGrid1.DataSource = rst1
        DataGrid1.Refresh
    End With
     
    'Feed one data combo
    Set rst1 = New ADODB.Recordset
        With rst1
        .ActiveConnection = mcn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select * from RegionCat"
        Set DataCombo1.RowSource = rst1
        DataCombo1.ListField = "RegionName"
        DataCombo1.Refresh
    End With
     
    'Feed another combo
    Set rst1 = New ADODB.Recordset
    With rst1
        .ActiveConnection = mcn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select * from EmployeeCat"
        Set DataCombo2.RowSource = rst1
        DataCombo2.ListField = "CatName"
        DataCombo2.Refresh
    End With
End Sub


Private Sub Form_Unload(Cancel As Integer)
  mcn.Close
  Set mcn = Nothing

End Sub

I have to go to a meeting, I'll look at this later this morning.

I see you use a datagrid, well the datareport is similar in that you need to provide it with a recordset for a datasource, once you have done that then you need to change the datafield property of the textboxes to your recordset fields.

So for a test add a data report to your project and add one textbox to the detail section.  Don't add anything else right now.  

Add a test button to your form.  On the click of the button open a recordset in code with this SQL:

"Select empName from Employees"

Once you have opened the recordset then you need to set the report's datasource property (in code) to your recordset.

Then add the following lines

DataReport1.Sections(3).Controls(1).DataField = "empName"
   
Datareport1.show

Let me know what happens.

Setting the datasource is the folowing line.

datareport1.datasource = (your recordset name)

I am making progress.  Below is my code. When I run it I get the following error.  "The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another."  Do you see any problems?





Option Explicit

Dim mcn As Connection
Dim rs As Recordset


Private Sub Command1_Click()
   Set rs = mcn.Execute("SELECT * from Employees WHERE RegionCat ='" & DataCombo1.Text & "' and DteExp BETWEEN #" & DTPicker2.Value & "# and #" & DTPicker1.Value & "#")
   Set DataReport1.DataSource = rs
DataReport1.Show
End Sub

Private Sub Command3_Click()
Unload Me
AdminMenu.Show
End Sub

Private Sub Form_Load()

Dim rst1 As ADODB.Recordset

    'Create a connection
    Set mcn = New ADODB.Connection
    With mcn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\atfweb\expense\expense.mdb;Persist Security Info=False"
        .Open
    End With

'Feed Category DataCombo Box
    Set rst1 = New ADODB.Recordset
    With rst1
        .ActiveConnection = mcn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select * from RegionCat"
        Set DataCombo1.RowSource = rst1
        DataCombo1.ListField = "RegionName"
        DataCombo1.Refresh
 End With

End Sub

Private Sub Form_Unload(Cancel As Integer)
mcn.Close
Set mcn = Nothing

End Sub

Got that figured out!  I still had the date Enviorment bound to the Data Report!  Undid that it pulls it up.

OK, last thing I think I need is how to populate the data report without using the data environment.

Assuming you placed controls on your data report in the detail section it is as follows.

DataReport1.Sections(3).Controls(1).DataField = "empName"
     
This will set the first control on the left to be the name field from your table.  Do you see how this works?  You refer to the controls by number starting at 1 going from left to right.  The detail section is 3 because the sections are numbered starting at 1 and going top to bottom.

In your recordset there are what is known as fields.  These are returned from your SQL statement.  You said "select * "  which is the same as saying "select empname, empId, etc".  Your recordset contains these fieldnames and to refernce them all you have to do is refer to them by name.  To put them on the datareport you have to set a control's datafield property = to a name of a field in your recordset.

OK, I have that, however now that I don't have a "Data Environment", my Data Report will not let me use RptTextBox, is that what you mean by controls?

tkuppinen-

I understand now!  I had a breakthrough last night playing around with and looking at your responses!  My question is this...can you put controls any other place other than the details section?

Here is my code:  It still won't put the value in the RptTextBox on the Data Report?


Option Explicit

Dim mcn As Connection
Dim rs As Recordset


Private Sub Command1_Click()
   Set rs = mcn.Execute("SELECT * from Employees WHERE RegionCat ='" & DataCombo1.Text & "' and DteExp BETWEEN #" & DTPicker2.Value & "# and #" & DTPicker1.Value & "#")
   Set DataReport1.DataSource = rs
   'DataReport1.Sections(1).Controls(3).DataField = "RegionCat"
 DataReport1.Show
End Sub

Private Sub Command2_Click()
DataReport1.Show
End Sub

Private Sub Command3_Click()
Unload Me
AdminMenu.Show
End Sub

Private Sub Form_Load()

Dim rst1 As ADODB.Recordset

    'Create a connection
    Set mcn = New ADODB.Connection
    With mcn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\atfweb\expense\expense.mdb;Persist Security Info=False"
        .Open
    End With

'Feed Category DataCombo Box
    Set rst1 = New ADODB.Recordset
    With rst1
        .ActiveConnection = mcn
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockReadOnly
        .Open "Select * from RegionCat"
        Set DataCombo1.RowSource = rst1
        DataCombo1.ListField = "RegionName"
        DataCombo1.Refresh
 End With

End Sub

Private Sub Form_Unload(Cancel As Integer)
mcn.Close
Set mcn = Nothing

End Sub

I rem out the DataReport1.Sections to try to add it to the propeties on the datReport and that didn't roke either I also tried changing the section to 1 because on the DataReport it shows details1 to give that a shott too.

Sorry I haven't got back to you in a while.  Controls are textboxes, labels, things you add to the report.  

You can put controls in any section you want.  Just refer to the section and the control.  The index starts at 1 and increments going from top to bottom

The details section is still referred to as datareport1.sections(3).  It is confusing but you are referring to the index of the section and not its name.  Although it is called section 1 its index is actually 3.

Try cahnging this line

'DataReport1.Sections(1).Controls(3).DataField = "RegionCat

to

'DataReport1.Sections(3).Controls(3).DataField = "RegionCat

This will only work if you have 3 textboxes in this section.  if you only have 1 then the line should read

'DataReport1.Sections(3).Controls(1).DataField = "RegionCat

There are typos in those lines the format should be

DataReport1.Sections(3).Controls(1).DataField = "RegionCat"

Sorry

It pulls up the DataReport and shows the title info that I put on the form, but it doen't show the RptTextbox attached to the RegionCat?

There is information in the database?  It is in the details section?  
You took the comment (') character out?

Adjusted points from 300 to 350

Yes to all three.  The RptTextBox says unbound, in designer mode.  Is there anything wrong with the SQL statement, that you can see?

P.S. Raised the points again, thanks for helping me out on this.

My mistake, I guess it would help if I picked records that fell in the date range specified by the DatePicker...

So it is working!!!  Now this brings me up to another question.  I want the "CatName" to shup up only once at the top, instead of with every record.  Is that possible?

"Show up" not "shup up"

No but if you want to be sure take the variable for the regioncat and just hardcode in a value you know exists and see if that works.

That response was for your previous post not the latest one.  Depending on how your information is set up you may able to do that.

What about 'grouping" is that possible to do this way?

Yes basically what you have to look at doing is adding sections to your report and placing the field by which you would like to group into the header.  

Can you give me an example of that?

What do you mean?  Just add the control to new section.  Set the datafield property of the control in your code and you should be fine.  You might want to add a "Group By" clause in your SQL statement also.

I guess what I am asking is how do I use the GROUP BY clause.

Literally, all you have to do in your case is add it to the end of your SQL statement.

"SELECT * from Employees WHERE RegionCat ='" & DataCombo1.Text & "' and DteExp BETWEEN #" & DTPicker2.Value & "# and #" & DTPicker1.Value & "# GROUP BY catName"

I got an error message that says "Cannot Group On Fields selected with '*'"

Sorry, you have to explicitly select the fields by name.  For example your SQL has to become  "SELECT empname, empid, etc, etc GROUP BY catName"

Adjusted points from 350 to 400

I get an error message "You tried to execute a query that does not include the specified expression 'RegionCat' as part of an aggregate function"

here is the SQL statement:

Set rs = mcn.Execute("SELECT RegionCat,EmpName,DteExp,CityName,AmtSpt,ProjName,PDNName,CatName,DescName,DTPicker1,DTpicker2 FROM Employees WHERE RegionCat ='" & DataCombo1.Text & "' and DteExp BETWEEN #" & DTPicker2.Value & "# and #" & DTPicker1.Value & "# GROUP BY EmpName")

Thanks for all your help!

I'm glad everything worked out.