Page ranges for faxable reports

[Randall Arnold]

I have a query that produces a report of items to be delivered that can vary
from one to whatever pages. Deliverable items are grouped by customer, and
subgrouped by address. A formfeed is forced on every customer change.

Here's my dilemma: the client wants to fax each customer's report separately
even though he wants to generate all reports at once. Each individual
customer-based report should list "Page 1 of X", "Page 2 of X" etc in the
customer's header. Now, I can get total pages to work fine, but I don't
want that-- I want a new page numebring beginning with each customer.
However, I have searched and experimented until I can't see straight and
nothing I've tried has produced the proper result.

Any ideas out there? Surely this one's been done!

TIA,

Randall Arnold

 

[Randall Arnold]

Okay, I found out how to reset the page numbers for each group using a
macro-- but I still can't get the total page count to change to reflect the
number of pages within each group. Is this not possible?

BTW, using Access 2003.

Randall Arnold

 

[Marshall Barton]

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.

 

[Randall Arnold]

Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The client knows to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.
--
Marsh
MVP [MS Access]

Okay, I found out how to reset the page numbers for each group using a
macro-- but I still can't get the total page count to change to reflect
the
number of pages within each group. Is this not possible?

BTW, using Access 2003.

Randall Arnold

 

[Marshall Barton]

Oh, Ok, I thought you were goint to try to automate the
faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.

 

[Randall Arnold]

Automation code may be the next step. That's actually more my forte so I'm
sure I'll make that part work.

Thanks again!

Randall Arnold

Oh, Ok, I thought you were goint to try to automate the
faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.
--
Marsh
MVP [MS Access]

Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The client knows to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

"Marshall Barton" wrote

 

[Marshall Barton]

That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.

 

[Randall Arnold]

I'd be doing it on the fly, faxing the segments in chunks, using a fax
driver on the PC. The same code you referred me to for manipulating page
ranges should adapt for what I may try next. Fax number will be stored in
the customer table and pulled out as needed, using the technique that
manipulates the page total for each section.

Randall Arnold

That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.
--
Marsh
MVP [MS Access]

Automation code may be the next step. That's actually more my forte so
I'm
sure I'll make that part work.

 

[Randall Arnold]

May be a moot point after all-- Access 2003 blows up on one line of the code
MS provides in the KB article.

At this line,

Set grpPages = DB.OpenRecordset("Backhoe Queue", dbOpenTable)

The error "Runtime error '3219': Invalid Operation"

Checking watched values show that the database variable DB never gets
assigned, even though the following code is included:

Set DB = DBEngine.Workspaces(0).Databases(0)

