Start Free Trial

asked on

How to setup user input in VB6 DataReport

We're starting out in VB6 DataReport. We have made a couple of simple reports.

We would like to have the report ask the user a date range and print the report based on that info.

How can we go about it?

This is quiet simple
Adjust you sql command with a where-clausule containing an between statement.

Something like this
select * from tableX where date between '2009-06-01' and '2009-06-30'.
Post your sql statement if you want us to adjust it to your needs. And also the type of data your database has been configured. Like Gregorian Calendar or something else.

User input could be achieved by using a datetimepicker, if that is you question.
Put a form between the asking for a print and the actual printing containing two datatimepicker or an other form of asking the two dates. Keep the two dates in a variable to place into the sql command.

ASKER

I adjusted the sql command with a where clause. (I right-click on command, chose 'SQL statement", and place the SQL query. But doesn't the query supposed to be passed thr values from the form?

I made a form with one text box text1.text

How do I do the SQL query.

I want to search for everything within column ItemDecription for whaterver the user enters in Text1.text.

So my query is:

select * from Items where ItemDescription like '% ????? %'

What do I put there?

If ItemDescription is containing a date the you have to post as where clausule
where ItemDescription between '2009-06-01' and '2006-07-01' to select a single month.

So normally you have to ask the using for two date is you are not willing to use for a single date.
Single date can be posted as
between '2009-06-01 00:00:00.000' and '2009-06-01 02:00:00.000' for asking all records in this 2 hours.

ASKER

Where do I place the query?

Please be more specific

Put here your query retrieving data from database, then to put this data in your datareport:
RS.source = ...... 'your sql command
RS.Open , , adOpenForwardOnly, adLockReadOnly

Load DataReportYouCreated
Set DataReportYouCreated.DataSource = RS
DataReportYouCreated.Show vbModal

'comment
'DataReportYouCreated = the name of you datareport
'RS = the recordset containing your data from the database to place on the datareport

ASKER

The instructions provided, where do I place them?

I have the application open right now. I have a Form, a DataReport and a Data Environment.

Where specifically do I place your lines?

Thanx for the patience.

these instruction can be placed in a module. from the form/module you make a call (.show) to open the datareport.
In the datareport you don't have to add any code. Fill in de datafield propertie of the textlabels in the datareport with the corresponding database fieldname
ex.
select X,Y from table x
X and Y should be filled in, in the two rpttextboxes on you datareport

ASKER

Sorry 3_S, I am lost.

When you say "these instruction can be placed in a module", you mean place the instructions you gave on 6/26/2009 on a Module?

That is:

RS.source = ...... 'your sql command
RS.Open , , adOpenForwardOnly, adLockReadOnly

Load DataReportYouCreated
Set DataReportYouCreated.DataSource = RS
DataReportYouCreated.Show vbModal

place these on a module?

ASKER

Still...where I place the values of the textbox (input data) of the UserForm???

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

I use SQL 2000.

Ok. Now we're moving...thanx!

Now at this line "DataReportYouCreated.Show vbModal", an error appear:

---------------------------
Error
---------------------------
DataField 'Command1.VendorID' not found
---------------------------
OK
---------------------------

I notice on my Detail section of DataReport, the fields when expanding, it has "[Command]" in their name

In sql 2000 you should be able to pass you date as following
between 'yyyy-mm-dd' and 'yyyy-mm-dd' Single quotes must be placed.

Do you have a field name VendorID in your sql statement and do you have a rpttextbox on the datareport with property datafield set to VendorID?

the fields on your datareport where you expect data from the DB must be of the type rptTextBox, else you can't set the datafield property (in a label this is not available) the name of the rptTextBox is of no importance, you can choose a meaningfull name to make your work easier, but this is not needed.

ASKER

Understood. The field name of VendorId from the From is VendorId.text.

You mention "rpttextbox on the datareport with property datafield set to VendorID". Does that mean I have to use the TExtBox from the ToolBar from DtatReport and not generel?

I have tried to display the ToolBox for DataRepor and can't seem to make it appear.

Or how can I determinei if the VendorId.text is from TextBox of DataReport?

ASKER

Re-reading your reply, you indicate "the fields on your datareport where you expect data from the DB must be of the type rptTextBox,"

The FIelds included in the Detail Section in the DataReport is pulled straight from the DataEnvironment created.

Please advice....

ASKER

I just deleted all field in Detail Section and just left the Header and the report prints (obviously with no data, just header).

I goto DataEnvironment, drag a field and run it. Same "Command1" error

Are you sure there are no errors in you sql statement, have you tried it interactively in query analyzer?

In one of my previous posts I used command1 as name of a button on a form? Did you changed this to the button you are using?

A dataenviroment is not needed on the datareport. all information from the database is in your recordset. the recordset is the only thing you need to pass to the datareport.

Only on thing must be done in the datareport and that's placing the rpttextboxes and fill in the datafield property to make this work. (of course labels/lines/pictures) can be added too, to make the report complete)

Everything I explained so far was with the understanding that you used a dns-less connection.
How do you get the information in your recordset, also with a dataenvironment (a second one then)?

ASKER

Yes I has debug the application and F8 all until the SQL statement. I copied it to my Sql Analyzer and script ok.

I have 2 script:

For reading all records pertaining the VEndor:
select VendorID, VendorReceiptNo, DifPrecio as Cost1, DiscOtrasNegoc as Cost2
from ces00rp01 WHERE vendorid='AA01'

The other to total the VEndors:
select sum(DifPrecio) as Cost1, sum(DiscOtrasNegoc) as Cost2
from ces00rp01 WHERE vendorid='AA01'

Tested ok.

ASKER

Understood and greatly appreciated your time and patience.

When you say "that's placing the rpttextboxes", I assume that's when I drag the field from DataEnvironment1?

ASKER

Wait!

You mean I don't have to creat a dataenviroment or commands?

If so, how do I pass the data from the record set to the DataReport???

ASKER

Ok. I deleted entirely the dataenviroment.

Load DataReport1
Set DataReport1.DataSource = rstRecord
DataReport1.Show vbModal

I haven't include the fields in Detail Section I can;t seem to find how to do it.

However, noe when running I get this error (seems we're getting there):

---------------------------
Error
---------------------------
DataField '(Empty)' not found
---------------------------
OK
---------------------------

So I gues what need to make this complete is hot to pass to Detail Section...

Pleae Advice

No not from the dataenvironment but from the toolbox, normally located on your left of your screen.
there you should have a group Generel (here you can find button, textbox... the normal things you can add to a form)
The moment you add a datareport to you project a group datareport should appear on this toolbox. There you can select rpttextbox and place this on the report
If toolbox is not visible this can be done in the visual basic menu View \ toolbox

can you post how you make your connection to the database

If so, how do I pass the data from the record set to the DataReport???
to pass you recordset to the report just add
place this under a button or some place else from where you want to start the report
Load DataReport
Set DataReport.DataSource = RS
DataReport.Show vbModal

DataReport => this should be the name of your datareport
RS => the recordset containing the records needed for the report, this is your sql statement posted previously by you.

ASKER

The TollBox for DataReport doesn''t appear. How can I get it back? (I click View \ toolbox, and nothing, just the regular TollBox greyed out)

My connection to the database:
Set cnn = Nothing
Set rstRecord = Nothing
Set cnn = New ADODB.Connection
Set rstRecord = New ADODB.Recordset
Dim lCompanyDB, SqlServerName As String
SqlServerName = "(local)"
lCompanyDB = "TWO"
cnn.Open "Provider=SQLOLEDB; " & _
"Initial Catalog=" & lCompanyDB & "; " & _
"Data Source=" & SqlServerName & "; " & _
"integrated security=SSPI; persist security info=True;"

I did place the 3 lines at the Button where User select to start print. Here are the lines, but accommodated to my project:

Load DataReport1
Set DataReport1.DataSource = rstRecord.DataSource
DataReport1.Show vbModal
rstRecord.Close

ASKER

Nevertheless, I right-click and select Insert Control and can insert a TextBox; is that ok? or do I have to get from the ToolBox of DataReport?

I included the TextBox and it displays a name as "Unbound" and the Name at Properties is Text1.

When clicking DataField or in properties its empty or nothing is display.

Please advice.

Load DataReport1
Set DataReport1.DataSource = rstRecord ' this line is change, .datasource must be deleted
DataReport1.Show vbModal
rstRecord.Close

the icons on datareport are greyed out when you are not working in a datareport, so select a datareport from the project explorer, then you should be able to select rpttextbox

ASKER

Ok. I type directly on entered in the DataField the VendorID from my "Seclct and now I get an erroe:

---------------------------
Error
---------------------------
Report width is larger than the paper width
---------------------------
OK
---------------------------

Yes, you could also insert in that way.
Unbound is correct, nothing is display in datafield is ok. You must fill this in yourself with the fieldname you want this unbound control to connect with when a recordset is passed through
unbound will still be display, it will get bound at runtime of your program when a recordset is passed.

my mistake
unbound disappears (and changes in the text you put in datafield) but it is still unbound untill the recordset is passed

ASKER

Ok Just shrink the page to 4 inches and it worked. I can see the values of the field.

However, 4" of width is not what we want to print. How can I keep my 8" of report width?

ASKER

Can you tell me how to make the TollBox of DataReport be visible? (right-clicking takes longer)

Thanx

Now I'm not quite following you.
You can set the width at design in the report. You have a ruler above you report and this way you can set the width to 8".
Do you mean you get an error that the report will not fit on the page, look at your default printer if 8" width is supported.

If you right click you project and add a new datareport is it then grayed out in the new report?

ASKER

yes

(i found a quicket way, copy & paste)

But it would be good to know how to make the display visible and correct the report width

Are these the buttons that are grayed out in you datareport toolbox?
Sometimes when you open genereal the datareport group goes down and stand in the left lower corner of your screen.

datareport.JPG

If these are greyed out and you have a datareport openend like in the above picture then i have no idea why this is.

ASKER

I don't have the "DataReport" part, only General (see attach).

Can you help in the report width error?
EE.width.JPG

ASKER

Ok. I'll close thie question. My initial inquest has been furnished

Thanx

ASKER

Thanx.

I''ll post the question for the assistance for the wdhth in another question.

Again, thanx!