Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Sam13 (TechnicalUser)
31 May 01 19:33
Hi there, I'm relatively new to working with VB and databases.  I'm working on a project that is using the Data Environment and the Data Report Designer to create reports.  This is my first time using these aspects of VB.  I've stumbled my way through setting up some reports but I'm having trouble with a few things.

First, How do you get the report to show in a landscape format rather than portrait?  Is this option even available??

My second question Is a bit more involved.  Basically when I create the reports, I'm using the data environment connection to create a query of my tables using parent/child commands and SQL statements.  The issue is that When my parent item has no data in the child portion then the report still creates a line item for the parent.  I want to eliminate this line item and only have the report show parent data that has child data.  I hope I'm being clear.  I'm a bit lost on how to do this.

My third question is can the Data Report Designer be used with an ADO control?  Basically I want to be able to search the database on criteria the user enters and print a report using the recordset returned by the search.  Any thoughts on the best way to print such a report would be great.

Thanks in advance for the help.

Sam
bbosley (Programmer)
5 Jun 01 13:57

I would use a stored procedure to return only the records you want printed on the report.  This will also allow you to create parameters to pass to the stored procedure to allow the user to enter criteria.

Let me know if you would like an example.  Send me your email address and I will send you a sample vb project.

bbosley
bbosley@pisc.com
EdRev (Programmer)
8 Jun 01 15:14
bbosley,
i was reading through this thread and it seem like i'm in the same boat as sam13. Do you mind posting a sample for both of us.

thanks,
Sam13 (TechnicalUser)
20 Jun 01 7:50
Basically,  here's what I ended up doing:

1. landscape  - still can't figure out how to landscape the data report. I ended up adjusting the margins of the data report so that all my fields could be displayed.  The reports now have a right and left margin of about 1cm instead of 1in. which will work for my purposes.
2. I stopped using parent/child relationships to define my report and used one command with a complex SQL statement and used the parameters and grouping sections of the command properties window.  I used the SQL builder to help in creating my SQL statement.  I learned more about what I can do with SQL this way.  I'm still by no means an expert though.  The SQL builder is a good tool for those of us who are just learning SQL.  I still don't know how to create a stored procedure.
3.  I'm no longer trying to use ADO controls with the data report object, for all my research it doesn't look like ado controls can be used with the data report object.
Helpful Member!  SimplyRed (Programmer)
22 Jun 01 5:55
the landscape setting (and all other printer settings like paper type) can only be set at the settings of your printer, so before you preview the report, you have to change the settings of your printer.

if you want to use ado recordsets in your data report, all you have to do is set up the proper fields your report will be bound into (by setting the datafield property to then name of the field in the recordset) then code the datasource property through code by assing the value of the recordset to it

example

Set DataReport.DataSource = ADOControl.DataSource

or

Set DataReport.DataSource = ADODB.Recordset

both works OK

hope i could help
JohnMacA (Programmer)
27 Jul 01 6:34
I have found altering printer settings in VB via the printer object frustrating, and usually ineffective. The reason seems to be that changes to the printer object only affect the main VB program, and not linked items like the Data Report designer! The only certain way to change printer settings in VB is to make changes to the system default printer via API calls - examples in MS Q198860 Q173981 Q190218.

To change the orientation of a Data Report in code, you can use:

datareport1.Orientation = rptOrientLandscape
uthamsar (Programmer)
1 Aug 01 16:32
Sending parameters to your report from user selected values from a combobox ( for that matter from any control):
Suppose your dataenvironment object name is 'deReports', your command name is 'country' (command name is where your sql is), your combobox name is 'cbosource', and 'dataReport1' is your report namethen do the following in your click event to generate the report:

deReports.country cboSource.List(cboSource.ListIndex)
DataReport1.Show

If you want grab the value selected by the user from the combo box into a variable (say 'vValue') and do the following:
deReports.country vValue
DataReport1.Show

You can send as many values depending on the number of parameters you are expecting in you sql separating them with a comma.
In the following example I am sending 3 parameters that I used for grouping in my sql

deReports.Command1_Grouping strRptCountry, strRptSource, strRptTopic

    rptCountry.Show

You can actually achieve the same result with ADO and without dataenvironment object.
Of course, I am able to see the data in my report. I am working on grouping fields using ADOS without dataenvironment object.

I hope this would help you to solve your problem


Guest (Visitor)
3 Aug 01 10:14
I have found this .DLL useful for setting my datareports from landscape to portrait and back again.

http://support.microsoft.com/support/kb/articles/Q198/9/01.ASP
whois (Instructor)
3 Aug 01 14:15
Just before you call the datareport.show method, set up the properties, such as:


         deReporting.comAuthors
         With drAuthorsDetail
          'height and width are for the reports container window, not the report  
            .Height = 13965
            .Width = 16965
            .Orientation = rptOrientLandscape
            .TopMargin = 400
            .BottomMargin = 400
            .LeftMargin = 400
            .RightMargin = 400
           'the caption is for the reports container as well.  
            .caption = strTitle
            .Show vbModal
         End With

There is alot more you can do - just play around a little.
Guest (Visitor)
13 Aug 01 12:29
Hello....
    Anybody having a problem with Data Report Designer.... to set the printer into landscape is so frustrating and this is the bug of Microsoft...

    If you are still using VB6 just update your Visual Studio Service Pack... visit the link below...

http://msdn.microsoft.com/vstudio/sp/vs6sp5/vbdownload.asp?id=10

Guest (Visitor)
16 Aug 01 15:12
I am also trying to use the Data Report Designer to generate a report with parameters.  My Data Environment name is simply 'DataEnvironment1', and my SQL statement is in a child command thats part of the details section of a grouping (I hope that makes sense ... there is only one grouping).  

My Click Event has the following code:

DataEnvironment1.Codes_Grouping List1.ListIndex
DeptReport_Detailed.Show

where List1 is a simple listbox containing numbers 1-12.

The problem is that I recieve the following error.  
Compile error: Wrong number of arguments or invalid property assignment.

My SQL statement is as follows:
SELECT * FROM tbl_FaxData WHERE DatePart("m",[AcknowledgedTime]) = k

I am using k as a variable name, because it tells me that the ? character is not a valid parameter name.  If I put in a value instead of k, the report prints out what I need it to.  I just can't seem to get the parameter part working.  
Can you tell me what I am doing wrong?

I am a beginner at using SQL with the Data Report Designer.  Any help would be much appreciated.
uthamsar (Programmer)
16 Aug 01 15:59
For Alan C (Data report designer)

Your code should be changed as follows:
DataEnvironment1.Codes_Grouping List1.List(list1.listIndex)
DeptReport_Detailed.Show

and to pass the parameter to your sql:
SELECT * FROM tbl_FaxData WHERE DatePart("m",[AcknowledgedTime])=?
Actually when you are in your data report design, select properties under your sqq(codes_grouping), the place where you see your SQL - under the 'General' tab - you can see your sql; then select 'Grouping' tab and then check mark 'group command object' and you must be able to see all the selected fields (in your case all the fields of that table as you have used '*') in the 'fields in command' and your grouping field in 'Fields used for grouping'; if this one looks ok, then select 'Parameters' tab and place and set the parameters properties - name, type etc., and place a ? in 'Parameters' box. As you are sending only one parameter, you do this once only. You can set as many parameters as you want but that number should match with the number of parameters (?) in your sql statement.
The sql should look similar to the following:
Select * FROM table a where lastname=?