Looks like I need to do some further research... : (

Randall Arnold

That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.
--
Marsh
MVP [MS Access]

Automation code may be the next step. That's actually more my forte so
I'm
sure I'll make that part work.

 

[Marshall Barton]

Do you have reference to the DAO library?
--
Marsh
MVP [MS Access]

May be a moot point after all-- Access 2003 blows up on one line of the code
MS provides in the KB article.

At this line,

Set grpPages = DB.OpenRecordset("Backhoe Queue", dbOpenTable)

The error "Runtime error '3219': Invalid Operation"

Checking watched values show that the database variable DB never gets
assigned, even though the following code is included:

Set DB = DBEngine.Workspaces(0).Databases(0)

Looks like I need to do some further research... : (

Randall Arnold

"Marshall Barton" wrote

That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.
--
Marsh
MVP [MS Access]

Automation code may be the next step. That's actually more my forte so
I'm
sure I'll make that part work.

faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.


Randall Arnold wrote:

Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The client knows
to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.


Randall Arnold wrote:

Okay, I found out how to reset the page numbers for each group using a
macro-- but I still can't get the total page count to change to
reflect
the
number of pages within each group. Is this not possible?

BTW, using Access 2003.

Randall Arnold

I have a query that produces a report of items to be delivered that
can
vary from one to whatever pages. Deliverable items are grouped by
customer, and subgrouped by address. A formfeed is forced on every
customer change.

Here's my dilemma: the client wants to fax each customer's report
separately even though he wants to generate all reports at once.
Each
individual customer-based report should list "Page 1 of X", "Page 2
of
X"
etc in the customer's header. Now, I can get total pages to work
fine,
but I don't want that-- I want a new page numebring beginning with
each
customer. However, I have searched and experimented until I can't
see
straight and nothing I've tried has produced the proper result.

 

[Randall Arnold]

Yep, version 3.6.

Randall Arnold

Do you have reference to the DAO library?
--
Marsh
MVP [MS Access]

May be a moot point after all-- Access 2003 blows up on one line of the
code
MS provides in the KB article.

At this line,

Set grpPages = DB.OpenRecordset("Backhoe Queue", dbOpenTable)

The error "Runtime error '3219': Invalid Operation"

Checking watched values show that the database variable DB never gets
assigned, even though the following code is included:

Set DB = DBEngine.Workspaces(0).Databases(0)

Looks like I need to do some further research... : (

Randall Arnold

"Marshall Barton" wrote

That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.
--
Marsh
MVP [MS Access]


Randall Arnold wrote:

Automation code may be the next step. That's actually more my forte so
I'm
sure I'll make that part work.

faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.


Randall Arnold wrote:

Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The client knows
to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.


Randall Arnold wrote:

Okay, I found out how to reset the page numbers for each group using
a
macro-- but I still can't get the total page count to change to
reflect
the
number of pages within each group. Is this not possible?

BTW, using Access 2003.

Randall Arnold

I have a query that produces a report of items to be delivered that
can
vary from one to whatever pages. Deliverable items are grouped by
customer, and subgrouped by address. A formfeed is forced on every
customer change.

Here's my dilemma: the client wants to fax each customer's report
separately even though he wants to generate all reports at once.
Each
individual customer-based report should list "Page 1 of X", "Page
2
of
X"
etc in the customer's header. Now, I can get total pages to work
fine,
but I don't want that-- I want a new page numebring beginning with
each
customer. However, I have searched and experimented until I can't
see
straight and nothing I've tried has produced the proper result.

 

[Marshall Barton]

Hmmm? Grasping at straws now.

Is Backhoe Queue a local table or is it a linked table or
query?

Does dbOpenDynaset help?

Does using
Set db = CurrentDb()
instead of
Set db = DbEngin(0)(0)
make a difference?
--
Marsh
MVP [MS Access]

Yep, version 3.6.

"Marshall Barton" wrote

Do you have reference to the DAO library?

May be a moot point after all-- Access 2003 blows up on one line of the
code
MS provides in the KB article.

At this line,

Set grpPages = DB.OpenRecordset("Backhoe Queue", dbOpenTable)

The error "Runtime error '3219': Invalid Operation"

Checking watched values show that the database variable DB never gets
assigned, even though the following code is included:

Set DB = DBEngine.Workspaces(0).Databases(0)

Looks like I need to do some further research... : (

Randall Arnold

"Marshall Barton" wrote
That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.
--
Marsh
MVP [MS Access]


Randall Arnold wrote:

Automation code may be the next step. That's actually more my forte so
I'm
sure I'll make that part work.

faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.


Randall Arnold wrote:

Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The client knows
to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.


Randall Arnold wrote:

Okay, I found out how to reset the page numbers for each group using
a
macro-- but I still can't get the total page count to change to
reflect
the
number of pages within each group. Is this not possible?

BTW, using Access 2003.

Randall Arnold

I have a query that produces a report of items to be delivered that
can
vary from one to whatever pages. Deliverable items are grouped by
customer, and subgrouped by address. A formfeed is forced on every
customer change.

Here's my dilemma: the client wants to fax each customer's report
separately even though he wants to generate all reports at once.
Each
individual customer-based report should list "Page 1 of X", "Page
2
of
X"
etc in the customer's header. Now, I can get total pages to work
fine,
but I don't want that-- I want a new page numebring beginning with
each
customer. However, I have searched and experimented until I can't
see
straight and nothing I've tried has produced the proper result.

 

[Randall Arnold]

It's a linked table, but that point is moot since the Set command isn't even
working (no error on Dim statement for DB). I tried CurrentDB and got the
same result. I'll try your other suggestions next.

Thanks!

Hmmm? Grasping at straws now.

Is Backhoe Queue a local table or is it a linked table or
query?

Does dbOpenDynaset help?

Does using
Set db = CurrentDb()
instead of
Set db = DbEngin(0)(0)
make a difference?
--
Marsh
MVP [MS Access]

Yep, version 3.6.

"Marshall Barton" wrote

Do you have reference to the DAO library?

Randall Arnold wrote:

May be a moot point after all-- Access 2003 blows up on one line of the
code
MS provides in the KB article.

At this line,

Set grpPages = DB.OpenRecordset("Backhoe Queue", dbOpenTable)

The error "Runtime error '3219': Invalid Operation"

Checking watched values show that the database variable DB never gets
assigned, even though the following code is included:

Set DB = DBEngine.Workspaces(0).Databases(0)

Looks like I need to do some further research... : (

Randall Arnold

"Marshall Barton" wrote
That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.
--
Marsh
MVP [MS Access]


Randall Arnold wrote:

Automation code may be the next step. That's actually more my forte
so
I'm
sure I'll make that part work.

faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.


Randall Arnold wrote:

Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The client
knows
to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.


Randall Arnold wrote:

Okay, I found out how to reset the page numbers for each group
using
a
macro-- but I still can't get the total page count to change to
reflect
the
number of pages within each group. Is this not possible?

BTW, using Access 2003.

Randall Arnold

I have a query that produces a report of items to be delivered
that
can
vary from one to whatever pages. Deliverable items are grouped
by
customer, and subgrouped by address. A formfeed is forced on
every
customer change.

Here's my dilemma: the client wants to fax each customer's
report
separately even though he wants to generate all reports at once.
Each
individual customer-based report should list "Page 1 of X",
"Page
2
of
X"
etc in the customer's header. Now, I can get total pages to
work
fine,
but I don't want that-- I want a new page numebring beginning
with
each
customer. However, I have searched and experimented until I
can't
see
straight and nothing I've tried has produced the proper result.

 

[Marshall Barton]

That's it.

dbOpenTable only applies to tables in the db object. Using
dbOpenDynaset should get you past this issue.
--
Marsh
MVP [MS Access]

It's a linked table, but that point is moot since the Set command isn't even
working (no error on Dim statement for DB). I tried CurrentDB and got the
same result. I'll try your other suggestions next.

Hmmm? Grasping at straws now.

Is Backhoe Queue a local table or is it a linked table or
query?

Does dbOpenDynaset help?

Does using
Set db = CurrentDb()
instead of
Set db = DbEngin(0)(0)
make a difference?
--
Marsh
MVP [MS Access]

Yep, version 3.6.

"Marshall Barton" wrote
Do you have reference to the DAO library?

Randall Arnold wrote:

May be a moot point after all-- Access 2003 blows up on one line of the
code
MS provides in the KB article.

At this line,

Set grpPages = DB.OpenRecordset("Backhoe Queue", dbOpenTable)

The error "Runtime error '3219': Invalid Operation"

Checking watched values show that the database variable DB never gets
assigned, even though the following code is included:

Set DB = DBEngine.Workspaces(0).Databases(0)

Looks like I need to do some further research... : (

Randall Arnold

"Marshall Barton" wrote
That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.
--
Marsh
MVP [MS Access]


Randall Arnold wrote:

Automation code may be the next step. That's actually more my forte
so
I'm
sure I'll make that part work.

faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.


Randall Arnold wrote:

Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The client
knows
to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.


Randall Arnold wrote:

Okay, I found out how to reset the page numbers for each group
using
a
macro-- but I still can't get the total page count to change to
reflect
the
number of pages within each group. Is this not possible?

BTW, using Access 2003.

Randall Arnold

I have a query that produces a report of items to be delivered
that
can
vary from one to whatever pages. Deliverable items are grouped
by
customer, and subgrouped by address. A formfeed is forced on
every
customer change.

Here's my dilemma: the client wants to fax each customer's
report
separately even though he wants to generate all reports at once.
Each
individual customer-based report should list "Page 1 of X",
"Page
2
of
X"
etc in the customer's header. Now, I can get total pages to
work
fine,
but I don't want that-- I want a new page numebring beginning
with
each
customer. However, I have searched and experimented until I
can't
see
straight and nothing I've tried has produced the proper result.

 

[Randall Arnold]

That got me to the next error! Which was:

Runtime error '3251': Operation is not supported for this type of object

on the line:

grpPages.Index = "Primary Key"

Maybe not available with a Dynaset? Ay yi yi...

Thanks for your continued help, Marsh. I'll check the MSKB again.

Randall Arnold

That's it.

dbOpenTable only applies to tables in the db object. Using
dbOpenDynaset should get you past this issue.
--
Marsh
MVP [MS Access]

It's a linked table, but that point is moot since the Set command isn't
even
working (no error on Dim statement for DB). I tried CurrentDB and got the
same result. I'll try your other suggestions next.

Hmmm? Grasping at straws now.

Is Backhoe Queue a local table or is it a linked table or
query?

Does dbOpenDynaset help?

Does using
Set db = CurrentDb()
instead of
Set db = DbEngin(0)(0)
make a difference?
--
Marsh
MVP [MS Access]



Randall Arnold wrote:
Yep, version 3.6.

"Marshall Barton" wrote
Do you have reference to the DAO library?

Randall Arnold wrote:

May be a moot point after all-- Access 2003 blows up on one line of
the
code
MS provides in the KB article.

At this line,

Set grpPages = DB.OpenRecordset("Backhoe Queue", dbOpenTable)

The error "Runtime error '3219': Invalid Operation"

Checking watched values show that the database variable DB never gets
assigned, even though the following code is included:

Set DB = DBEngine.Workspaces(0).Databases(0)

Looks like I need to do some further research... : (

Randall Arnold

"Marshall Barton" wrote
That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.
--
Marsh
MVP [MS Access]


Randall Arnold wrote:

Automation code may be the next step. That's actually more my forte
so
I'm
sure I'll make that part work.

faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.


Randall Arnold wrote:

Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The client
knows
to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.


Randall Arnold wrote:

Okay, I found out how to reset the page numbers for each group
using
a
macro-- but I still can't get the total page count to change to
reflect
the
number of pages within each group. Is this not possible?

BTW, using Access 2003.

Randall Arnold

I have a query that produces a report of items to be delivered
that
can
vary from one to whatever pages. Deliverable items are grouped
by
customer, and subgrouped by address. A formfeed is forced on
every
customer change.

Here's my dilemma: the client wants to fax each customer's
report
separately even though he wants to generate all reports at
once.
Each
individual customer-based report should list "Page 1 of X",
"Page
2
of
X"
etc in the customer's header. Now, I can get total pages to
work
fine,
but I don't want that-- I want a new page numebring beginning
with
each
customer. However, I have searched and experimented until I
can't
see
straight and nothing I've tried has produced the proper
result.

 

[Marshall Barton]

You got it in one. The Index property is only available
with a table type recordset (dbOpenTable).

That article assumes the table is local to the front end mdb
file. This would make a lot of sense since you would not
want to use a back end table in case two users were running
the report at the same time.

If this a split, but single user application, you could open
the backend database (using OpenDatabase) and use that db
object to open a table type recordset.

Since this is a temporary table, you may want to avoid the
bloat issues by either placing the table in a temporary
database:
http://www.granite.ab.ca/access/temptables.htm

or by using an approach that uses arrays instead of a
temporary table
http://www.mvps.org/access/reports/rpt0013.htm

 

[Randall Arnold]

Thanks once again Marsh!

I need the backend separated due to the way this is being developed and
deployed.

However, I actually got that code working after making numerous changes, but
out of the blue I now get an error saying the referenced field doesn't
exist! Very peculiar, since it does indeed exist (I verified, including
spelling and syntax) and in fact the code did finally work at one point. I
don't know that I did anything to change that, but...

Anyway, I obviously have another bug to chase down, because the field error
is a showstopper. I may try the array code, though, once I figure the field
problem out.

Randall Arnold

You got it in one. The Index property is only available
with a table type recordset (dbOpenTable).

That article assumes the table is local to the front end mdb
file. This would make a lot of sense since you would not
want to use a back end table in case two users were running
the report at the same time.

If this a split, but single user application, you could open
the backend database (using OpenDatabase) and use that db
object to open a table type recordset.

Since this is a temporary table, you may want to avoid the
bloat issues by either placing the table in a temporary
database:
http://www.granite.ab.ca/access/temptables.htm

or by using an approach that uses arrays instead of a
temporary table
http://www.mvps.org/access/reports/rpt0013.htm
--
Marsh
MVP [MS Access]

That got me to the next error! Which was:

Runtime error '3251': Operation is not supported for this type of object

on the line:

grpPages.Index = "Primary Key"

Maybe not available with a Dynaset? Ay yi yi...

Thanks for your continued help, Marsh. I'll check the MSKB again.


"Marshall Barton" wrote

 

[Randall Arnold]

There's something truly bizarre going on here.

I undid everything I'd done up to now and tried the array-based code you
linked. The first time I ran it, everything worked EXACTLY like I'd wanted!
After confirming that, I shifted the output text box to the right, changed
the font size and reran it-- and that time it didn't work. The code tests
for Me.Pages = 0 and now, after running the routine once, Me.Pages *always*
resolves to zero. I've quit Access, rebooted, added debug statements to see
what's going on, etc-- can't figure it out! Makes no sense that simply
formatting the text box would do this, so something else must be going on.
But I copied the code to another report that works the same way, and got the
same result: nothing.

This stuff makes me nuts.

Randall Arnold

You got it in one. The Index property is only available
with a table type recordset (dbOpenTable).

That article assumes the table is local to the front end mdb
file. This would make a lot of sense since you would not
want to use a back end table in case two users were running
the report at the same time.

If this a split, but single user application, you could open
the backend database (using OpenDatabase) and use that db
object to open a table type recordset.

Since this is a temporary table, you may want to avoid the
bloat issues by either placing the table in a temporary
database:
http://www.granite.ab.ca/access/temptables.htm

or by using an approach that uses arrays instead of a
temporary table
http://www.mvps.org/access/reports/rpt0013.htm
--
Marsh
MVP [MS Access]

That got me to the next error! Which was:

Runtime error '3251': Operation is not supported for this type of object

on the line:

grpPages.Index = "Primary Key"

Maybe not available with a Dynaset? Ay yi yi...

Thanks for your continued help, Marsh. I'll check the MSKB again.


"Marshall Barton" wrote

 

[Marshall Barton]

Clarity derives from tranquility of thought, Grasshopper
;-)

It sounds like you removed the text box that refers to the
Pages property. Access reports do not waste their time
calculating Pages unless it is used in a control on the
report.
--
Marsh
MVP [MS Access]

There's something truly bizarre going on here.

I undid everything I'd done up to now and tried the array-based code you
linked. The first time I ran it, everything worked EXACTLY like I'd wanted!
After confirming that, I shifted the output text box to the right, changed
the font size and reran it-- and that time it didn't work. The code tests
for Me.Pages = 0 and now, after running the routine once, Me.Pages *always*
resolves to zero. I've quit Access, rebooted, added debug statements to see
what's going on, etc-- can't figure it out! Makes no sense that simply
formatting the text box would do this, so something else must be going on.
But I copied the code to another report that works the same way, and got the
same result: nothing.

This stuff makes me nuts.

Randall Arnold

You got it in one. The Index property is only available
with a table type recordset (dbOpenTable).

That article assumes the table is local to the front end mdb
file. This would make a lot of sense since you would not
want to use a back end table in case two users were running
the report at the same time.

If this a split, but single user application, you could open
the backend database (using OpenDatabase) and use that db
object to open a table type recordset.

Since this is a temporary table, you may want to avoid the
bloat issues by either placing the table in a temporary
database:
http://www.granite.ab.ca/access/temptables.htm

or by using an approach that uses arrays instead of a
temporary table
http://www.mvps.org/access/reports/rpt0013.htm
--
Marsh
MVP [MS Access]

That got me to the next error! Which was:

Runtime error '3251': Operation is not supported for this type of object

on the line:

grpPages.Index = "Primary Key"

Maybe not available with a Dynaset? Ay yi yi...

Thanks for your continued help, Marsh. I'll check the MSKB again.


That's it.

dbOpenTable only applies to tables in the db object. Using
dbOpenDynaset should get you past this issue.


Randall Arnold wrote:

It's a linked table, but that point is moot since the Set command isn't
even
working (no error on Dim statement for DB). I tried CurrentDB and got
the
same result. I'll try your other suggestions next.

Hmmm? Grasping at straws now.

Is Backhoe Queue a local table or is it a linked table or
query?

Does dbOpenDynaset help?

Does using
Set db = CurrentDb()
instead of
Set db = DbEngin(0)(0)
make a difference?


Randall Arnold wrote:
Yep, version 3.6.

"Marshall Barton" wrote
Do you have reference to the DAO library?

Randall Arnold wrote:

May be a moot point after all-- Access 2003 blows up on one line of
the
code
MS provides in the KB article.

At this line,

Set grpPages = DB.OpenRecordset("Backhoe Queue", dbOpenTable)

The error "Runtime error '3219': Invalid Operation"

Checking watched values show that the database variable DB never
gets
assigned, even though the following code is included:

Set DB = DBEngine.Workspaces(0).Databases(0)

Looks like I need to do some further research... : (


"Marshall Barton" wrote
That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.


Randall Arnold wrote:
Automation code may be the next step. That's actually more my
forte
so
I'm
sure I'll make that part work.

faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.


Randall Arnold wrote:
Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The client
knows
to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.

 

[Randall Arnold]

You are correct! In the process of cleaning up I deleted the test boxes I'd
created-- didn't occur to me until I read just what you now said but on the
experts exchange. Restored the Pages textbox to force the 2-pass format and
all works well again.

Whoo hoo! My client will be pleased. I owe ya one, Marsh. But I'm still
gonna press for a new GroupPages feature for the next version of Access.

Randall
Visio MVP in training ; )

Clarity derives from tranquility of thought, Grasshopper
;-)

It sounds like you removed the text box that refers to the
Pages property. Access reports do not waste their time
calculating Pages unless it is used in a control on the
report.
--
Marsh
MVP [MS Access]

There's something truly bizarre going on here.

I undid everything I'd done up to now and tried the array-based code you
linked. The first time I ran it, everything worked EXACTLY like I'd
wanted!
After confirming that, I shifted the output text box to the right, changed
the font size and reran it-- and that time it didn't work. The code tests
for Me.Pages = 0 and now, after running the routine once, Me.Pages
*always*
resolves to zero. I've quit Access, rebooted, added debug statements to
see
what's going on, etc-- can't figure it out! Makes no sense that simply
formatting the text box would do this, so something else must be going on.
But I copied the code to another report that works the same way, and got
the
same result: nothing.

This stuff makes me nuts.

Randall Arnold

You got it in one. The Index property is only available
with a table type recordset (dbOpenTable).

That article assumes the table is local to the front end mdb
file. This would make a lot of sense since you would not
want to use a back end table in case two users were running
the report at the same time.

If this a split, but single user application, you could open
the backend database (using OpenDatabase) and use that db
object to open a table type recordset.

Since this is a temporary table, you may want to avoid the
bloat issues by either placing the table in a temporary
database:
http://www.granite.ab.ca/access/temptables.htm

or by using an approach that uses arrays instead of a
temporary table
http://www.mvps.org/access/reports/rpt0013.htm
--
Marsh
MVP [MS Access]



Randall Arnold wrote:

That got me to the next error! Which was:

Runtime error '3251': Operation is not supported for this type of object

on the line:

grpPages.Index = "Primary Key"

Maybe not available with a Dynaset? Ay yi yi...

Thanks for your continued help, Marsh. I'll check the MSKB again.


That's it.

dbOpenTable only applies to tables in the db object. Using
dbOpenDynaset should get you past this issue.


Randall Arnold wrote:

It's a linked table, but that point is moot since the Set command
isn't
even
working (no error on Dim statement for DB). I tried CurrentDB and got
the
same result. I'll try your other suggestions next.

Hmmm? Grasping at straws now.

Is Backhoe Queue a local table or is it a linked table or
query?

Does dbOpenDynaset help?

Does using
Set db = CurrentDb()
instead of
Set db = DbEngin(0)(0)
make a difference?


Randall Arnold wrote:
Yep, version 3.6.

"Marshall Barton" wrote
Do you have reference to the DAO library?

Randall Arnold wrote:

May be a moot point after all-- Access 2003 blows up on one line
of
the
code
MS provides in the KB article.

At this line,

Set grpPages = DB.OpenRecordset("Backhoe Queue", dbOpenTable)

The error "Runtime error '3219': Invalid Operation"

Checking watched values show that the database variable DB never
gets
assigned, even though the following code is included:

Set DB = DBEngine.Workspaces(0).Databases(0)

Looks like I need to do some further research... : (


"Marshall Barton" wrote
That may be, but using code to rip a report into pieces is a
nontrivial issue that will require some third party product
such as a good pdf driver or something like that.

Just trying to keep the destination in sight, so you don't
waste time going sown a dead end toute.


Randall Arnold wrote:
Automation code may be the next step. That's actually more my
forte
so
I'm
sure I'll make that part work.

faxing in code. If it's going to be a manual paper fax,
then the pages per group would be the next step.


Randall Arnold wrote:
Thanks, I'll check that one out.

The faxing isn't the issue; just the page numbering. The
client
knows
to
separate the report into groups of faxable sheets per vendor.

Randall Arnold

Here's an article about getting the pages per group:

http://support.microsoft.com/default.aspx?scid=kb;en-us;306127&Product=acc97

but I don't think any of this is going to help you fax
pieces of a report to separate people. Try working out the
faxing issues before you spend too much time working on the
report.