can i loop thru a listbox generating a report for each list item?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a table of a million records, with between 60-120 keys (it varies).

the listbox is built from reading the table to produce the unique keys.
every time I select a key, the process takes about 10 minutes to loop thru
and produce a report which I then dump to a pdf. I'd like a way to automate
this!

i'm running access 2000. and I'm not incharge of the choice of database (I
know this is probably too many records!) and I didn't write the code to
create the listbox, so I'd rather not touch it.

Can you help ?
 
Kate L Fletcher said:
I've got a table of a million records, with between 60-120 keys (it varies).

the listbox is built from reading the table to produce the unique keys.
every time I select a key, the process takes about 10 minutes to loop thru
and produce a report which I then dump to a pdf. I'd like a way to automate
this!

i'm running access 2000. and I'm not incharge of the choice of database (I
know this is probably too many records!) and I didn't write the code to
create the listbox, so I'd rather not touch it.

Can you help ?
Click to expand...
 
Kate,

A million records is not too much. But on the face of what you have
said so far, it sounds like you are doing it the hard way.

Am I correct in my understanding?...
- the table has about a million records
- one of the fields in the table is a Key field
- within the million records, the number of unique Key values is 60-120
- you have a Listbox that displays a list of these unique Key values
- you have a report based on the data in the table
- you select one of the Keys from the listbox
- your report is printed, including only the records matching the Key
you selected in the listbox.
 
Steve,
yes, that's right. and yes, I'm aware that it's a bit silly to read the
million records every time I need to construct the listbox!

however, I did not write this program, it came from a software company that
we don't have a relationship with any more. what I'd like to do is keep
their program/s as they are (because I know they do work, if slowly), and
have a sort of "outside loop" that does what a human currently has to do -
which is:
open the big report with 1 million records in
select each member of the list in turn
construct the report for that key value (120th of the million records)
dump it to a pdf
get the next item in the list
etc.

is that possible?

thanks for your prompt reply to my original post.
K8
 
Kate,

Sorry to be obscure or obtuse. But if what you want to achieve here is
a separate report for each of the Keys, then I don't see the relevance
of the Listbox at all.

How are you going about exporting to PDF?

Another pertinent question... in the design of the table, is there an
index on the Key field? If not, can you add one?
 
hello Steve
the listbox thingy: I think this was originally set up because we didn't
wlays print all the reports, as not everyone was in the scheme to start with.
so we just picked the ones we wanted out of the list....

PDF dump is via PDF995 (a free package we got from the www)

not sure about adding an index - is that different from the primary key?
(the table already has a primary key - which is a different field from the
one in the listbox).

thanks again for your prompt reply.
Kate
 
Kate,

Going backwards...

In design view of the table, select the Key field. In the Properties
towards the bottom, find the Indexed property. Enter: Yes (Duplicates OK)

PDF995 is good. It's not free.

So, now we are really in the wrong newsgroup. Not really a good job for
a macro. You need a VBA porocedure to handle this, since it involves
looping through records.

So, this is "air code" untested, but the general concept is there. Have
a go.
_____________

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = DBEngine(0)(0)
Set rst = dbs.OpenRecordset("SELECT DISTINCT [Key] FROM YourTable",
dbOpenSnapshot)
Do Until rst.EOF
DoCmd.OpenReport "YourReport", , , "[Key] = " & rst![Key]
rst.MoveNext
Loop
rst.Close
Set rst=Nothing
set dbs = Nothing
______________

This code assumes that Key is a number data type. If it is text, we
would need:
DoCmd.OpenReport "YourReport", , , "[Key] = '" & rst![Key] & "'"

And I have not resumed to know how you are handling the interface with
PDF995, and the naming of the files. This code will work assuming you have:
- PDF995 set up as the Printer in the design view of the report
- PDF995 configured to automatically name the files
 
thanks Steve, I'll give it a whirl and see how it goes.
Kate

Steve Schapel said:
Kate,

Going backwards...

In design view of the table, select the Key field. In the Properties
towards the bottom, find the Indexed property. Enter: Yes (Duplicates OK)

PDF995 is good. It's not free.

So, now we are really in the wrong newsgroup. Not really a good job for
a macro. You need a VBA porocedure to handle this, since it involves
looping through records.

So, this is "air code" untested, but the general concept is there. Have
a go.
_____________

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = DBEngine(0)(0)
Set rst = dbs.OpenRecordset("SELECT DISTINCT [Key] FROM YourTable",
dbOpenSnapshot)
Do Until rst.EOF
DoCmd.OpenReport "YourReport", , , "[Key] = " & rst![Key]
rst.MoveNext
Loop
rst.Close
Set rst=Nothing
set dbs = Nothing
______________

This code assumes that Key is a number data type. If it is text, we
would need:
DoCmd.OpenReport "YourReport", , , "[Key] = '" & rst![Key] & "'"

And I have not resumed to know how you are handling the interface with
PDF995, and the naming of the files. This code will work assuming you have:
- PDF995 set up as the Printer in the design view of the report
- PDF995 configured to automatically name the files

--
Steve Schapel, Microsoft Access MVP
hello Steve
the listbox thingy: I think this was originally set up because we didn't
wlays print all the reports, as not everyone was in the scheme to start with.
so we just picked the ones we wanted out of the list....

PDF dump is via PDF995 (a free package we got from the www)

not sure about adding an index - is that different from the primary key?
(the table already has a primary key - which is a different field from the
one in the listbox).
Click to expand...
Click to expand...
 
Back
Top