I am not exactly sure about your datapart("m", ...
But I do not have any problems sending parameters to the report. If you want you could see my explanation on this topic on August 1 under 'uthamsar'. If you still need any clarifications, let me know.
Guest (Visitor)
20 Aug 01 21:03
Thanks for your quick response Uthamsar,

I am however still having some problems.  Maybe I am not understanding how all of this works ... I don't want to group the report any further.  The child command object where my SQL lies, is within the details section of a grouping.  I dragged the one field in the Summary section of the grouping to the first group header of the report(DeptGrouping_Header), and used another field from the details of that grouping to the group header just below the DeptGrouping_Header (called Codes_Header).  I then have a Details section just below Codes_Header on the report, and that is where I am trying to use the SQL statement to filter out the data I need.


I have tried adding the
DataEnvironment1.Codes_Grouping List1.List(List1.ListIndex)
but it still gives me the same error message:
Compile Error: Wrong number of arguments or invalid property assignment

I tried as you said selecting the 'Grouping' tab, and placing a check beside 'group command object', but then it tells me that at least one field must be used in the grouping.  I then selected the 'AcknowledgedTime' field and selected OK.  It then created another grouping under the Command object with the SQL in it (I think at this point I have gone too far because I don't need another grouping...) I dragged the 'AcknowledgedTime' field from the Summary of this grouping onto the details section of the report, but the same error occurs.

I feel that the structure of my DataEnvironment (with respect to groupings and child commands) is correct ... but that there's something else missing in the code ... please let me know if I am wrong.  

Any further help would be much appreciated.
uthamsar (Programmer)
23 Aug 01 12:24
To Alan C:
if you do not mind could you send me your files (in a zipped file). my e-mail address is ravinuthala@un.org.
 I used a grouping object and it was working finw for me. May be if I look into your code, I might be able to help you.
uthamsar (Programmer)
23 Aug 01 12:38
For Alan C:
Another quick check is in the following command
DataEnvironment1.Codes_Grouping List1.List(List1.ListIndex)
do instead this
DataEnvironment1.Codes_Grouping n
That n can be any valid number in your combo box.
 If you are still getting that error check the parameter tab and lookfor the data type

Could you please check in the parameters (when you go into the properties of your command object, check the parameter tab)- the data type. If you are sending a number from your combo box and the data type is advarchar or something, then you would get an error. It should be changed to either adNumeric or adInteger. Try that and see.

I have  feeling that this should work as you were saying that it was working if you hardcoded a number instead of k
Guest (Visitor)
27 Aug 01 1:48
I have a similar question regarding using stored procedures in data environment. Basically I have a query in Acces with a field name tranDate with criteria [param] How do I assign values to this in my data environment. When I try to do "where table.trandate = parameter", it places both [param] and parameter in my parameter list and then comes back saying "invalid name" for [param]. Also I tried table.tranDate[param] = parameter, but that sis not seem to work either. Any help ASAp would be greatly appreciated.
Guest (Visitor)
27 Aug 01 3:23
Hello
I have written a program to handle the printer specification and paper from vb programs . if you want the program source i can send it to you..
email address : rostami@scsco.net

but
uthamsar (Programmer)
27 Aug 01 9:33
To Gaurishankar

Under your connectionobject sql properties, select general tab; use SQl statement, for ex:
Select * from tablex where fieldname1=?
That question mark is important.
Then click parameter tab, you could see '?' under parameters, under 'paramater properties' typr param1, and select the data typr depending the type of parameter you are going to send (varchar , integer etc).

From your application, whatever event from where you want to run your report, grab the value you want to send it as the parameter ( for example when the user selected avalue ffrom a list box or combo box) and then do the following:
data
if codes is your command name in the data environment, datareport is your report name then
If DataEnvironment1.rscodes.State = adStateOpen Then
        DataEnvironment1.rsCodes.Close
    End If
    DataEnvironment1.codes the value you grabbed (the one you want to send it as a parameter
    Datareport.Show

If you prefer to send your code to me in a zipped file, you can do that, I am glad to help you out. My e-mail is ravinuthala@un.org
Guest (Visitor)
27 Aug 01 16:12
I am trying to bring data into a report from two different tables. I can bring both fields under the parent command but when I put it inside a report the application locks up and refuses to respnod. Any help would be greatly appreciated.
uthamsar (Programmer)
27 Aug 01 16:46
It is very difficult to understand what you are doing exactly. If you do not mind, semd me your code, I will look into the code. my e-mail address is Ravinuthala@un.org
Guest (Visitor)
27 Aug 01 23:43
I have another problem in creating reports. My report is formed by a form which select the date interval. The data in the report are okey. But... how can I put the selected date at the page header of the report?
Thank you.
StaceyG (Programmer)
28 Aug 01 9:15
Here is a sample where I am looking up specific information on a claim number that the user selects:
'requery using the selected claim number
    DataEnv.cmddisputebyclaimnum (strClaimNum)
'change the data report title to display the selected
'control number
    DRDisputebyClaimNum.Sections("Section4").Controls("LBLREPORTTITLE").Caption = "CLAIM NUMBER - " & strClaimNum
'show the data report
    DRDisputebyClaimNum.Show vbModal

'hope that helps
'Stacey
Crazyec (Programmer)
28 Aug 01 22:17
It really help. Thanks Stacey!
evandey (Programmer)
31 Aug 01 17:21
I am trying to run a data report from VB.  Instead of using the data environment, I am using an ADO recordset as my data source. I'm having trouble getting the data report to group by any of the fields in my recordset. I'm receiving the error "Report sections do not match data source". Can someone please help. Thanks.
Guest (Visitor)
4 Sep 01 11:29
I need to know what I need to do to get the parameters from my form to the data environment. I have the parameters set up in a SQL query to draw in dates for parameters. I have the variables public and the name of the variable is placed in the data environment under the correct parameter. I am apparently missing something. Any help would be greatly appreciated.
 
Guest (Visitor)
5 Sep 01 5:45
Can some one help me urgently, I am having a very bad prob with the data report. I have a column in my recordset that is displayed correctly. But I need to show anyvalue above 100000 in red and others in blue. How to do that???
Any idea..i don't have a clue regarding this...Pls Help!!!
Guest (Visitor)
6 Sep 01 10:12
I am trying to build a report that is drawing data from three different tables. One table holds the majority of the fields that need reported on. The other two only hole one field a piece that I need in my report. The problem I am having is that the two extra fields that reside in these two tables are a many to one fields. i.e. I have one instance of incident_number in the first table but have multiple instances in each of the other two tables. Can anyone help me? If so I thank you in advance.
Raj666 (Programmer)
12 Sep 01 6:29
I have created a datareport using dataenvironment.

The DE contains one command to which a parameter is passed.
I have used grouping thru the command properties.

But when I access the dataenvironment i get the error 'Data provider or other service returned an E_FAIL status'

My code ==> deEsop.command1 <<parameter>>

Can anybody throw some light on this.
Helpful Member!  briggsy79 (Programmer)
13 Sep 01 3:30
Is there anyway you can display only a single record in the datareport? I want to print a record that the user is loking at on a different form
Thanks
saling (Programmer)
14 Sep 01 2:59
Is it possible to display my data repeatedly in columns instead of rows?
Guest (Visitor)
17 Sep 01 22:53
Hi,

Regarding to Data Report Environment, every time the report is shown, then any subsequent modification to the database, the report does not update itself to reflect the changes when it called again. How do you fix this kind of problem?
For example, my report name is dabreport_cust, the event routine is like this.

  private sub report_cust_click()
     dbreport_cust.show
  end sub


Thanks

Regards
yputopia



sidanshu (Programmer)
18 Sep 01 8:34
hi yputopia
to Refresh your data report before showing it you have to refresh your connection object or you can just unload and load your data environment before showing the report.e.g.
use
 Unload DEtempdb 'detempdb is ur dataenvironment name
 Load detempdb

 dbreport_cust.Refresh or dbreport_cust.show

Hope that will spolve your problem.

Anshu
StaceyG (Programmer)
18 Sep 01 9:19
Sidanshu is correct. Here's an example:
deDVS is my data environment, rsBattererListQuery is my recordset within the data environment that acquires the data and DRBatterer is my report.

  Load deDVS
  With deDVS
    If .rsBattererListQuery_Grouping.State <> 0 Then .rsBattererListQuery_Grouping.Close
    .BattererListQuery_Grouping
  End With
DRBatterer.Refresh

DRBatterer.Show
Guest (Visitor)
18 Sep 01 20:23
Thanks sidanshu,
 
I simply added

 Unload/load DEtempdb

 ....

then it worked.

yptopia
Guest (Visitor)
18 Sep 01 20:23
Thanks sidanshu,
 
I simply added

 Unload/load DEtempdb

 ....

then it worked.

yputopia
Guest (Visitor)
26 Sep 01 15:43
hello
i am having some probs with data reports
i have to basically display items from a table.
but that table has only ID's in it .the names of the ids are in other table .Plus i am having probs in passing parameters from my form to data report
my id is tpsan@hotmail.com

san
vbaspprogrammer (Programmer)
30 Sep 01 3:36
Hi All!
   I'm getting error when passing a parameter to the dataenvironment.  

The parameter type is adVarChar and this is the way i'm passing my parameter

de1.com1 "ex1"

where de1 is the data environment and com1 is my command object.  By the way command object is having a child command object.  It has not got any parameters.  But still i'ld like to know how to pass parameters to the child as well.

pls. do reply ASAP.

Bye

ARAVINDA SARMA M.
Guest (Visitor)
2 Oct 01 5:11
still now i am unable to increase the width of my report.
if i increase it computer gives me message that the reportwidth is greater than the page width.
so please send me the solution.
Guest (Visitor)
8 Oct 01 3:58
Hi All!

I have one parent table and three child table so i created command1 for parent table and command2 and 3 for the child table but the problem is when i retrive the structure in Data Report it says "the report does not support multidimension hierarchies" ... is there any possible way to create a multi dimensional on the data report?

Please reply ASAP.

Kamesenin. (master_butan@yahoo.com)
Guest (Visitor)
13 Oct 01 18:43
Hi, yputopia

As you mentioned, I use the following:

Unload DEtempdb
Load DEtempdb
Datareport1.show 'datareport1 is my datarport's name.

It doesn't work, any suggestion. Thanks.

Guest (Visitor)
14 Oct 01 7:37
how I can send a select statement constructed in data environment as a parameter to the report created in data report designer .
I designed a form and according to the user's choice the select statement will be constructed ... the problem is how to send it to the report .

thanks a lot
Guest (Visitor)
16 Oct 01 18:49
hi all
i need some help with data reports
i am using vb and access as back end
i need to retieve recoreds between a specific date
so how do i do that
do i have the field in access as datatype-date or as text
when i am sending thru vb do i send as date or as text
like
data_env_name.conn_name date1
should date1 be type Date or text
i tried using test and date type in access
it gives error datatype mismatch
please could u reply as soon as possible
tpsan@hotmail.com




Guest (Visitor)
16 Oct 01 18:50
hi Kamesenin
i am getting the same error
dont know wht to do
if u are able to fix it please let me know
tpsan@hotmail.com
Helpful Member!  RACaluste (Programmer)
22 Oct 01 23:06
mohammed,

  you don't need a dataenvironment for that.. you can use ado and datareport to do that:

  dim conn as new adodb.connection
  dim rs as new adodb.recordset

  conn.open (your connection string)
  rs.open (insert your SQL statement here), conn
  set Datareport1.datasource = rs
  datareport1.show

  in your datareport, don't set a datasource, and in your textboxes, set the datafield to the field name corresponding to your recordset field names.

allan (arcanist@hotmail.com)
telescopi (IS/IT--Management)
25 Oct 01 8:02
Thats beautiful!

I've never used the dataenvironment, it seems so much more complex than just creating a recordset :)

But nowhere have I ever seen a description of using the data report without an environment, you are a star :)
aneem (Programmer)
25 Oct 01 22:33
Need help urgently!!!
I created a report using Datareport and Dataenvironment and print by
    drpPrinting.PrintReport False 'for disable printing dialog box
but it not work !! so I use a debug breakpoint between the print calls .. It work !!  
Thanks
Guest (Visitor)
30 Oct 01 3:51
Hi, I am working on the project that use database connected by SQL... I have been trying to group the data using GROUP BY, but for somereason I cannot make that work right... if any body can help me with GROUPING data, Appreciate it. thanks. BY the way I am trying to get data from two tables  and let user type the name so user can only see the data he/she chose..
thanks
SHog (Programmer)
1 Nov 01 1:49
HI, I am working on a datareport without using Dataenvironment. instead, I am using select statement to get data.. But I cannot group data . everytime I try I get error... for example, Model A has 4 function which a 2 As and 2 Bs.. and I want to remove duplicates.. but show the detail like below

MODEL A
  Function A  detail
              detail
  Function B  detail
              detail

also I want to get that on datareport..I want to use Model A, and FUnction as group header.. if anybody can help me with this problem appreciated..


LEWLEW (Programmer)
7 Nov 01 23:44
HEY I AM HAVING THE SAME PROBLEM WITH THE REPORT UPDATING WHEN I CREATE A EXE FILE CAN I GET SOME HELP MAYBE AN EXAMPLKE OR SOMETHING
Guest (Visitor)
8 Nov 01 21:37
sorry i'm new in the vb program
but iwant to know how make a report without using Crystal Report Designer
and i want to know how make a report between 10/11/2001 and 20/11/2001

thank's
Guest (Visitor)
8 Nov 01 21:40
sorry i'm new in the vb program
but iwant to know how make a report without using Crystal Report Designer
and i want to know how make a report between 10/11/2001 and 20/11/2001

thank's
Juice05 (Programmer)
14 Nov 01 10:59
I have a little problem, I have a form with multiplt text boxes and certain ones must not be blank so if the user selects a text box and doesn't put anything in it, a msgbox comes up telling them to fill the text box in. (This happens on the lost focus event). But if the user clicks on another text box that must be filled before filling in the first text box, I get stuck in an endless loop of msgboxes. This is happening because it is on the lost focus event, it has to get the focus of something else before it loses the text boxes focus and if another required text box gets focus then when it losses focus a msgbox comes up....over and over...I hope this is readable and any help would be appreciated.
Guest (Visitor)
21 Nov 01 0:29
i am having problem with data report. I am inserting a group header and place a rpttextbox there but when i run it, i am having this error, "report section do not match data source". please help me.
Migo (Programmer)
29 Nov 01 2:19
I'm new to data report. May I know how do I get a running no when displaying the records. Eg
1     ABC      $4.00
2     DEB      $4.50
Guest (Visitor)
6 Dec 01 18:11
I get run time errors - "Error Generating Termporary Filename" on all my executables when I try to open a data report in them.  It seems as though one of my DLLs is not registered, but after making sure all that are in my references list and all that the package and deployment wizard found are registered, I don't know what to do....can anyone help?

Thanks
Kyle
VBoy (IS/IT--Management)
6 Dec 01 22:18
Hi there;

Is there anyway to design a data report at run time?  I need to creat a very flexible report... Please help.

Richard
bhabesh (Programmer)
14 Dec 01 1:20
Dear Sir,

I got your reference in Net. Recently I faced a problem regarding DataReport in VB.

I declared connection, recordset and command  object in declaretion and provide the appropriate data source to datareport but don't know how dataField property of the text boxes placed in the form will be activated.  


Source code declared in the form is noted below for your kind reference.

Kindly advice me about the matter.


Thanking you

Bhabesh



Dim WithEvents cn As Connection
Dim WithEvents rs As Recordset
Dim ad As New ADODB.Command

Private Sub Command1_Click()

    Datareport1.Show
End Sub

Private Sub Form_Load()
     'Set ad = New Command
     Set cn = New Connection
    Set rs = New Recordset
    cn.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=smi3;UID=sa;PWD=smc3c;DATABASE=Cyber"
    cn.Open
    ad.CommandText = "Select * from FinStatus where TotAmt=?"
    ad.ActiveConnection = cn
    ad.Parameters(0).Value = 590
    
    Set rs = ad.Execute()
    
    
    Set     Datareport1.DataSource = rs
    
End Sub
Guest (Visitor)
25 Dec 01 16:16
Hello

i'd lke to tell you about a problem i have:

I'm working on a database application, that uses pictures! The pictures i use are not in the database but in a folder and i have a way to link each picture to a record in my database.

I want to get report  with photo on them. But the component DataReport that goes with VB6 doesn't allow to do that dynamically. Do u know a way to get through that! Or can you tell if there is another report component that can help?

Thank for helping!
Please write to me at mankiki@yahoo.com
Guest (Visitor)
26 Dec 01 15:34
Does anyone know how I can change the font (size, type, color) on a message box?
Guest (Visitor)
7 Jan 02 1:36
Hello,

well it's obvious that i have a problem that's why i posted it here coz i believe that the problem cannot be solve if you didn't ask about ur problem.

okay this is my problem well i put a line control on my datareport i just want like a table but the problem is if the line is in horizontal position it couldn't stretch when the textbox has grown in size so every row in the table has a gap lines ... i tried to look in the RptLine properties but theres no property named "can grow" ... it makes me frustrated right now i dunno how can i solve this problem so if you have an idea and you know how to solve it i'll i appreciate it.

thanks!

Kamesenin Butan
(slave programmer)

master_butan@yahoo.com
mdmartin1 (TechnicalUser)
7 Jan 02 13:34
Hello, I am trying to create a Data Report that will be generated from parameters received through input. I am using the following command to pass the data.
Private Sub Command2_Click()
    Dim sql As String, User As String
    sql = "select * from main"
    User = Text1.Text
    sql = sql & " where user = '" & User & "'"
    DataEnvironment1.Commands("Command1").CommandText = sql
    DataReport1.Show
End Sub
This command works find until I add groupings to the Data Environment Command1.  When I do this I get an error when I run this code.  I think I need additional code but am not sure what it would be.  Any help would be appreicated.
Thank you
VijayMehta2002 (Programmer)
9 Jan 02 10:10
Need Help to Use Data Reports.

I want to print 10 lines of output on a standard
page using a standard dot matrix printer and then
stop printing. Also the page should not eject to
the next page.

Guest (Visitor)
27 Jan 02 8:57
Hi!!!

Can anyone tell me how to suppress repeating values in the detail section of the report? Is it possible? If not, how can i have two group section in one report? Hope you can help me guys.   Thanks!!!

This is the sample i want my report to look like.

Accout no.   Invoice     items     amount

1111111111    2aaaaaaa    xxxxxx    1250.20
                     3aaaaaaa    ffffff        250.25      
                                       zzzzzz     255.00

2222222222    4ddddddd    yyyyyy     253.35
 
Sahel (Programmer)
5 Feb 02 7:50
Hi .. if ur using SQL query u can use "SELECT DISTICNT field1,field2 FROM tablename WHERE CLAUSE"

This way ur recordset will return only values once .. no duplicates ..
smitad (Programmer)
12 Feb 02 6:13
hello,

 Is it possible to design a datareport at run time?  What I try to do is to create a very flexible report to have different coloumn names and amount of coloumns to display at the report.

 Report Example 1

 Column1   Column2   Column3   Column4
  
 Report Example 2

 Column1   Column2   Column3

 
As you can see, the report is not set with a exact amount of coloumns, and each coloumn might have different names.  Instead of having design all the different combination of reports at design time, which could be many, can I just create it by using codes..

Hopes you know what I try to say

Thanks for all your help

smita
Guest (Visitor)
13 Feb 02 7:21
Anybody know how to disable the print and export controls in the datareport. I want users to be able to view a report but not print or export it?


Appreciated.
Guest (Visitor)
13 Feb 02 10:06
Does anybody know how to create a Mailing Labels report by using Visual Basic Data Report Designer?
Guest (Visitor)
14 Feb 02 12:33
Can any one tell me how  can I generate any data report with serial no.. not Field in the Database Table
Guest (Visitor)
8 Mar 02 11:45
I'm have an Access database with different variables that will determine what report I will be printing.  However, I define a variable as a DataReport and I can't get the .show command to appear.  Here's my code:

Dim sReportName As DataReport

With rsReportList
    .Index = "SecondaryKey"
    .Seek "=", sRptLbl, sRptCat, sRptName, sRptSort, sRptFilter
    sReportName = !TechName
End With


I want to add sReportName.show but the .show command won't appear.  Any thoughts?
Guest (Visitor)
1 Apr 02 15:08
J'ai besoin d'installer un exécutable contenant un report. Mon exécutable(contenant juste un data environment et un data report) fonctionne parfaitement sur mon poste qui a VB 6.0 pro. Si  je l'installe l'exe avec le runtime de VB sur un autre poste:pb de classe non enregistrée
Quels sont les outils exacts à installer sur un pc qui n'a que le runtime de VB 6.0 de façon à pouvoir utiliser les reports correctement?
Faut-il ajouter des adresses dans la base de registre?
Quelles sont les dll et ocx à installer en plus du runtime?
NaijaMan (Programmer)
5 Apr 02 1:47
The orientation of a printed VB report is set to the current state of the default printer at the time of printing (Portrait or Landscape) the report, to programmatically change this before printing, ensure that you have VB service pack 5 installed and use the following line of code

 DataReport.Orientation = [value]

 Where [Value] can be any of the following constants:
    
   vbPRORPortrait  (For portrait)
   vbPRORLandscape (For Landscape)
Guest (Visitor)
6 May 02 11:04
Good day

i've been working with my project in about two months now i solve the landscape problem but how can you enlarge your report width to match the long bond paper size?

have suggestions! please post it 4 me thanks!

Jampaz

Guest (Visitor)
7 May 02 22:36
I have a recordset that was generated by a ADOs command with commandtype=storeprocedure, this is the source that i use for datareport, the problem is that in the rptTextbox the information start since the second register, so if i have a recordset with one register doesn't appear anything...
somebody can help me...
jenriquecastilla@hotmail.com
Guest (Visitor)
7 May 02 22:38
I have a recordset that was generated by a ADOs command with commandtype=storeprocedure, this is the source that i use for datareport, the problem is that in the rptTextbox the information start since the second register, so if i have a recordset with one register doesn't appear anything...
somebody can help me...
jenriquecastilla@hotmail.com
dontee (Programmer)
9 May 02 7:09
I've just started working with Data Environments and Reports and will appreciate some help. I set up the data environ and report but when I try to run/preview the report, I get the message "Failed getting Rowset(s) from current data source". I've tested the connection and it's good. Can anyone suggest a solution, please??
Guest (Visitor)
13 May 02 10:22
Thanks for the info on using Datareport designer shall try it out and strike back with similiar kind of questions . Expecting all ya people cooperation. Thanks in advance.

regards
Prashant.P.R.
ke6izp (Programmer)
14 May 02 13:36
dontee,

That's because, by default, queries aren't defined as returning recordsets. I had the same problem. To fix it:

Select your Data Environment.
View the control.
Right click the command, select properties.
Click the 'Advanced' tab
Turn on the 'Recordset Returning' check box.

Jim

vb6Access (Programmer)
20 May 02 16:30
I am writing a report in the vb6 data report.  I understand how it works, but I would like to manipulate the group headers to print one heading on page one of the report and another heading on page 2.  How would I set this up?  Thanks in advance
Guest (Visitor)
23 May 02 8:30
I have a question regarding parameters to child commands in the DataEnvironment. I will try to explain the problem with a simple example.
Example:
  Parent-
    Table:PetOwner
      ID
      Name
      Adress
  Child-
    Table:Pet
      ID
      Name
      Gender
      Age
      
The relation is ID = ID ofcourse.
I would like to use the same report to show:
  1) PetOwner and his/hers pets with an age < 2
  2) PetOwner and his/hers pets with name = 'Peter'
  3) PetOwner and his/hers pets with Gender = male

