Present recurvive relationships in a report

[tec-goblin]

Hi!
I have an Order>Product>Part order in my database. I can group it properly
in access, but when it gets complicated is when i need to present the Parts
of a Part (it's recursive with a maximum of 3 levels, using a PartsInPart
table).
I can only present the ids of the Parts in a Part (from the PartsInPart
table), but I don't know how to present in the report the details of each one
of these parts too.
Any advice?

 

[krissco]

Hi!
I have an Order>Product>Part order in my database. I can group it properly
in access, but when it gets complicated is when i need to present the Parts
of a Part (it's recursive with a maximum of 3 levels, using a PartsInPart
table).
I can only present the ids of the Parts in a Part (from the PartsInPart
table), but I don't know how to present in the report the details of each one
of these parts too.
Any advice?

If I understand correctly, two of your tables look like this:

Part {PartID, PartName, . . .}
PartsInPart {MasterPartID, ChildPartID}

You should be able to alias the PartsInPart table several times, and
include all levels of "subparts" using outer joins in your
recordsource. You need to join Part with PartsInPart to get the part
information you are missing.

If your SQL is rusty, you can display the subpart ID on your report,
and use the DLOOKUP() function to pull part information from your Part
table.

-Kris

 

[tec-goblin]

Part {PartID, PartName, . . .}
PartsInPart {MasterPartID, ChildPartID} Yes.


You should be able to alias the PartsInPart table several times, and
include all levels of "subparts" using outer joins in your
recordsource.

This is probably a stupid question, but how do I access the recordsource of
a report? How can I do joins in a report? I know how to do it on a query, but
then Access won't see the hierarchy and won't present the report in a
hierarchical way.
Could I create a table joining Part and PartsInPart and use the hierarchies
and sorting window of the report to link all levels? Well, it's worth a try
anyway.

If your SQL is rusty, you can display the subpart ID on your report,
and use the DLOOKUP() function to pull part information from your Part
table.

