Can you put a Report into a form

[maxxev]

Hi.

I have some information that does not look very pleasnt displayed via forms & subforms.

Is there anyway to get a report into a form?

Or any other better way to get information to display in a sort of tree diagram

(basicall this is for an ingredient database that has ingredients logged with thier component ingredients and then any sub component ingredients:

Example:

Chocolate sauce (and any information)
Contains:
Sugar
Cream
Chocolate
Contains:
Sugar
Cocoa Mass
Cocoa butter
etc.
Milk
Cocoa
etc.

Cheers

 

[PWise]

look @ subdatasheets

 

[Remou]

How about a treeview control?

http://support.microsoft.com/kb/209891/en-us

 

[maxxev]

The tree view looks excelent, however it is also beyond my understanding...

SELECT tblIng_Spec_Detail.Ing_specID, tblIng_Spec_Detail.[Supplier Ing Name], tblIng_Spec_Detail.[Supplier Ing Code],

tblComponent_Ings.Ing_Comp_Reg_ID, tblComponent_Ings.Ing_Sub_Component_ID, tblComponent_Ings.[%] AS [tblComponent_Ings_%],

tblSubComponents_in_Components.SubComp_in_ingID, tblSubComponents_in_Components.Ing_Sub_Component_ID, tblSubComponents_in_Components.[%] AS [tblSubComponents_in_Components_%]

FROM tblIng_Spec_Detail INNER JOIN (tblComponent_Ings LEFT JOIN tblSubComponents_in_Components ON tblComponent_Ings.Ing_Comp_Reg_ID = tblSubComponents_in_Components.Ing_Comp_Reg_ID) ON tblIng_Spec_Detail.Ing_specID = tblComponent_Ings.Ing_specID;

Above is the format of my query (with some added blank lines to help in reading).

Would anyone be so kind as to walk me through the Treeview a little?

Cheers

 

[maxxev]

I've done this but the tree is blank

Option Compare Database
Option Explicit


'=================Load Event for the Form=======================
'Initiates the routine to fill the TreeView control
'============================================================

Private Sub Form_Load()
Const strTableQueryName = "QTreeIngredients"
Dim db As DAO.Database, rst As DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, dbReadOnly)
AddBranch rst:=rst, strPointerField:="Ing_specID", strIDField:="tblComponent_Ings.Ing_Comp_Reg_ID", strTextField:="tblComponent_Ings.Ing_Sub_Component_ID"
End Sub

'================= AddBranch Sub Procedure ======================
'      Recursive Procedure to add branches to TreeView Control
'Requires:
'   ActiveX Control:  TreeView Control
'              Name:  xTree
'Parameters:
'               rst:  Self-referencing Recordset containing the data
'   strPointerField:  Name of field pointing to parent's primary key
'        strIDField:  Name of parent's primary key field
'      strTextField:  Name of field containing text to be displayed
'=============================================================
Sub AddBranch(rst As Recordset, strPointerField As String, _
              strIDField As String, strTextField As String, _
              Optional varReportToID As Variant)
   On Error GoTo errAddBranch
   Dim nodCurrent As Node, objTree As TreeView
   Dim strCriteria As String, strText As String, strKey As String
   Dim nodParent As Node, bk As String
   Set objTree = Me!xtree.Object
   If IsMissing(varReportToID) Then  ' Root Branch.
      strCriteria = strPointerField & " Is Null"
   Else  ' Search for records pointing to parent.
      strCriteria = BuildCriteria(strPointerField, _
           rst.Fields(strPointerField).Type, "=" & varReportToID)
      Set nodParent = objTree.Nodes("a" & varReportToID)
   End If

      ' Find the first emp to report to the boss node.
   rst.FindFirst strCriteria
   Do Until rst.NoMatch
         ' Create a string with LastName.
      strText = rst(strTextField)
      strKey = "a" & rst(strIDField)
      If Not IsMissing(varReportToID) Then  'add new node to the parent
         Set nodCurrent = objTree.Nodes.Add(nodParent, tvwChild, strKey, strText)
      Else    ' Add new node to the root.
         Set nodCurrent = objTree.Nodes.Add(, , strKey, strText)
      End If
         ' Save your place in the recordset so we can pass by ref for speed.
      bk = rst.Bookmark
         ' Add employees who report to this node.
      AddBranch rst, strPointerField, strIDField, strTextField, rst(strIDField)
      rst.Bookmark = bk     ' Return to last place and continue search.
      rst.FindNext strCriteria   ' Find next employee.
   Loop

exitAddBranch:
   Exit Sub

      '--------------------------Error Trapping --------------------------
errAddBranch:
   MsgBox "Can't add child:  " & Err.Description, vbCritical, "AddBranch Error:"
   Resume exitAddBranch
End Sub

also is it possible to do 2 layers in the tree diagram... ?