Is it possible to have some variables/parameters for this or do I have to do three different reports?

(I guess that this is something that is very common?!)
vb6Access (Programmer)
23 May 02 9:43
I have had the same problem.  I try to pass parameters in the data environment, but I have it doesn't work trying to pass the parameters to the child table if those fields are not in the parent table.  If you find out how to do this, please let me know.

If you use the Child table as the parent table, then you can run as many parameters as you want (1 report using 3 parameters).  I don't know if this answers your question or not?
Guest (Visitor)
23 May 02 10:26
vb6Access

Thank you for your reply.
Unfortunately your reply doesn't solve my problem.

I know how to use parameters in th 'parent' command. My problem is that I would like to pass the parameters to the child command.
In this way I would be able to use the same commands & report to make different reports depending on the parameters.

The obvious solution is to make more commands. (They will be almost identical. But I will not be able to make 'dynamic' querys like I want.) This is something that I would like to avoid.

Is there anyone that has had the same problem?
Guest (Visitor)
23 May 02 11:53
I could manage to do what I want if I could change the CommandText of the child command. (CommandText~=SQL command)
I know how to set the CommandText of the 'parent', but I cannot do it for the child command.

This is how I do it for the 'parent' command:
  DataEnv.Commands("cmdPetOwner").CommandText = "SELECT * FROM PetOwner"

