Data Report 3

[MuraliKrishnaK]

Hai,

Is it possible to send a parameter to the datareport's query?

The situation is..
1. MS Access Database of addresses
2. Query to retrieve address
3. Parameter criteria for the query(the city part when the user wants to print the address of a particular city)

Now how to solve this situation? How to pass parameters to the reports?

Murali Krishna

 

[boatBuny]

The following works wonderfully passing param's when NOT using the grouping feature.

With DataEnvironment1
If .rscmdNA_Grouping.State = adStateOpen Then
.rscmdNA_Grouping.Close
End If
strSQL = "SELECT PART.PART_NO, PART.REV, PART.TITLE, PART.PREP_BY, PART.PREP_DATE, PART.ASSOC_CNTR, PART.PROJECT, PART.SYSTEM, PART.SUBSYSTEM, NA.NA, NA.NA_SEQ, NA.DASH FROM NA, PART WHERE NA.PART_NO = PART.PART_NO AND (PART.ARCHIVE_DATA = '0') AND (PART.MARSHALL_DATA = '0') AND (PART.VENDOR = '0')AND (PART.PART_NO BETWEEN '" & frmSelect.Text1.Text & "' AND '" & frmSelect.Text2.Text & "')"
End With
With cmdNA
DataEnvironment1.Commands("cmdNA_Grouping&quot.CommandText = strSQL
End With

However, as soon as I add grouping to my command object, I receive an error message stating that the -for each txt??? on the detail section- cannot be found.

?
bB

 

[MangroBongacello]

boatBuny,

when you add grouping to your command object, check the Hierarchy info of the command (context menu). It looks something like this:

SHAPE {SELECT * FROM `Table`} AS Command1 COMPUTE Command1 BY 'FieldToGroupBy'

You have to set the DataMember property for each text box in the detail section to Command1. Then it should work.

Hope this helps
Mangro

 

[boatBuny]

Mangro:

SHAPE {SELECT PART.PART_NO, PART.REV, PART.TITLE, PART.PREP_BY, PART.PREP_DATE, PART.ASSOC_CNTR, PART.PROJECT, PART.SYSTEM, PART.SUBSYSTEM, NA.NA, NA.NA_SEQ, NA.DASH FROM NA, PART WHERE NA.PART_NO = PART.PART_NO AND (PART.ARCHIVE_DATA = '0') AND (PART.MARSHALL_DATA = '0') AND (PART.VENDOR = '0') AND (PART.PART_NO BETWEEN ? AND ?)} AS cmdNA COMPUTE cmdNA BY 'PART_NO','REV','TITLE','PREP_BY','PREP_DATE','ASSOC_CNTR','PROJECT','SYSTEM','SUBSYSTEM'

Been there done that:
Get error - command cmdNA.NA not found.

I'm stumped and tired!

bB

 

[geeker21]

Mangro

I have done the same thing, please see below:

.CommandText = "Shape {SELECT StaffInfo.ID, StaffInfo.name, StaffInfo.team, Details.area, Details.billtrak, Details.disputeref, Details.enduser, Details.custref, Details.acct, Details.servnum, Details.product, Details.billsys, Details.adjdate, Details.auth, Details.moa, Details.typadj, Details.attributed, Details.reason, Details.comment, TBL_Details.tref, TBL_Details.amt_gst_inc, TBL_Details.amt_gst_excl FROM StaffInfo INNER JOIN (TBL_Details INNER JOIN Details ON TBL_Details.billtrak = Details.billtrak) ON StaffInfo.ID = Details.ID WHERE (((Details.billtrak)= ?))} as Command1 Compute Command1 by 'ID','billtrak'"


but it gives me the same error as before. I also tried using the data environment with groupings, but I seem to can't pass the parameter in the query. When I put a default value, it gives me an error saying Relate, Compute By, or Sort cannot be done on columns who's key length is unknown or exceeds 10KB. Any ideas???? but I'm a bit more concerned on how to pass the value in the code????

 

[MangroBongacello]

bB: cmdNA.NA???
geeker21: I always use ado recordset as data source for data raport, never using DE. I think it's more flexible. I don't know if you've already tried something like this:

Sub ShowMyReport(varBilltrak as Variant)
dim strSQL as string
dim rs as adodb.recordset
dim cnn as adodb.connection

cnn.Open "Provider=MSDATASHAPE; Data Provider=Microsoft.JET.OLEDB.4.0; Data Source=d:\BILLADJ\BillingDB.mdb;"

strSQL="Shape {SELECT StaffInfo.ID, StaffInfo.name, StaffInfo.team, Details.area, Details.billtrak, Details.disputeref, Details.enduser, Details.custref, Details.acct, Details.servnum, Details.product, Details.billsys, Details.adjdate, Details.auth, Details.moa, Details.typadj, Details.attributed, Details.reason, Details.comment, TBL_Details.tref, TBL_Details.amt_gst_inc, TBL_Details.amt_gst_excl FROM StaffInfo INNER JOIN (TBL_Details INNER JOIN Details ON TBL_Details.billtrak = Details.billtrak) ON StaffInfo.ID = Details.ID WHERE (((Details.billtrak)= " & varBilltrak & "))} as Command1 Compute Command1 by 'ID','billtrak'"

set rs=cnn.execute(strSQL)

Set Report.DataSource = rs
Report.Show
End Sub

First you set the DataMember property for each textbox in a detail section to "Command1" then run the sub. If billtrak is of text data type, don't forget to add single quotes into the sql statement.

I hope this will help you. Good luck.
Mangro

 

[boatBuny]

Mango,

The command name is cmdNA with grouping named cmdNA_Grouping.

I'm putting cmdNA_Grouping as the DataMember on the grouping section txt properties, and the cmdNA in the details section txt properties. I still receive the same error.

I have attempted removing all the controls in the Detail section of the report and then receive error, sections do not match. I'll look closer at the code you provided above and see if I cannot find a work-around but you will have to believe me when I say, "been there done that, tried it didn't work"!

Thanks for the attempt!

geeker....Let me know if you discover the answer and I will do the same!

bB

 

[geeker21]

Mangro,
thanks for the tips, I'll probably try your code in my next program. I have mine working, I found out that one of my field is 'causing the error message. I have my comment field with a data type of 'Memo', I changed it to 'Text' and my report is now working properly.

bB:
I'm not sure if your still using DE to create your report, Mangro's suggestion above is pretty useful and will give you more flexibility. but in case you still do, I have posted the steps I did to make it work using DE.

In the DE command properties, put your query in the SQL box. (refer to my query above w/o the 'Shape command'). in the parameters tab, change the neccessary details for your parameter. then in the grouping tab select all fields that you want to group. after doing all of the above you should have the ff:

DE1:
Command1:
SQL: Command1 grouped using Command1_Grouping
Summary fields in Command1_Grouping
....... List of all group fields
Details fields in Command1
....... List of all your fields

Create a Data Report:
Data Source : DE1
Data Member : Command1_Grouping

Right mouse click in the report and select "Retrieve Structure" Drag all the Group fields in the Header section of the report and the Detail Fields in the detail.

In the form, put the following code:

Private Sub cmd_print_Click()
Dim t As String

t = InputBox("Enter Billtrak Number to Print&quot
Load DataEnvironment1
With DataEnvironment1
If .rsCommand1_Grouping.State <> 0 Then .rsCommand1_Grouping.Close
.Command1_Grouping CStr(t)
End With
DataReport1.Refresh
If DataReport1.Visible = False Then DataReport1.Show

End Sub Kind Regards
geeker21

 

[boatBuny]

Everyone:

I have found the answers to my delima:

Three additional items had to be added to the code in the DataReport_Initialize area get these reports to work (and show) with grouping and param passing.

1st, I had to add the actual code for the param ? in the Shape statement
2nd, I had to add a .refresh
3rd, I had to add a .visible for each section on the report

Here it is and Geeker, great luck to you, I made it just 6 business days before my project was due for UAT =).

Private Sub DataReport_Initialize()
Dim SQL As String
Dim cmdNA As Object
With DataEnvironment1
If .rscmdNA_Grouping.State = adStateOpen Then
.rscmdNA_Grouping.Close
End If
SQL = &quot;SHAPE {SELECT PART.PART_NO, PART.REV, PART.TITLE, PART.PREP_BY, PART.PREP_DATE, PART.ASSOC_CNTR, PART.PROJECT, PART.SYSTEM, PART.SUBSYSTEM, NA.NA, NA.NA_SEQ, NA.DASH FROM NA, PART WHERE NA.PART_NO = PART.PART_NO AND (PART.ARCHIVE_DATA = '0') AND (PART.MARSHALL_DATA = '0') AND (PART.VENDOR = '0') AND (PART.PART_NO BETWEEN '&quot; & frmSelect.Text1.Text & &quot;' AND '&quot; & frmSelect.Text2.Text & &quot;')} AS cmdNA COMPUTE cmdNA BY 'PART_NO','REV','TITLE','PREP_BY','PREP_DATE','ASSOC_CNTR','PROJECT','SYSTEM','SUBSYSTEM'&quot;
End With

With cmdNA
DataEnvironment1.Commands(&quot;cmdNA_Grouping&quot.CommandText = SQL
NAIreport.Refresh

End With
With NAIreport
.Sections(&quot;PageHeader&quot.Visible = True
.Sections(&quot;cmdNA_Grouping_Header&quot.Visible = True
.Sections(&quot;cmdNA_Detail&quot.Visible = True
.Sections(&quot;PageFooter&quot.Visible = True

End With

Thanks to all that have offered assistance!

bB