Thanks Kris, my SQL might be occasionally rusty, but my VBA knowledge is
inexistent (the main application, ie not the one for the reports, presents
the parts in a treeview c# control).

 

[krissco]

This is probably a stupid question, but how do I access the recordsource of

a report?

Open the report in design mode. Click View -> Properties on the menu.

How can I do joins in a report? I know how to do it on a query, but
then Access won't see the hierarchy and won't present the report in a
hierarchical way.

You don't do joins in a report - only in a query.

Could I create a table joining Part and PartsInPart and use the hierarchies
and sorting window of the report to link all levels? Well, it's worth a try
anyway.

You probably won't want to do that. There is a 1-M relationship
between Part and PartsInPart. This is sufficient. Don't worry about
how complicated the recursive relationship looks.

Thanks Kris, my SQL might be occasionally rusty, but my VBA knowledge is
inexistent (the main application, ie not the one for the reports, presents
the parts in a treeview c# control).

You can use Dlookup() without having to delve into VBA.

Tell me if I understand this correctly. Do you currently have a report
that looks like this:

Order header information:
Product 1.
First Part in Product 1
Sub part of first part
Another sub part of the first part
A sub-sub part of the first part
Second part in product 1
Product 2.
. . .

For instance:

If Part contains:
PartID PartName
1 Car
2 Door
3 Handle
4 Glovebox
5 Truck
6 Screw
7 Glass
8 Seat
9 Cushion

And PartsInPart contains:
MasterPartID ChildPartID
1 2
1 8
2 3
2 7
3 6
1 4
5 4
5 2
5 8
8 9

If an order is placed for a product that contains a car, then your
report (currently) looks like:
Order header information:
Some product
1
2
3
6
7
4
8
9
. . .

And you want it to look like:
Order header information:
Some product
1 (Car)
2 (Door)
3 (Handle)
6 (Screw)
7 (Glass)
4 (Glovebox)
8 (Seat)
9 (Cushion)
. . .

Is the first example what you currently have and the second example
what you really want? If that is the case, add a text box to the right
of the part# and use the DLookup() funciton: =Dlookup("PartName",
"Part", "PartID = " & NameOfControlThatHasPartID )

If I have misunderstood, then perhaps we should look at that SQL
statement.

-Kris

 

[tec-goblin]

That was very helpful. Getting access to the record source actually helped me
do this in another way, which seemed simpler originally:
I added PartsInPart, Part_1, PartsInPart_1 and Parts_2 and I had now the
proper hierarchy:
Order
Product
Part
Part
Part

I added all necessary fields to the recordsource, changed the sorting and
grouping properties and I had it all appear nicely.
WITH one problem: if my original joins where inner joins I had problem
making the report appear for orders that don't go to the bottom of the
hierarchy (some orders don't even have products yet).
Then I changed the joins to l eft joins, but I had the problem of having
visible headers for inexistent parts in the above cases
(so
order 32
door product
door part
leaf 1
window
leaf 2
screen

will appear as:
order 32
door product
door part
leaf 1
window
leaf 2
[blank part]
screen
[blank part]
[blank part]
)

I thought of fixing that by making the part header invisible if the part id
was null, or smth like that. I then observed that the visible property
doesn't allow an expression, so I resorted to VBA.
I made some efforts like:

Private Sub GroupHeader2_Format(Cancel As Integer, Format Count As Integer)
If ([name] Is Null) Then
GroupHeader2.Visible = False
Else
GroupHeader2.Visible = True
End If
End Sub

which didn't work.
( [name] the control with the part's name. Other failed efforts included
[Part.name] (the field), name.Text, [name].Text, etc, either as Is Null or =
"")

I am a complete newbie in VBA. Is there a way to do this, or should I
backtrack to do the solution with the DLOOKUP you suggested? I am so close...
(and thanks for your patience)

 

[krissco]

That was very helpful. Getting access to the record source actually helped me
do this in another way, which seemed simpler originally:
I added PartsInPart, Part_1, PartsInPart_1 and Parts_2 and I had now the
proper hierarchy:
Order
Product
Part
Part
Part

I added all necessary fields to the recordsource, changed the sorting and
grouping properties and I had it all appear nicely.
WITH one problem: if my original joins where inner joins I had problem
making the report appear for orders that don't go to the bottom of the
hierarchy (some orders don't even have products yet).
Then I changed the joins to l eft joins, but I had the problem of having
visible headers for inexistent parts in the above cases
(so
order 32
door product
door part
leaf 1
window
leaf 2
screen

will appear as:
order 32
door product
door part
leaf 1
window
leaf 2
[blank part]
screen
[blank part]
[blank part]
)

I thought of fixing that by making the part header invisible if the part id
was null, or smth like that. I then observed that the visible property
doesn't allow an expression, so I resorted to VBA.
I made some efforts like:

Private Sub GroupHeader2_Format(Cancel As Integer, Format Count As Integer)
If ([name] Is Null) Then
GroupHeader2.Visible = False
Else
GroupHeader2.Visible = True
End If
End Sub

which didn't work.
( [name] the control with the part's name. Other failed efforts included
[Part.name] (the field), name.Text, [name].Text, etc, either as Is Null or =
"")

I am a complete newbie in VBA. Is there a way to do this, or should I
backtrack to do the solution with the DLOOKUP you suggested? I am so close...
(and thanks for your patience)

Select all your controls - go to properties - and set the "CanShrink"
property to Yes.
In addition to the controls, make sure you select each section header/
footer and set CanShrink to Yes. That should eliminate your blank
lines.

-Kris

 

[tec-goblin]

Unfortunately, I need labels and shapes in the report, which cannot shrink
. (I set everything else's can shrink to yes but that naturally did not
help).

 

[krissco]

Unfortunately, I need labels and shapes in the report, which cannot shrink
. (I set everything else's can shrink to yes but that naturally did not
help).

Ok. I'm not sure. Repost under a different title to address the blank
line problem.

-Kris

 

[tec-goblin]

Ok, thanks for pointing me to the right direction: I searched the forum for
'blank lines in reports' and found about the IsNull() function.
The solution was to use for each part section something similar to:

Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount as Integer)
[GroupHeader2].Visible = Not IsNull([Part_id])
End Sub

where [Part_id] is an invisible control in my form, but [Part_name] worked
fine too (thus the invisible control is not needed)