Is there anyone that knows how to find and change the child CommandText and parent relations?

 
Guest (Visitor)
2 Jul 02 19:30
I'm trying to set a value in my Report to not Visible from my code, but I cant figure out how to access an Object in the Report......
leh (TechnicalUser)
3 Jul 02 5:02
i've been using vb datareport, but i found some problems, if there are still group details to be printed on the next page the group header will not appear.  I want the group header be printed on the next page if there are still group details left.  Anybody can help me, I will appreciate.

Thank you,
leh
Guest (Visitor)
23 Jul 02 8:08
I am also having huge struggles with datareports. Basically I have managed to print my reports and make it look nice and this all from using the dataenvironment. I use a SQL statement in the dataenvironment which will basically retrieve all the records. For ex; "SELECT * FROM History"
My question is how can I, during runtime, add a WHERE-clause to the statement which will reference to a value in a textbox during runtime for ex; "SELECT * FROM History WHERE Reference = '" & text1 & "'"
I have tried everything but nothing seems to work. My report shows all the records and this is not what I want. Does anyone have any suggestions?
chernandez2000 (Programmer)
23 Jul 02 10:34
hi vbwill

Its kinda early for me, but let me show the syntax I used for such a SQL statement :
"SELECT * FROM History WHERE Reference = " & Chr(39) & text1 & Chr(39) & "
'chr(39) is of course a single quote

