How to Create an indented Bill report using Access Table

This question stems from the below previously closed question Url:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24592086.html
.
I have extracted data from our MAPICS Parent/Child Prodstruc and with the help of JimD, who I want to say has truly earned the title of "Genius", have it structured and sequenced ready for a report.
.  
I have been trying to no avail to get this structure in the report correct as I would like to see it in the example below:
.
 EndItem Part
       Child Part
       Child part
              Child of Child Part
                       Child of child of child part
       Child part

.....and so on?  The EndItem  Part could be in the header as there is no need to be in the tree I guess.

Any chance this is less complicated than my feeble brain is making it out to be?

My table is as follows:

A scaled down version for simplicity of my data is as follows:

tblBOM:

Level....... SequenceNumber.............AssemblyID(Parent PN) ................PartID(ChildPn)
1........................1...........................................DM1048......................... ........124003
1........................2...........................................DM1048..................................200041
1........................3...........................................DM1048..................................230123
1........................4...........................................DM1048..................................240570
2........................1........................................ ...230123..................................060106
2........................2............................................230123..................................060107
2........................1............................................450163................................. 240597
More Data but will stop here.
I could have as much as 6 levels in my structure

Any help would be greatly appreciated.
Thanks,
RandyF
 
BlackbeltrrfAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 Here, see if this helps.

JimD.
IndentedBOM.zip
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
test
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, were back and now I'll retype the answer<g>

First, create a text control for each of the fields you need on the report and bind them to the fields.  Now hide all these controls.

Now add one un-bound control called txtLine.

In the detail's OnFormat event, do:

  Me.txtLine = Space(Me.txtLevel * 4) & Me.txtSequenceNumber & "   "  & Me.txtComponetNumber .... etc

  I would add a group on the parent assembly number and use the group header to print that so you don't have to print it on each line.

  You can mess with the Space() forumal to get the indenting you want.

  Any questions holler.

JimD.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Randy,

  BTW, you can mix the two approaches and keep a variable portion for the level and a fixed portion which lists the part, desc, QPA, etc.

  Do that by shortening txtLine, then un-hiding whatever fields you need to the right.

  Only txtLine will be variable with the Space() function and the other controls will be in a fixed place.

  I actually like this format the best (easy to read).

JimD.
0
 
Jeffrey CoachmanMIS LiasonCommented:
EndItem Part
       Child Part
       Child part
              Child of Child Part
                       Child of child of child part
       Child part

If I am reading this correctly, the Report wizard will create that "layout" for you.

Make sure you select each Grouping Level in order.

Then select either the "Stepped" or "Outline" layout

JeffCoachman
0
 
BlackbeltrrfAuthor Commented:
Jeff,

I tried what you suggested but it will only allow grouping down 4 levels and I need to go as much as 6 deep.

Jim,
I am getting ready to try your suggestion today.

Thank you both very much for the help and support in this.

Randy
0
 
BlackbeltrrfAuthor Commented:
Jim,

I tried what you suggested but for some reason I am getting the error "Cannot find the object 'Me' when I do a print preview and a blank form when I try to view "Report View' .  What am I doing wrong?

I added All you said above and put the code in the "event" "On Format" in the details section.

Thanks,
Randy
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Try print preview.  Report View is worthless IMHO as it will not execute VBA code.

JimD.
0
 
BlackbeltrrfAuthor Commented:
That is when I get the error while doing Print Preview

Randy
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I tried what you suggested but it will only allow grouping down 4 levels and I need to go as much as 6 deep.>
Then you will have to do this in the design of the report manually.

The "nesting" limit is 7 levels deep.

JeffCoachman
0
 
BlackbeltrrfAuthor Commented:
I can't seem to get Jim's approach to work as it keeps telling me that the object "Me" can't be found.  I have tried deveral things to no avail.

Jeff the manual approach works but I have a question.  The structure is fine but it holds a place for all levels thus making a great space between lines when all levels are not present.  Can this be collapsed somehow when there is no data on that level?

Thank you very much for all the help you and Jim have given me on this.

Randy
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Randy,

 I'm working on a sample now...

Jim.
0
 
Jeffrey CoachmanMIS LiasonCommented:
...wait for Jim's sample

