sOrd.Open "SHAPE {SELECT ord_no,cust_name FROM ord_mstr} AS Order APPEND ({SELECT ord_no, item_no FROM ord_details} AS
OrderDet RELATE ord_no TO ord_no)", cn, adOpenStatic
If rsOrd.RecordCount > 0 Then
rsOrd.MoveFirst
While Not rsOrd.EOF
'display field values from Master Table i.e., ord_mstr
MsgBox "Order No from Master = " & rsOrd.Fields("ord_no")
MsgBox "Customer Name from Master = " & rsOrd.Fields("cust_name")
'child records will be stored inside Master Recordset itself
'i.e., ord_details records will be stored inside ord_mstr recordset
'OrderDet Recordset object will be stored inside rsOrd Recordset
'we dont have to open a seperate recordset for fetching order_details table
Set rsOrdDet = rsOrd.Fields("OrderDet").Value
If rsOrdDet.RecordCount > 0 Then
rsOrdDet.MoveFirst
While Not rsOrdDet.EOF
MsgBox "Order No from Detail = " & rsOrdDet.Fields("ord_no")
MsgBox "Item No from Detail = " & rsOrdDet.Fields("item_no")
rsOrdDet.MoveNext
Wend
End If
rsOrd.MoveNext
Wend
Set cn = Nothing
Set rsOrd = Nothing
Set rsOrdDet = Nothing
End If
|