Also, would a refresh of some property in the dataenvironment be in order after the SQL statement?

Good luck
chernandez2000 (Programmer)
23 Jul 02 10:42
Also here's a way to get a data report without using the dataenvironment:

    Dim cn_ForReport As ADODB.Connection
    Dim rs_ForReport As ADODB.Recordset
    Dim strSQL       As String
    
    Set cn_ForReport = New ADODB.Connection

    cn_ForReport.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & whateverpathyourdbisat & ";" & _
        "Persist Security Info=False"
  
    cn_ForReport.Open
    
    strSQL = "SELECT * FROM History WHERE Reference = " &   Chr(39) & text1 & Chr(39) & "

    Set rs_ForReport = cn_ForReport.Execute(strSQL)
          
    ' Connect the Recordset to the DataReport.
    Set WhateverReportName.DataSource = rs_ForReport

Good luck amigo
       
Guest (Visitor)
24 Jul 02 9:19
I AM TRYING TO CREATE A REPORT THAT CAN SHOW PICTURES STORED IN THE DATABASE ALONG WITH OTHER FEILDS. I TRIED USING THE REPORT IMAGE CONTROL BUT AS  THE REPORT IMAGE CONTROL DO NOT HAVE DATASOURCE,DATAMEMBER AND DATAFEILD PROPERTIES. I AM WONDERING IF SOME ONE CAN HELP ME ON THIS.
dwray (Programmer)
24 Jul 02 9:56
CHERNADEZ2000

IS there a way to incorperate grouping levels without using a data environment?

Thanks.

Dom
chernandez2000 (Programmer)
24 Jul 02 11:34
dwray

There may be a way, but I believe I was trying to figure that out once and got a headache .

You may want to look into "hierarchical recordsets". Not sure but that may open up some doors for you.

Good luck.
dwray (Programmer)
24 Jul 02 13:01
CHERNADEZ,

Thanks, I actully found an example in the MSDN Knowledge base.  You can link to it here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q289793

