How to print a VBA array and group on a field

[John Vasas]

I have a report already made in Access that I need to pass
an array too and to print the contents in the array. I
need the option of grouping on a specfic set of records in
the array. I can get the first group of records printed,
but I have not been able to get the report to repeat and
print the next group of records. Here's an example.

Calling Number:
77429003-009
Drawing Number:
717700817-001
77429004-001
Calling Number:
77429004-001
Drawing Number:
77400800
77420001
79P070000

 

[Marshall Barton]

I have a report already made in Access that I need to pass
an array too and to print the contents in the array. I
need the option of grouping on a specfic set of records in
the array. I can get the first group of records printed,
but I have not been able to get the report to repeat and
print the next group of records. Here's an example.

Calling Number:
77429003-009
Drawing Number:
717700817-001
77429004-001
Calling Number:
77429004-001
Drawing Number:
77400800
77420001
79P070000

Is this an unbound report?

What is the structure of the array?

 

[John Vasas]

Yes, the the report is unbound. I ReDim the array to the
size I need, but in the example I sent the size is 6 x 10.
The 6 would be the number of records and the 10 is the
number of fields. The 10 remands constant. I left out the
other fields so things would not get too complex. Here's
how the values are entered into the array,

Calling Number:
Drawing Number
Others.....
DrawingsList(0, 0) = "77429003-009"
DrawingsList(0, 1) = "77429003-009"

 

[Marshall Barton]

Yes, the the report is unbound. I ReDim the array to the
size I need, but in the example I sent the size is 6 x 10.
The 6 would be the number of records and the 10 is the
number of fields. The 10 remands constant. I left out the
other fields so things would not get too complex. Here's
how the values are entered into the array,

Calling Number:
Drawing Number
Others.....
DrawingsList(0, 0) = "77429003-009"
DrawingsList(0, 1) = "77429003-009"
.
DrawingsList(1, 0) = "77429003-009"
DrawingsList(1, 1) = "717700817-001"
.
DrawingsList(2, 0) = "77429003-009"
DrawingsList(2, 1) = "77429004-001"
.
DrawingsList(3, 0) = "77429004-001"
DrawingsList(3, 1) = "77400800"
.
DrawingsList(4, 0) = "77429004-001"
DrawingsList(4, 1) = "77420001"
.
DrawingsList(5, 0) = "77429004-001"
DrawingsList(5, 1) = "79P070000"

Well, I don't know why you don't have this data in a table??
After all, we are using a database system here!

You've left out the reason why there are only two drawings
for the first Calling number and three for the second one.
So I'll ignore that issue.

But, if you insist on using an array, I think the easiest
way you can do this is by using the report's Page event to
do all the work. The code uses the Print method to display
the array data.

Private Sub Report_Page()
Dim J As Integer, K As Integer
Dim lngLeftEdge As Long
Me.FontName = "Arial"
Me.FontSize = 12

lngLeftEdge = 800 'twips, 1440 twips = 1 inch
Me.CurrentY = 1400
For K = 0 To UBound(DrawingsList)
Me.CurrentX = lngLeftEdge
Me.Print "Calling Number:"
Me.CurrentX = lngLeftEdge + 1000
Me.Print DrawingsList(K, 0)
Me.CurrentX = lngLeftEdge + 500
Me.Print "Drawing Number:"
For J = K To UBound(DrawingsList)
If DrawingsList(J, 0) <> DrawingsList(K, 0) _
Then Exit For
Me.CurrentX = lngLeftEdge + 1500
Me.Print DrawingsList(J, 1)
Next J
K = J - 1
Next K

End Sub

Add your own error handling.

If the data can spill over onto a second page, you've got a
real mess on you hands. To return to my opening remark,
please reconsider and use a table for this kind of thing.

 

[John Vasas]

The records that I sent you were just an example. The data
base has thousands of records in it and the search engine
which has the array only returns a list of specfic records
for a part number entered. I re-wrote the search engine
and used an array to speed things up. What used to take
several minutes, now can be run in less than a minute. At
present a temp table is created and the results are
returned to that table to be printed and then the temp
table is deleted. This can make the data base get big fast
and require a lot of compacting. Thank you for the help
I'll give the code a try.

 

[Marshall Barton]

The records that I sent you were just an example. The data
base has thousands of records in it and the search engine
which has the array only returns a list of specfic records
for a part number entered. I re-wrote the search engine
and used an array to speed things up. What used to take
several minutes, now can be run in less than a minute. At
present a temp table is created and the results are
returned to that table to be printed and then the temp
table is deleted. This can make the data base get big fast
and require a lot of compacting. Thank you for the help
I'll give the code a try.

I think your time would be better spent eliminating the
temporary table bloat problem instead of trying to contort
an unbound report into doing this.

It's easy enough to use a temporary database to hold your
temporary table, then any bloat would be dealt with by
deleting the temporary db. Take a look at Tony's
TempTables.MDB at
http://www.granite.ab.ca/access/temptables.htm

for a good technique. It's not even necessary to link to
temporary table, just set the report's record source query
to use an IN clause to refer to the temp db.

 

[Larry Linson]

I'm open to any suggestions you have.

Strangely, I thought Marsh had just given you some, the first being, use a
temporary table but in a manner that won't cause bloat (and even explained
where to get starter code for this).

 

[John Vasas]

I already had a temporary table running, but it suffered
from bloat. I down loaded the anti-bloat code and I'll
give it a try.