Member Avatar for hallianonline

I am facing a problem that I have to show database results from 3 different tables on a single report these three tables have are used for different purposes and want to show their headings details separately. I am using VB6 and designing this report in DataReport a common feature for this.
Table1 is for Menu Details
Having following fields: Bill_ID, Menu Type, Cost
Table2 is for Services Details:
Having following fields: Bill_ID, Service Name, Cost
Table3 is for Extra Details:
Having following fields: Bill_ID, Description, Cost
Now structure I want to is like this using common Bill ID, and want to show the headings for each table and then details of each table one by one like this

Bill ID: 1234567890

Menu Details
+-----------+--------+
| Menu Type | Amount |
+-----------+--------+
| Prod1     |    100 |
| Prod2     |     60 |
| Prod3     |     75 |
+-----------+--------+

Service Details
+--------------+------+
| Service Name | Cost |
+--------------+------+
| Service1     |   15 |
| Service2     |   17 |
+--------------+------+

Extra Details
+-------------+------+
| Description | Cost |
+-------------+------+
| Extra1      |   11 |
| Extra2      |   12 |
+-------------+------+
Total Amount: $xxxx
Edited by hallianonline
  • 2 Contributors
  • 2 Replies
  • 288 Views
  • 19 Hours Discussion Span
  • Latest Post Latest Post by hallianonline

Dani AI

Generated

Short answer: the DataReport will only repeat rows when it is bound to a recordset and the controls in the Detail section are bound to fields. Manually setting a label in a loop (as in your attempt) just overwrites the same control and leaves you with the last value. is right to push the work back to the database/query layer — build a single ordered recordset and let the report render each row.

A clean pattern is to combine the three tables into one ordered result (either with a UNION query or by materializing a combined recordset) and include a Section/Sort column you can group on. Example SQL (conceptual — parameterize the BillID instead of concatenating):

SELECT 'Menu'   AS Section, MenuType  AS Item, Cost, 1 AS SortOrder FROM tblMenu   WHERE Bill_ID = ?
UNION ALL
SELECT 'Service' AS Section, ServiceName AS Item, Cost, 2 AS SortOrder FROM tblServices WHERE Bill_ID = ?
UNION ALL
SELECT 'Extra'  AS Section, Description AS Item, Cost, 3 AS SortOrder FROM tblExtras  WHERE Bill_ID = ?
ORDER BY SortOrder, Item;

If you need more control over ordering/headers you can build a client-side (disconnected) ADODB.Recordset, append explicit header rows and detail rows, then bind that single recordset to the DataReport. Example pattern:

Dim rst As New ADODB.Recordset
rst.Fields.Append "Section", adVarChar, 30
rst.Fields.Append "Item",    adVarChar, 255
rst.Fields.Append "Cost",    adCurrency
rst.CursorLocation = adUseClient
rst.Open

' add header row
rst.AddNew
rst!Section = "Menu Details"
rst!Item = Null
rst.Update

' append menu items...
' append service header + items...
' append extra header + items...

Set drBookBill.DataSource = rst
drBookBill.Show

Practical tips: use adUseClient / adOpenStatic if you rely on RecordCount; bind TextBox controls to the Item and Cost fields (don’t set captions in a loop); compute totals either with SQL SUM or in code and append a final row; avoid SQL concatenation (use parameters/ADODB.Command). If you prefer a GUI approach, Access reports support subreports (one recordsource per subreport) and can be automated from VB6 — often easier for complex grouped layouts.

Recommended Answers

All 2 Replies

Member Avatar for DenisOxon

I assume you can get the results you want in the database with a query and report ?

If so then access the query from VB6 and do what you will with the results.

Member Avatar for hallianonline

yes for one table my code is this but its printing the last record only. If i can get a working loop with it i'll be able to do the rest of the things
here is my code

Dim intCtrl As Integer
Dim Counter As Integer
If rs.State = 1 Then rs.Close
    sql = "SELECT * FROM tblHallBServices WHERE servb_bkid='" & LabBID.Caption & "'"
    rs.Open sql, cn
    Counter = rs.RecordCount
    Set drBookBill.DataSource = rs
    drBookBill.Sections("Section2").Controls.Item("Label14").Caption = Counter

'Check if the recordset contain records'
If Not rs.RecordCount = 0 Then
'Move to the first record'
   rs.MoveFirst
'Loop till the recordset return EOF(end of file)'
   While Not rs.EOF
            drBookBill.Sections("Section2").Controls.Item("Label15").Caption = rs!servb_sname
            drBookBill.Sections("Section2").Controls.Item("Label17").Caption = rs!servb_cost
      rs.MoveNext
   Wend
End If
    drBookBill.Show
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.