A shove in the right direction is needed

  • Thread starter Thread starter MyndPhlyp
  • Start date Start date
M

MyndPhlyp

It's probably and old chestnut, but I'll ask it anyway.

How can I call a VBA subroutine to retrieve rows for a report?

Here's the situation:

I'm working with a manufacturing Bill Of Material table. For any Item
Number, there could be multiple parents and/or multiple children. All my
attempts at building a recursive SELECT have failed and I'd like to write up
a routine in VBA to feed the report generating a fully-exploded Bill Of
Material.

So, where in the report properties does one reference a subroutine or
function to return rows and what is the syntax used? How do you specify the
various returned columns?

Or am I completely off my nut?
 
If I'm understanding correctly, you can do what you want by using a
subreport on the report, where the subreport is based on the children
records.

If this isn't your solution, then tell us more about what you're actually
doing....recursive SELECT statements?
 
Ken Snell said:
If I'm understanding correctly, you can do what you want by using a
subreport on the report, where the subreport is based on the children
records.

If this isn't your solution, then tell us more about what you're actually
doing....recursive SELECT statements?
Click to expand...

I'm trying to avoid using 'n' number of aliased tables (or 'n' number of
subreports) to generate a fully exploded hierarchical relationship.

In Access Cookbook (O'Reilly), page 42, there is an example of building a
query showing the relationship between employees and supervisors. The
solution isn't very elegant and works only where there is a single parent
for any level in the hierarchy. The caveat shows up in the last two
sentences of the example:

"For hierarchies in which one child can have many parents - such as parts
and assemblies in a bill of materials database - a separate table is needed
to contain the multiple records needed for each child, each one specifying a
different parent."

I have to play the cards dealt to me - the table structure is what it is.

Using subreports, it looks like I am in the same boat as trying to build a
query (but I've been wrong once before).

I'm going to want to indent each component's item number depending on the
level it appears in the hierarchy. (The Space() function does nicely - give
it the level number and get the right number of spaces in return. Appending
it to the front of the item number takes care of the rest.) The remaining
component columns remain aligned.

I don't want to limit the nesting levels. Aliasing 'n' number of tables or
subreports definitely limits things and maintenance later on down the road
becomes a bear as you have to touch each and every iteration.

So, I figured the better way to handle this is to somehow feed the report
details from some VBA code ... if I can only figure out where to place the
hook. Therein lies the problem.
 
I don't have O'Reilly's book, so I'm unfamiliar with this topic.

I admit that I don't fully understand what you want to display on the
report, as I don't know the data nor tables. Perhaps if you provide some
examples of SQL statements that you're trying to use and some examples of
how the data should be displayed, we may identify a solution for you.
 
Ken Snell said:
I don't have O'Reilly's book, so I'm unfamiliar with this topic.

I admit that I don't fully understand what you want to display on the
report, as I don't know the data nor tables. Perhaps if you provide some
examples of SQL statements that you're trying to use and some examples of
how the data should be displayed, we may identify a solution for you.
Click to expand...

Reading between the lines, you are pretty much implying that generating an
Access report based purely on the output of VBA is not a possibility and
that the only workaround is to embed nested subreport after nested subreport
for each level of the hierarchy. Is this an accurate assessment?

Or is it possible to direct the output from VBA code to a report-like
display (not necessarily a defined Access report) other than Debug.Print,
message boxes and forms? (In other words, I am more than willing to
programmatically do all the header, footer, paging, detail, formatting and
other stuff.)
 
MyndPhlyp said:
It's probably and old chestnut, but I'll ask it anyway.

How can I call a VBA subroutine to retrieve rows for a report?

Here's the situation:

I'm working with a manufacturing Bill Of Material table. For any Item
Number, there could be multiple parents and/or multiple children. All my
attempts at building a recursive SELECT have failed and I'd like to write up
a routine in VBA to feed the report generating a fully-exploded Bill Of
Material.

So, where in the report properties does one reference a subroutine or
function to return rows and what is the syntax used? How do you specify the
various returned columns?
Click to expand...


I think I understand what you're doing, but as you've
already determined, it not going to yield to an simple
solution. I suggest that you try using an unbound report
and doing all the work with VBA code in the report's event
procedures.

Open a recordset on your table in the report's Open event
and locate the top level items's record. Then in the Detail
section's Format event copy the recordset fields to the text
boxes in the detail section. (Consider setting the
controls' Left property instead of using the Space
function.)

After populating the controls with the appropriate values,
locate the next item's record (keeping track of the levels
as you go). If there is another item to process, set
Me.NextRecord = False to tell access that you have another
detail to process.

Use the report's Close event to close the recordset and
perfom any other needed cleanup.
 
<...big ol' snip...>

Marshall:

That looks EXACTLY like what I was searching for - total control over the
report contents. It gets me around so many headaches (albeit creating some
new ones in the process).

An unbound report and objects was what I had hopes of using. The bit I was
missing was how to control Access's report from start to finish. The
NextRecord = Boolean was the key. (I'll look at Left, too.)

In my experience, a lot of reporting packages (Crystal and Access, just to
name two) are great for simple and mildly complex reports. They tend to fall
short when it comes to situations such as my immediate one (and several on
the not-too-distant horizon). Couple that with the limitations of the data
source (like ODBC, Btrieve, etc.) and things really get either impossible or
highly undesirable. The only solution left is to go back to the "old school"
way of doing things.

Thanx a bunch for teaching an old dog yet another new trick.
 
Back
Top