(sorry, I don't know how to post a link)

Here is the text:

HOWTO: Dynamically Populate a Group Data Report in Visual Basic
The information in this article applies to:
Microsoft Visual Basic Enterprise Edition for Windows 6.0, 6.0 SP3, 6.0 SP4
ActiveX Data Objects (ADO) 2.0, 2.1, 2.1 SP2, 2.5, 2.6, 2.7

Summary
This article explains how to create a Group Data Report programmatically without binding it to any data at design time.
More Information
Step-by-Step Example
Open a Standard EXE project in Visual Basic. Form1 is created by default.
Add a Command button to Form1.
On the Project menu, click References, and then add a reference to Microsoft ActiveX Data Objects Library.
On Project menu, click to add DataReport1. If Add Datareport is not on the Project menu, add it from the Designers tab located on the Project menu under Components. (Make sure that you deselect Selected Items Only in the Project menu under Components.)
Open Datareport1 and ensure that the Detail, Page Header, and Page Footer sections are visible. If the Report Header or Footer sections are visible, remove them by right-clicking the report and removing the check mark from Show Report Header/Footer.
Right-click and select Insert Group Header/Footer. A new Section, Section4, is added to the Datareport.
Add one rptLabel control and one rptTextbox control to the new section.
Add two rptLabel controls and two rptTextbox controls to the Detail section of the Datareport.
Copy and paste the following code into the form:
      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim cmd As New ADODB.Command
      Dim rs1 As New ADODB.Recordset

      Private Sub Command1_Click()

      Dim q As Integer
      Dim intCtrl As Integer
      Dim x As Integer
      Dim z As Integer
      x = 0
      q = 0
      z = 0

      With DataReport1
      .Hide
      Set .DataSource = rs
      .DataMember = ""
      With .Sections("section4").Controls
      For intCtrl = 1 To .Count
      If TypeOf .Item(intCtrl) Is RptLabel Then
         .Item(intCtrl).Caption = "City" & " :"
          q = q + 1
      End If
      If TypeOf .Item(intCtrl) Is RptTextBox Then
        .Item(intCtrl).DataMember = ""
        .Item(intCtrl).DataField = "City"
      End If
      Next
      End With
      
      q = 0
      With .Sections("Section1").Controls
          For intCtrl = 1 To .Count
          If TypeOf .Item(intCtrl) Is RptLabel Then
                   .Item(intCtrl).Caption = rs1.Fields(q).Name & " :"
                    q = q + 1
          End If
          If TypeOf .Item(intCtrl) Is RptTextBox Then
                   .Item(intCtrl).DataMember = "Command1"
                   .Item(intCtrl).DataField = rs1(z).Name
                   z = z + 1
          End If
      Next intCtrl
      End With
      .Refresh
      .Show
      End With
      End Sub

      Private Sub Form_Load()

      Command1.Caption = "Show Report"
      cn.Open "Provider=MSDATASHAPE; Data Provider=Microsoft.JET.OLEDB.4.0;" & _
               "Data Source=D:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;"
       
      With cmd
           .ActiveConnection = cn
           .CommandType = adCmdText
           .CommandText = " SHAPE {SELECT FirstName,Lastname,City FROM `Employees`}  AS Command1 COMPUTE Command1 BY 'City'"
           .Execute
      End With
       
      With rs
           .ActiveConnection = cn
           .CursorLocation = adUseClient
           .Open cmd
      End With
      Set rs1 = rs(0).Value

      End Sub
References
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
Q240019 HOWTO: Dynamically Populate a Data Report in Visual Basic

There is a lot of unecessary code here used to identify text boxes in the different sections and assign a Data Field, but this can be ignored if you already assign the datafield property in the Data Report and set the DataMember to command1.

I'm not sure if I understand all of this but it worked for me.
chernandez2000 (Programmer)
24 Jul 02 13:22
Excellent young Jedi.
Guest (Visitor)
19 Aug 02 1:49
I was able to get that howto thing microsoft posted but I don't understand it.

This is what I'm doing.  I created a data report and added a group. Then, on a form, I have a command button that shows that form.  My code works well if I remove the group from the data report but if I dont I get an error message that says something about the data structure doesn't match.

Here is my code.  Hope you can help me.


Private Sub cmdProdHist_Click()
Dim ProdChosen As String

    ProdChosen = InputBox("Enter Product:", "Product")
    
        Call Open_Connection
    
    rsSales.CursorLocation = adUseClient
    rsSales.CursorType = adOpenDynamic
    rsSales.LockType = adLockPessimistic

        rsSales.Open "SELECT Date, [sales invoice number], [product name], Quantity " _
        & "FROM SALES WHERE [product name] like '" & ProdChosen & "%' group by [sales invoice number], [product name], date, quantity", cn
        
    
    
    With rptProdHist
       Set .DataSource = rsSales
        .DataMember = ""
        
        
        With .Sections("Section1").Controls
            .Item("txtDate").DataMember = ""
            .Item("txtDate").DataField = rsSales.Fields(0).Name
            .Item("txtInvNum").DataMember = ""
            .Item("txtInvNum").DataField = rsSales.Fields(1).Name
            .Item("txtQty").DataMember = ""
            .Item("txtQty").DataField = rsSales.Fields(3).Name
            .Item("txtloose").DataMember = ""
            .Item("txtloose").DataField = rsSales.Fields(2).Name
             
        End With
        
        'With .Sections("section4").Controls
         '   .Item("Lblprod").Caption = rsSales.Fields(2).Value
        'End With
        
        'With .Sections("section7").Controls
            
         '   .Item("label9").Caption = "txtQty"
            
        'End With
            
        .Orientation = rptOrientPortrait
        .Show
        .WindowState = 2
        
    End With

End Sub
BjLim010182 (Programmer)
19 Aug 02 1:58
I was able to get that howto thing microsoft posted but I don't understand it.

This is what I'm doing.  I created a data report and added a group. Then, on a form, I have a command button that shows that form.  My code works well if I remove the group from the data report but if I dont I get an error message that says something about the data structure doesn't match.

Here is my code.  Hope you can help me.


Private Sub cmdProdHist_Click()
Dim ProdChosen As String

    ProdChosen = InputBox("Enter Product:", "Product")
    
        Call Open_Connection
    
    rsSales.CursorLocation = adUseClient
    rsSales.CursorType = adOpenDynamic
    rsSales.LockType = adLockPessimistic

        rsSales.Open "SELECT Date, [sales invoice number], [product name], Quantity " _
        & "FROM SALES WHERE [product name] like '" & ProdChosen & "%' group by [sales invoice number], [product name], date, quantity", cn
        
    
    
    With rptProdHist
       Set .DataSource = rsSales
        .DataMember = ""
        
        
        With .Sections("Section1").Controls
            .Item("txtDate").DataMember = ""
            .Item("txtDate").DataField = rsSales.Fields(0).Name
            .Item("txtInvNum").DataMember = ""
            .Item("txtInvNum").DataField = rsSales.Fields(1).Name
            .Item("txtQty").DataMember = ""
            .Item("txtQty").DataField = rsSales.Fields(3).Name
            .Item("txtloose").DataMember = ""
            .Item("txtloose").DataField = rsSales.Fields(2).Name
             
        End With
        
        'With .Sections("section4").Controls
         '   .Item("Lblprod").Caption = rsSales.Fields(2).Value
        'End With
        
        'With .Sections("section7").Controls
            
         '   .Item("label9").Caption = "txtQty"
            
        'End With
            
        .Orientation = rptOrientPortrait
        .Show
        .WindowState = 2
        
    End With

End Sub
Deepak (MIS)
26 Aug 02 8:11
How To Change the Forecolor of rptText at runtime at a given condition.
Say
If ROL < 100 Then
     Sections("Section1").Controls(1).ForeColor = vbRed
Else
     Sections("Section1").Controls(1).ForeColor = vbBlue
End IF
Where to write this code.
Thanks in Advance
my email is deepak_adlakha@hotmail.com
Regards
Deepak
nimit (MIS)
23 Sep 02 0:20
I am using DataReports with MSaccess.
I have stored the date in the MSAccess as yyyyMMdd format as text,so that i can use this format for comparison purposes.

My Problem is when i generate report ,in the front end the format is again yyyyMMdd format for dates while i need it in
dd/mm/yyyy format.

Please solve this tricky problem.
However Please note i have tried the following
a) Using date format in VB ,unfortunatly it desnt work with
yyyyMMdd format.
b) using stored procedure,it also doesnt seems to work

Please do advice me and if possible give my a handy solution.

nimit (MIS)
23 Sep 02 0:22
I am using DataReports with MSaccess.
I have stored the date in the MSAccess as yyyyMMdd format as text,so that i can use this format for comparison purposes.

My Problem is when i generate report ,in the front end the format is again yyyyMMdd format for dates while i need it in
dd/mm/yyyy format.

Please solve this tricky problem.
However Please note i have tried the following
a) Using date format in VB ,unfortunatly it desnt work with
yyyyMMdd format.
b) using stored procedure,it also doesnt seems to work

Please do advice me and if possible give my a handy solution.

bluenoser337 (Programmer)
27 Sep 02 13:39
Sorry, I posted twice (once in wrong forum)...

Have a DB stored procedure (SQLServer 7.0) that gives me columns I'll call A,B,C,D,E,F group by A,B,C,D,E,F order by A,B,C,D,E,F.  In Data Environment I set the Database Object/Name as my stored procedure and I can print out the values with A,B,C,D,E,F chosen for grouping, but I can't get the report arranged properly.  In other words, I want all of group A to contain subgroup B, to contain subgroup c, etc... divided up acordingly.
City 1(A)
    Store 1,2,3,etc (B)
       Salesperson 1,2,3,etc (C)
           Widgets 1,2,3,etc (D)   etc
City 2 (A)
    Store 1,2,3,etc (B)
       Salesperson 1,2,3,etc (C)
           Widgets 1,2,3,etc (D)   etc                    