I am sure that this is an issue that can be solved in more than 1 way.

Besides, tomorrow is Tax Day in the US, and I have a lot of work to do...!
0
 
Jeffrey CoachmanMIS LiasonCommented:
Blackbeltrrf,

Can you post the sample DB that used my technique?

I want to see if I can duplicate Jim's output with my report...

Jeff
0
 
BlackbeltrrfAuthor Commented:
Jeff,
OK here is what I think you wanted.  I need to give a lengthy explanation and I apologize for this but.....I am doing two different versions, one with you and one with Jim's format.  I have removed all the other functions that the database does and stripped it down to the Indented Bill portion only.

For your version:
(Note that the Macro will not work for you because I am extrapolating information from our MAPICS system.  If you run the CostedBOMmcro it will wipe out your data and you will not be able to reconstruct it so DO NOT run the macro.  It is for viewing information only)

As you can see I am building the Bill one "Parent/Child at a time using "Lvl_0qry" thru "Lvl_5_qry" in succession and adding the previous to each query result.  After that a final "BOMCombineqry"  rolls everything up to a "BomTbl_Ttl".  This is the table I am using to display the "BomTbl_Ttlrpt" formated as you suggested.
This report is usable and works great but as you can see it holds all levels a place and causes gaps between parts when levels are missing. (Can these be collapsed?)  if so I am done even though I could call it complete as is I will probably get requests to collapse the report as I explained.

Side note:
To do the format that Jim has suggested, the same routine is ran as above with the following additions:
The CompileQry1 thru 6 is ran in sequence  to create the "TblPartMasterLive".
Using Jim's last post and sample database this will work fine with the exception that I have not looked at it long enough now that he has added a "DisplayOrder" field.  I don't know as of yet how I can get this in a dynamic way as I am building the "TblPartMasterLive" and TblBombu" tables.
If I figure this out (any help is welcome :))this approach will work and not create gaps in the output report data.

So you see I have both methods so very close to working but each is a little shy at the moment for different reasons.

Sorry for the lengthy explanation and as always all the help you and Jim are giving is appreciated more than you know.

Randy
Costing-BOM-For-Jeff.accdb
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Don't want to confuse things more, but you might want to look at mbizup's article:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_19-BOM-parts-list-expansion.html

  Has a nice sample DB in there.

<<So you see I have both methods so very close to working but each is a little shy at the moment for different reasons.>>

  What's lacking?

JimD.
0
 
BlackbeltrrfAuthor Commented:
Jim,

Thank you for the refence It looks promising.  To answer the What's Lacking question is I am not sure how to assign the "DisplayOrder" to the bill as I am not getting them out of the MAPICS system in the same order as final display on the report.

I get the Parent /Child relation ships one level at a time so the order they are inserted into the table is more complicated for my feeble mind than an AutoNumber feature or similar.

Example of Inquiry from MAPICS through ODBC:

1. I ask for End Item Part A (This is Level 0)
2. Mapics only knows what next level PN down is (PN B, C, D....)(These are Level1)
3. At this point I have PN B, C, D...(In that order in my table but this would not necesarrily be the Display order because as I continue down the Hiearchy Level 2 or 3's would have to be inserted into the mix somewhere between PN B-C, C-D....)

This may be simple to overcome but I don't know how at this point but I will work on it.


Rnady

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Randy,

<<To answer the What's Lacking question is I am not sure how to assign the "DisplayOrder" to the bill as I am not getting them out of the MAPICS system in the same order as final display on the report.

I get the Parent /Child relation ships one level at a time so the order they are inserted into the table is more complicated for my feeble mind than an AutoNumber feature or similar.
>>

  That's typical of a lot of systems and I am familer with MAPICS.  In the example I gave, what you'd be filling is the tblBOM and tblPartMaster first.

  At that point you'd run an explosion by calling ExplodeBOM() with the top level item and the qty your producing.  That would fill tblBOMExploded and you could then base your report off that.

  And keep in mind that this is just a simple sample; it doesn't have to be done this way.  I just put enough building blocks there so you could do differnt things with them.

 As Miriam's article shows with the sample DB, she did things slightly diffferently, and also included a tree view example for the indented BOM.

  But if you stand back and look at the concepts, they are basically the same; BOMs are entered one level at a time and there is some type of explosion logic to create the full BOM.

JimD.

 
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<In the example I gave, what you'd be filling is the tblBOM and tblPartMaster first.>>

  Nad if you have linked tables into MAPICS, you don't even need to do that.  Just change  ExplodeBOM() to call the correct tables directly.

JimD.
0
 
BlackbeltrrfAuthor Commented:
Jim,

TYVM For the info.  I wanted to also say that "IN No Way" was I trying to suggest or imply that you needed a detailed explanation on how Mapics is structured or works.  I was simply trying to be exact in my explanation.

Belive me when I say I have the most revered opinion of your and Jeff's expertise and in no way consider myself anywhere close to your knowledge on such matters.

Thanks to you and Jeff for all you have been doing on this lengthy discussion.

Randy
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Randy,

<<TYVM For the info.  I wanted to also say that "IN No Way" was I trying to suggest or imply that you needed a detailed explanation on how Mapics is structured or works.  I was simply trying to be exact in my explanation.>>

  No problem.  I was just trying to tell you I was familer with the concept and understood what you were trying to do/say.

  I haven't taken the slightest bit of offense at anything you have said.

JimD.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Jim.

I was just trying to see if this was possible with a standard grouped report.

If anything, just to see if I could replicate the desired output.

You know me.
It's not even about points, ...it's just something that sounded interesting...

;-)

Jeff
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<It's not even about points, ...it's just something that sounded interesting...>>

  Hey, I was waiting to see what you came up with<g>.  I forgot all about the outline capability in reports.

JimD.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Blackbeltrrf,

So in viewing the sample you posted, are you saying that you would want to hide any top level group that did not have at least one lower level group below it?
Ex. page 6
You want ti hide Lvl1 for 470141 and 470144 because they don't have any Lvl 2 records?

Is this correct?
0
 
BlackbeltrrfAuthor Commented:
Jeff,

I would like to remove all the space between 470141 and 470144 for example to look something like:

1.......470141
1.......470144
1.......520043
1.......570010
1.......710528-020
....2..........050242
....2..........060187
....2..........123036
..........3..........090024
....2..........280140-001
....and so on

Am I dreaming?

Thnaks,
Randy.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks, I just wanted to make sure I understood.

If Jim's sample works then great.

I am just doing this to see if the same thing can be done with grouping.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Try this.....

You will have to insert data that has data in every grouping level to test this fully.

You can view the code and see the "Rhythm" of how this works.

I am sure you can adapt it if it need tweaking.
Because this is a code based solution, you will have to make sure you open the report in "Print Preview"

Jeff
Access--EEQ-26948287-ExpandColla.accdb
0
 
BlackbeltrrfAuthor Commented:
Jeff,

That works Great!  I just moved the Labels to the Page Header and it cleaned up nice.

Jim,
Been messing around with the exploded bom method and it is going to work great as well.

I want to thank you both for all the patience you both had in all my questions and not quite getting it the first time.  You are both truly a professional and totally know your stuff.

I had no solution and now I have two of them to choose from or simply mix and match if I wish.

You both have made me look good here at the office and I thank you more than you realize for the solution.

Maybe you two should get together and solve world hunger or something like that. :)

Again Thanks,
Randy

PS.
Having never had a question of my own how do we close this out and give both of you the maximum points I can?
0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks,

It was actually Jim who got me interested in Reports in MS Access.
He taught me things like Two-pass printing, ...etc

So when you thank me and Jim, you are are really thanking Jim twice.
;-)

Just split the points evenly, and enjoy the weekend!
;-)

Jeff
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  Maybe got him started, but I think he's past me by<g>.  There have been quite a few report questions now where I just wonder how he managed to pull it off, then look look at the question and say "Oh, never thought of doing that".

  Points split is fine here.  Glad you found the info useful.

JimD.

 
0
 
Jeffrey CoachmanMIS LiasonCommented:
Jim,

But you and Pete still manage to "spank" me every once a while, ...that keeps me humble.

;-)

Jeff
0
 
BlackbeltrrfAuthor Commented:
All I can say is that Jim and jeff are great at what they do. I am very thankful they were there to help and stuck it out to completion
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.