By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,986 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,986 IT Pros & Developers. It's quick & easy.

How I display all records using Dynamic queries on a report.

100+
P: 274
Hi Everyone,
I am struct with one report. I use a number of recordsets to meet requirements of my report. It works fine but the problem is:
It displays only first record; I need to have all records. I don't know how I can do that. Here is partial code of my DataReport in VB6

For Each sect In RptDailyClosingSummarytrial.Sections
Expand|Select|Wrap|Line Numbers
  1.  
  2.       Debug.Print "Section", sect.Name
  3.       For Each ctl In sect.Controls
  4.  
  5.          'for different status of emplyes
  6.              If ctl.Name = "dates" Then
  7.                 ctl.Caption = strdates
  8.              End If
  9.              If ctl.Name = "lblName" Then
  10.                 ctl.Caption = RS.Fields!company_name
  11.              End If
  12.              If ctl.Name = "lblAddress" Then
  13.                ctl.Caption = RS.Fields!address
  14.              End If
  15.              If ctl.Name = "lblCity" Then
  16.                 If IsNull(RS.Fields!city) Then
  17.                 ctl.Caption = ""
  18.                 Else
  19.                 ctl.Caption = RS.Fields!city
  20.                 End If
  21.              End If
  22.  
  23.              If ctl.Name = "lblPostalCode" Then
  24.                 If IsNull(RS.Fields!postal_code) Then
  25.                 ctl.Caption = ""
  26.                 Else
  27.                 ctl.Caption = RS.Fields!postal_code
  28.                 End If
  29.              End If
  30.  
  31.              If ctl.Name = "lblProvince" Then
  32.                  If IsNull(RS.Fields!province) Then
  33.                  ctl.Caption = ""
  34.                  Else
  35.                  ctl.Caption = RS.Fields!province
  36.                  End If
  37.              End If
  38.              If ctl.Name = "lblPhoneNo" Then
  39.                 If IsNull(RS.Fields!phone_number) Then
  40.                 ctl.Caption = ""
  41.                 Else
  42.                 ctl.Caption = RS.Fields!phone_number
  43.                 End If
  44.              End If
  45.     '''''''''''''''''Record count''''''''''''''
  46.              If ctl.Name = "counts" Then
  47.                 If IsNull(PS.Fields!Con) Then
  48.                 ctl.Caption = "0.0"
  49.                 Else
  50.                 ctl.Caption = PS.Fields!Con
  51.                 End If
  52.              End If
  53.  
  54.          ''''''''''txtgroup_id'''''''''''''''
  55.       If ctl.Name = "txtGroup_ID" Then
  56.  
  57.                 ctl.Caption = oRS.Fields!group_id
  58.      End If
  59.      ''''''''''''''''''txtname'''''''''
  60.      If ctl.Name = "txtName" Then
  61.  
  62.                 ctl.Caption = oRS.Fields!Name
  63.      End If
  64.          ''''''''''''''''''txtPrice1''''''''
  65.      If ctl.Name = "txtPrice1" Then
  66.  
  67.                 ctl.Caption = oRS.Fields!price1
  68.      End If
  69.  
  70.           ''''''''''''''''''txtgst1''''''''
  71.      If ctl.Name = "txtgst1" Then
  72.  
  73.                 ctl.Caption = NS.Fields!gst1
  74.      End If
  75.  
  76.  
  77.  
  78.  
  79.      ''''''''''subtotal'''''''''''''''
  80.      If ctl.Name = "subtotal" Then
  81.                 If IsNull(PS.Fields!sum1) Then
  82.                 ctl.Caption = "0.00"
  83.                 Else
  84.                 ctl.Caption = Format(PS.Fields!sum1, "########0.00")
  85.                 End If
  86.      End If
  87.      '''''''''''''''gst'''''''''''''''
  88.      If ctl.Name = "gst" Then
  89.                 If IsNull(PS.Fields!sum2) Then
  90.                 ctl.Caption = "0.00"
  91.                 Else
  92.                 ctl.Caption = Format(PS.Fields!sum2, "########0.00")
  93.                 End If
  94.      End If
  95.      '''''''''''''''''grand total''''''
  96.      If ctl.Name = "grandtotal" Then
  97.                 If IsNull(PS.Fields!sum3) Then
  98.                 ctl.Caption = "0.00"
  99.                 Else
  100.                 ctl.Caption = Format(PS.Fields!sum3, "########0.00")
  101.                 End If
  102.      End If
  103.      '''''''''''''''Average'''''''''''''
  104.      If ctl.Name = "average" Then
  105.                 If IsNull(PS.Fields!net) Then
  106.                 ctl.Caption = "0.00"
  107.                 Else
  108.                 ctl.Caption = Format(PS.Fields!net, "########0.00")
  109.                 End If
  110.      End If
  111.      ''''''''''''''''''''''''''''''''''''
  112.      '''''''''''''''Cash Transections'''''''''''''
  113.      If ctl.Name = "lblCashTran" Then
  114.                 If IsNull(GS.Fields!sum3) Then
  115.                 ctl.Caption = "0.00"
  116.                 Else
  117.                 ctl.Caption = Format(GS.Fields!sum3, "########0.00")
  118.                 End If
  119.      End If
  120.      ''''''''''''''''''''''''''''''''''''
  121.       '''''''''''''''Account Transections'''''''''''''
  122.      If ctl.Name = "lblChqOut" Then
  123.                 If (MS.RecordCount = 0) Then
  124.                 ctl.Caption = "0.00"
  125.                 Else
  126.  
  127.                 ctl.Caption = Format(MS.Fields!sum3, "########0.00")
  128.                 End If
  129.      End If
  130.      ''''''''''''''''''''''''''''''''''''
  131.    ' oRS.MoveNext
  132.     Next ctl
  133.  
  134.  Next sect
any help will be highly appreciated.
thanks in advance
Regards
Dec 21 '07 #1
Share this Question
Share on Google+
2 Replies


100+
P: 274
Hi Everyone,
I really need help for this report that Posted yesterday got no reply from anyone. I think I won't be able to do it on my own I have been trying this since last week.
I think the way I ask is too complex that no one understands what I want.
Here is another attempt to make my question simple

In datareport details section, I have following fields to display
Group Code Group Name Amount GST Subtotal

amoung these five fields GST is computed different query( as done in recordset ORS ) and rest of the fields are taken from another(As done is recordset NS);
chances are there there is no gst for group codes but there can be some value for Amount.

there are only four groups (Metals, Papers, UBC, MISC)
Now the problem is:
If I set datasource to ORS. Everything is ok except GST. GST calculated for first group(Metals) is repeated for all groups.

I set datasource to something else other than ORS, and NS. It prints information for group only. I tried do while, for, move next first, last and everything else I knew but no luck.

I badly need your help. So please gimme any tip you guys know. I don't want any source code only an Expert tip. Have someone ever worked on a report like this one?
Dec 22 '07 #2

100+
P: 274
Hi Everyone,
I think nobody ever get what my question was.....never mind

I solved the mystery after a long struggle. If anyone would be intereted in knowing how I did it. I will be pleased to reply. I can't tell how many different method I tried to get the solution.
Regards
Dec 24 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.