The report is displaying City 1 many times instead of just group headings for the cities.  I've tried everything but I keep getting errors that the report sections do not match the data source.  I've done "retrieve structure".  Wondering if the problem is with my stored procedure or report designer.  New with this Data Environment business.
Any help greatly appreciated.
tonioJ (Programmer)
10 Oct 02 7:16
HELLO HOW DO YOU GROUP DATA IN DATA REPORT? M GETTING A ERROR: "REPORT SECTION DO NOT MATCH DATASOURCE" , M USING DATA ENVIRONMENT FOR DATASOURCE.. THANK YOU
bluenoser337 (Programmer)
10 Oct 02 11:55
Microsoft has a tutorial for Data Environment and Report Designer.  It's not the greatest but it's something.  Good luck.  I'm still lost with a lot of this.  If you find any samples, let me know.  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconusingdewithyourapp.asp
bsarbaugh (Programmer)
22 Oct 02 10:20
I am new to Visual Basic and am having a problem with the Data Report Designer.  I can get the reports to refresh etc, but either the entire detail section of the report is blank or certain peices of data are missing.  When I check the database, they are there.  Here the other twist.  When I run the code in debug mode and step through it, the reports appear fine.

What is happening in Run mode to cause this?  I am using the following code to view the report.

Load deReport
    With deReport
        If .rscomList.State <> 0 Then
            .rscomList.Close
        End If
    End With
   
    If rptList.Visible = False Then
        rptList.Refresh
        rptList.Show
    Else
        rptList.Refresh
        rptList.SetFocus
    End If
bluenoser337 (Programmer)
24 Oct 02 14:33
Reply to chernandez2000 from July 23, 2002...thank you...thank you...for this simple code to get results from an SQL statement into my report.  I just have to work on how to properly display the grouping now...so I stop seeing that "report sections do not match..." message.
Helpful Member!  EnS (Programmer)
29 Oct 02 19:20
Hi All
I am having problems with my data report.  But first, let me thank all of you for your postings.  I am working on my first vb project and all of your suggestions have help me get furthur faster then if I were to figure it out myself.  Thanks again.

However, I have run in to a bit of a snag!  I have a vb project connected to an access db.  I am using the Jet 4.0 OLD DB Provider for this connection.  Just some background info.  Anyway, I have created a data report using a parent command and am having trouble with the passing of a referenced variable to the sql while keeping the grouping option viable!  When I exclude the WHERE clause, or if I hard code the condition of the WHERE clause, the report works fine.  But when I change the condition to WHERE <table_name.field> = ?, I lose my grouping option.  Can anyone explain to me why?

**************************************************************
My sql is as follows:

TRANSFORM
     SUM(SCORING_INFO.SCORE) AS CAT_SCORE
SELECT
     GENERAL_INFO.RECORD_NO,
     GENERAL_INFO.AUDIT_DATE,
     (EMP_F_NAME &' '& EMP_L_NAME) AS EMP_NAME,
     GENERAL_INFO.AUDITOR,
     GENERAL_INFO.RESOLVED_OUTAGE_COUNT,
     GENERAL_INFO.OUTAGE_REVIEWED_COUNT,
     GENERAL_INFO.REVIEWED_PERCENT,
     GENERAL_INFO.OVERAL_PERCENTAGE,
     SCORING_INFO.FC_NO,
     SCORING_INFO.ATM_NO,
     SCORING_INFO.WORK_OF_DATE,
     SCORING_INFO.RESOLUTION_DATE,
     OUTAGE_INFO.OUTAGE_DESC,
     GENERAL_INFO.COMMENTS,
     SUM(SCORING_INFO.SCORE) AS LINE_SCORE
FROM
     (EMPLOYEE_INFO INNER JOIN GENERAL_INFO
     ON EMPLOYEE_INFO.EMP_NO = GENERAL_INFO.EMP_NO)
     INNER JOIN (AUDIT_INFO INNER JOIN (OUTAGE_INFO INNER JOIN
     SCORING_INFO ON OUTAGE_INFO.OUTAGE_CODE =
     SCORING_INFO.OUTAGE_CODE) ON AUDIT_INFO.AUDIT_CODE =
     SCORING_INFO.AUDIT_CODE) ON GENERAL_INFO.RECORD_NO =
     SCORING_INFO.RECORD_NO
WHERE
     GENERAL_INFO.RECORD_NO = ?
GROUP BY
     GENERAL_INFO.RECORD_NO,
     GENERAL_INFO.AUDIT_DATE,
     (EMP_F_NAME &' '& EMP_L_NAME),
     GENERAL_INFO.AUDITOR,
     GENERAL_INFO.RESOLVED_OUTAGE_COUNT,
     GENERAL_INFO.OUTAGE_REVIEWED_COUNT,
     GENERAL_INFO.REVIEWED_PERCENT,
     GENERAL_INFO.OVERAL_PERCENTAGE,
     SCORING_INFO.FC_NO,
     SCORING_INFO.ATM_NO,
     SCORING_INFO.WORK_OF_DATE,
     SCORING_INFO.RESOLUTION_DATE,
     OUTAGE_INFO.OUTAGE_DESC,
     GENERAL_INFO.COMMENTS
ORDER BY
      GENERAL_INFO.RECORD_NO,
      AUDIT_INFO.AUDIT_DESC
PIVOT
      AUDIT_INFO.AUDIT_DESC

my click event is as follows:

Private Sub Command5_Click()
     
     auditDataEnvironment.Command1_Grouping txtRecord_No
     DataReport1.Show

End Sub

my Parameter info is as follows:
    Parameter Name: Param1
    Direction : Input
    Data Type: asInteger
    Precision: 0
    Scale: 0
    Size: 4
    Host Data Type: Long(VT_14)
    Required: True
**************************************************************

As I explained earlier, if I exclude the WHERE clause or set the WHERE clause to say,
WHERE GENERAL_INFO.RECORD_NO = 1,
then my grouping option will work and the report will print fine.  But if I place the WHERE clause as shown above and select the Parameters tab in the property dialog and set all the needed parameter properties, I lose my grouping!

Any help would be greatly appreciated!
Thanx

ERM

chernandez2000 (Programmer)
30 Oct 02 10:34
Bluenoser - glad it helped you out.

If you are still working on the Report Sections Do Not Match problem, here's a link in MSDN (IF you dont happen to have a copy, got to MSDN.MICROSOFT.COM) -  Do a search for:

PRB: 8570 Report Sections Do Not Match DataSource
EnS (Programmer)
30 Oct 02 19:17
F.Y.I.

After several brain splitting and frustrating hours, I found an answer.

Instead of creating a parent command in my DE, I placed a SHAPE
statement in the data report initialization sub.  

I first had to changed my connection string form
provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;
     Data Source=<path\db.mdb>
to
provider=MSDataShape.1;Data Source=<path\db.mdb>;
     Data Provider=Mirosoft.Jet.OLEDB.4.0

I then placed the following code in DataReport_Initializate()

Dim rs as Recordset

