Visual Basic Classic
--
Questions
--
Followers
Top Experts
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?
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?
Zero AI Policy
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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.
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.
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?
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.
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.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
Where do I place the query? Â
Please be more specific
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.DataS
    DataReportYouCreated.Show vbModal
'comment
'DataReportYouCreated = the name of you datareport
'RS = the recordset containing your data from the database to place on the datareport
    RS.source = ...... 'your sql command
    RS.Open , , adOpenForwardOnly, adLockReadOnly
    Load DataReportYouCreated
    Set DataReportYouCreated.DataS
    DataReportYouCreated.Show vbModal
'comment
'DataReportYouCreated = the name of you datareport
'RS = the recordset containing your data from the database to place on the datareport
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.
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.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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
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
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.DataS
    DataReportYouCreated.Show vbModal
place these on a module?
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.DataS
    DataReportYouCreated.Show vbModal
place these on a module?
Still...where I place the values of the textbox (input data) of the UserForm???
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
ASKER CERTIFIED SOLUTION
membership
Log in or create a free account to see answer.
Signing up is free and takes 30 seconds. No credit card required.
I use SQL 2000.
Ok. Now we're moving...thanx!
Now at this line "DataReportYouCreated.Show
--------------------------
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
Ok. Now we're moving...thanx!
Now at this line "DataReportYouCreated.Show
--------------------------
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.
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.
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?
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?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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....
The FIelds included in the Detail Section in the DataReport is pulled straight from the DataEnvironment created.
Please advice....
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
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?
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?
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
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)?
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)?
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.
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.
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?
When you say "that's placing the rpttextboxes", I assume that's when I drag the field from DataEnvironment1?
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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???
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???
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
    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
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
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
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.
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.
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
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
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.
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.
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
  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
  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
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 Â
--------------------------
--------------------------
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.
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.
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
my mistake
unbound disappears  (and changes in the text you put in datafield) but it is still unbound untill the recordset is passed
unbound disappears  (and changes in the text you put in datafield) but it is still unbound untill the recordset is passed
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?
However, 4" of width is not what we want to print. Â How can I keep my 8" of report width?
Can you tell me how to make the TollBox of DataReport be visible? Â (right-clicking takes longer)
Thanx
Thanx
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
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.
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?
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
(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
EARN REWARDS FOR ASKING, ANSWERING, AND MORE.
Earn free swag for participating on the platform.
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
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.
I don't have the "DataReport" part, only General (see attach).
Can you help in the report width error?
EE.width.JPG
Can you help in the report width error?
EE.width.JPG
Get a FREE t-shirt when you ask your first question.
We believe in human intelligence. Our moderation policy strictly prohibits the use of LLM content in our Q&A threads.
Ok. I'll close thie question. Â My initial inquest has been furnished
Thanx
Thanx
Thanx.
I''ll post the question for the assistance for the wdhth in another question.
Again, thanx!
I''ll post the question for the assistance for the wdhth in another question.
Again, thanx!
Visual Basic Classic
--
Questions
--
Followers
Top Experts
Visual Basic is Microsoft’s event-driven programming language and integrated development environment (IDE) for its Component Object Model (COM) programming model. It is relatively easy to learn and use because of its graphical development features and BASIC heritage. It has been replaced with VB.NET, and is very similar to VBA (Visual Basic for Applications), the programming language for the Microsoft Office product line.