[RESOLVED] Help in Data Report

[Encoder]

Is it possible that I can create a transaction receipt just by using data report? From the example that I've seen it seems that the report displays a list. I just need one row/record from the database (I'm using MS Access and SQL) containing the Payment No, Payment and payment type to create the receipt. Is it also possible that one report can use two different sets of database table? For example one textfield has a different datasource from another textfield.

[Hack]

To you, it might be a "transaction receipt"

To the software itself, it is nothing more than another report, so once you get the formatting correct, there is no reason it can't be used for your purpose.

[Encoder]

Yup that's what I'm asking if ever I could just select one record from the database to make it look like a receipt.

[dee-u]

Yes, you can used different columns from different tables if that is what you want, you just have to INNER JOIN them.

[dee-u]

This is an MSDN article on using DataReport, perhaps it should help.

[wes4dbt]

When you say "two different data sources", do you mean two different databases or two different tables in the same database? Big difference.

[Encoder]

When you say "two different data sources", do you mean two different databases or two different tables in the same database? Big difference.

Two tables in one database.

This is an MSDN article on using DataReport, perhaps it should help.

Thanks for that, but what command am I supposed to use select one record from the database? I've used the code below in the command text but it displays nothing.

SELECT * FROM payment WHERE pono = 'Form1.Text1.Text'

[wes4dbt]

If your using an ADO recordset for the reports datasource then use something like this

select * from table1 inner join table2 on table1.paymentId=table2.paymentId where table1.paymentId='" & Text1.Text & "'"

If your using a DataEnvironment for the datasource then check out this post
http://www.vbforums.com/showthread.php?t=552148

[Encoder]

Where should I put those codes in the project?

[Encoder]

^ What I mean is the code you mentioned on the other thread you gave me. Sorry for the misleading post, my head hurts.

deCropbyuser.Commands("Command1_grouping").Parameters("param1").Value = frmUrange!txtStart
deCropbyuser.Commands("Command1_grouping").Parameters("param2").Value = frmUrange!txtEnd
        rptCropbyuser.Show vbModal

[wes4dbt]

If your using a Command Button to print the report then put it in the "click" event. It can be just about anywhere in your code. The DataEnvironment and DataReport are Public objects.

[Encoder]

^Okay I get it thanks. But what part in the added commands is this particular code (the ones in bold and underlined)?

deCropbyuser.Commands("Command1_grouping").Parameters("param1").Value = frmUrange!txtStart
deCropbyuser.Commands("Command1_grouping").Parameters("param2").Value = frmUrange!txtEnd
        rptCropbyuser.Show vbModal

[wes4dbt]

If you look at the DataEnvironment attachment in the link with that code you will see "Command1 grouped using Command1_Grouping". Go to the properties, you'll see the SQL has 2 "?". Select the Parameters tab. The first "?" is "param1" and the second is "param2"

[Encoder]

Is param1 and param2 constant?

[wes4dbt]

This code is setting the value of param1 and param2, it is located in the "click" event of a Command Button

deCropbyuser.Commands("Command1_grouping").Parameters("param1").Value = frmUrange!txtStart
deCropbyuser.Commands("Command1_grouping").Parameters("param2").Value = frmUrange!txtEnd
 rptCropbyuser.Show vbModal

You can name the parameters anything you want when you design the dataenvironment.

[Encoder]

If you look at the DataEnvironment attachment in the link with that code you will see "Command1 grouped using Command1_Grouping". Go to the properties, you'll see the SQL has 2 "?". Select the Parameters tab. The first "?" is "param1" and the second is "param2"

I missed that one, I didn't look through the Parameter tab until now. Anyway thanks for the help.

[Encoder]

Alright I have another problem, I've used the code and it works but only in the first time. In the commands in the data environment I've inputted "SELECT * FROM payment WHERE ID = ?", named ? as param1. Then on the click eventI used this code

DataEnvironment1.Commands("payment").Parameters("param1").Value = frmPayment!txtPayNo                
DataReport1.Show vbModal

The datareport displays what I intended the first time (for example If the textbox has "1" in it displays the columns with ID "1" from the payment table, but when I click again and this time the textbox displays "2", the report still displays the columns with ID "1".

[wes4dbt]

You have to close the recordset or requery,

If deCropbyuser.rsCommand1_Grouping.State = adStateOpen Then
      deCropbyuser.rsCommand1_Grouping.Close
End If

Put this just before you set the param1 and run the report, I guess you could close the recordset right after you run the report.

[Encoder]

^ It works, however there's still a problem, cause my VB seems to be working strangely. Whenever I put a breakpoint right before the DataReport shows, the info I need displays on the DataReport. But if I clear the breakpoint (there's no breakpoint at all), the DataReport just displays blank. What seems to be the problem?

[wes4dbt]

I'd guess you are putting the data in a table just before you run the report. ACCESS has a delay before it writes the data. I found that closing the recordset and connection before running the report helps.