With DE.Connection
     Set rs = .Execute _
          ("SHAPE {TRANSFORM SUM(SCORING_INFO.SCORE) AS CAT_SCORE " _
          & "SELECT " _
          & " GENERAL_INFO.RECORD_NO, " _
          & "GENERAL_INFO.AUDIT_DATE, " _
          & "(EMP_F_NAME &' '& EMP_L_NAME) AS EMP_NAME, " _
          & "GENERAL_INFO.AUDITOR, " _
          & "GENERAL_INFO.RESOLVED_OUTAGE_COUNT, " _
          & "GENERAL_INFO.OUTAGE_REVIEWED_COUNT, " _
          & "GENERAL_INFO.REVIEWED_PERCENT, " _
          & "GENERAL_INFO.OVERAL_PERCENTAGE, " _
          & "SCORING_INFO.FC_NO, " _
          & "SCORING_INFO.ATM_NO, " _
          & "SCORING_INFO.WORK_OF_DATE, " _
          & "SCORING_INFO.RESOLUTION_DATE, " _
          & "OUTAGE_INFO.OUTAGE_DESC, " _
          & "GENERAL_INFO.COMMENTS, " _
          & "SUM(SCORING_INFO.SCORE) AS LINE_SCORE " _
          & "FROM " _
          & "(EMPLOYEE_INFO INNER JOIN GENERAL_INFO " _
          & "ON EMPLOYEE_INFO.EMP_NO = GENERAL_INFO.EMP_NO) " _
          & "INNER JOIN (AUDIT_INFO INNER JOIN (OUTAGE_INFO INNER JOIN " _
          & "SCORING_INFO ON OUTAGE_INFO.OUTAGE_CODE = " _
          & "SCORING_INFO.OUTAGE_CODE) ON AUDIT_INFO.AUDIT_CODE = " _
          & "SCORING_INFO.AUDIT_CODE) ON GENERAL_INFO.RECORD_NO = " _
          & "SCORING_INFO.RECORD_NO " _
          & "WHERE " _
          & "GENERAL_INFO.RECORD_NO = "& <entered value> &" " _
          & "GROUP BY " _
          & "GENERAL_INFO.RECORD_NO, " _
          & "GENERAL_INFO.AUDIT_DATE, " _
          & "(EMP_F_NAME &' '& EMP_L_NAME), " _
          & "GENERAL_INFO.AUDITOR, " _
          & "GENERAL_INFO.RESOLVED_OUTAGE_COUNT, " _
          & "GENERAL_INFO.OUTAGE_REVIEWED_COUNT, " _
          & "GENERAL_INFO.REVIEWED_PERCENT, " _
          & "GENERAL_INFO.OVERAL_PERCENTAGE, " _
          & "SCORING_INFO.FC_NO, " _
          & "SCORING_INFO.ATM_NO, " _
          & "SCORING_INFO.WORK_OF_DATE, " _
          & "SCORING_INFO.RESOLUTION_DATE, " _
          & "OUTAGE_INFO.OUTAGE_DESC, " _
          & "GENERAL_INFO.COMMENTS " _
          & "PIVOT " _
          & "AUDIT_INFO.AUDIT_DESC} AS Commad1 " _
          & "COMPUTE Command1 BY 'RECORD_NO', " _
          & "'AUDIT_DATE','EMP_NAME','AUDITOR', " _
          & "'RESOLVED_OUTAGE_COUNT', " _
          & "'OUTAGE_REVIEWED_COUNT', " _
          & "'REVIEW_PERCENT', " _
          & "'OVERALL_PERCENTAGE','COMMENTS'")
End With

Set Me.DataSource = rs

Me.Refresh

End Sub

I then went and set the datamember property for all textboxes
contained in the detail section of the report to reflect <Command1>
and left all the datamember property for all the textboxes contained
within the header and footer as blank.  The report now runs like a dream.

Hope this helps out somebody.

ERM

Schmendrick (Programmer)
4 Nov 02 2:03
I am building a report using Datareport Designer basically just for formating. (I have a bunch of other reports and want this one to look the same.) Anyway. I placed a bunch of labels in the report and filled them in with code like this:
EmployeeBalance2.Sections("Section1").Controls("label10").Caption = Ctotal
EmployeeBalance2.Sections("Section1").Controls("label11").Caption = CHtotal

It all works great... except that it prints it all twice!

label 10    Ctotal
label 11    CHtotal

label 10    Ctotal
label 11    CHtotal

Why is that???

Thanks in advance!
bluenoser337 (Programmer)
4 Nov 02 7:11
Schmendrick...can't answer your question but it makes me ask another one...where do you place the code for changing label captions, etc.  Does it go in the report itself?  In what kind of sub? (new to me) THANKS!
alanf (Programmer)
4 Nov 02 8:44
Schmendrick...Have you checked the results of your query before it goes into the report? Maybe everything is coming up twice in the query because of your join condition.
Schmendrick (Programmer)
4 Nov 02 12:25
bluenoser337... You can place the code anywhere in your program itself... I havent tried putting anything in the report (new to the designer)

alanf... Thats not it (unfortunatly). I had the same query printing to a listbox before and it worked great (just looked ugly)

Here is part of the sub that creates/calls the report:

SQLtext = "SELECT * FROM INVOICE WHERE SOLDBY = '" & User & "';"
Set rst = Login.db.OpenRecordset(SQLtext)
        If rst.RecordCount = 0 Then
                Msgbox "No Transactions for this user on this date"
                Exit Sub
        End If
rst.MoveLast: rst.MoveFirst
        Do Until rst.EOF
            If rst.Fields("HOWPAID") = "MC    " Then
                Mtotal = Mtotal + Val(rst.Fields("TOTALAMOUNT"))
                MCount = MCount + 1
            ElseIf rst.Fields("HOWPAID") = "Visa " Then
                Vtotal = Vtotal + Val(rst.Fields("TOTALAMOUNT"))
                VCount = VCount + 1
            ElseIf rst.Fields("HOWPAID") = "Discover" Then
                Dtotal = Dtotal + Val(rst.Fields("TOTALAMOUNT"))
                DCount = DCount + 1
            ElseIf rst.Fields("HOWPAID") = "Check" Then
                CHtotal = CHtotal + Val(rst.Fields("TOTALAMOUNT"))
                CHCount = CHCount + 1
            ElseIf rst.Fields("HOWPAID") = "Cash" Then
                Ctotal = Ctotal + Val(rst.Fields("TOTALAMOUNT"))
                CCount = CCount + 1
            ElseIf rst.Fields("HOWPAID") = "Amex" Then
                Atotal = Atotal + Val(rst.Fields("TOTALAMOUNT"))
                ACount = ACount + 1
            End If
            rst.MoveNext
        Loop
        
EmployeeBalance2.Sections("Section4").Controls("label1").Caption = "Employee Balance Sheet for " & frmChooseEmp.EmpList.Text        
EmployeeBalance2.Sections("Section1").Controls("label10").Caption = Ctotal
EmployeeBalance2.Sections("Section1").Controls("label11").Caption = CHtotal
EmployeeBalance2.Sections("Section1").Controls("label12").Caption = Mtotal
EmployeeBalance2.Sections("Section1").Controls("label13").Caption = Vtotal
EmployeeBalance2.Sections("Section1").Controls("label14").Caption = Dtotal
EmployeeBalance2.Sections("Section1").Controls("label15").Caption = Atotal
RunningTotal = Ctotal + CHtotal + Mtotal + Vtotal + Dtotal + Atotal
EmployeeBalance2.Sections("Section1").Controls("label16").Caption = RunningTotal
            
EmployeeBalance2.Show
mdmonir (Programmer)
15 Nov 02 11:07
Hi ,
 
 I just downloaded visual studio service pack 5.Now my forms are working fine when I try to print them in landscape mode but my Data report which has grouping feature is not working.I use different ways such as

  Printer.Orientation = vbPRORLandscape
  DataEnvironment1.cmReports_Grouping DataCombo1
  DataReport1.PrintReport
This time I got a printout with portrait mode but when I use
 Printer.Orientation = vbPRORLandscape

      DataEnvironment1.cmReports_Grouping DataCombo1
      DataReport1.PrintForm
I did not even get a printout.Please send me some comments regarding this problem.Thanks in advance.
jrvbweenie (Programmer)
14 Jan 04 16:45
Can anyone tell me how to change the printer's paper source (bin) for printing a Data Report Item in VB?  Any suggestions would be helpful.  
jpinto (TechnicalUser)
18 Oct 04 14:27
Is there a way to make a report without the Data Environment and using DAO connection instead of ADO?

Thanks,

João Pinto
johnwm (Programmer)
19 Oct 04 3:41
Read FAQ222-2244 for guidance on forum usage - it's generally better to ask a new question in a new thread rather than tagging on the end of an old one.

For this question:
Yes - you can set the DataReport object Datasource property to any recordset directly in code

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

www.essexsteam.co.uk for steam enthusiasts

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close