Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Database Reports: Determine what page number will display record "x"

Posted on 2008-10-20
16
Medium Priority
?
579 Views
Last Modified: 2013-11-28
I need  an Access 2003 Database running on Windows to run a report behind the scenes and then return the page number where a specific record "starts".

Example:  My Flower database holds many individual records and I print out a book that has options to A) print one flower record per page as well as B) print the records on continuously with no page breaks.  

I have the code that works for opening the report page in preview mode for a specifc record I have selected.  Note:  This is NOT filtering.  It is the process of viewing or going to (F5 function) a specific page within the report.  ;)

What I don't have figured out is how to find out when the next record starts.  (ie: what if a single record takes up 3 pages, but others pages have 3 records included?).  

Here's what I have thus far:
DoCmd.OpenReport "MyFlowers", acViewPreview
SendKeys "{f5}"
SendKeys "454"
SendKeys "{enter}"

By the way, I've searched high and low just to find anything that would get me this far.  I think I need a point!  *grin*

Any help would be much appreciated.
0
Comment
Question by:bzspud
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22764102
bzspud

Try this:

JeffCoachman

Access-EEQ23831383DetermineWhatP.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22764535
Oops

Here it is again:
Access-EEQ23831383DetermineWhatP.mdb
0
 

Author Comment

by:bzspud
ID: 22765073
Jeff,

Thanks for taking this on.   You're on the right track, however, the format of the report is different that what you have setup.  when I changed it to my format, the code returned a "0" value.   I know the report is working as I double checked it.  

Report Formatting:  Rather than horizontal records, my reports are vertical, reading from the top down like a book with a full sheet of information.  (think of a landscaping book with info, photo's etc just for 1 item.  

With the continuous reports, the same thing happens, but the information is squeezed into a double Columnar view, zigzagging vertically up and down like a long skinny newspaper article.    When the first record uses all the space it needs fully complete, the next record pops in right below it and the process continues thru every record.  Thus, records with a lot of info may space 2 and maybe 3 pages, however, some records may only take up, say 3 inches on the page thus giving me 4 records per page.

Your idea was terrific.  I'd like to see your second version.  :)

Here's a DB from Microsoft for an example, but basically it's in the page setup; change the columns to 2 and then move the report spacing entirely to the far left.  

0
Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

 

Author Comment

by:bzspud
ID: 22765077
ah - forgot to include the MS link for the multiple report DB:  http://office.microsoft.com/en-us/access/HA011565341033.aspx?pid=CL100570041033

Gail
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22765159
Then you should have mentioned your Report's unique structure in your original post.
95% of all access report that I have seen here are *not* columnar.

"change the columns to 2 and then move the report spacing entirely to the far left."
I do not have the time to build this report from scratch and then customize it.
I don't want to take the time to make another sample for you to tell me that it is:
  "different that what you have setup."

Can you post a sample of your database here?

JeffCoachman
0
 

Author Comment

by:bzspud
ID: 22765304
Understood and note made of your comment.   I'm sending back your database with the revised reports which are now in a columnar setup.  

My reports are:
a) Single Column Report: tblCustomersFlowers1Per
b) Continous Columnar Report:  rptCustomersFlowersContinue

I added filler data and some interesting images to the first 5 records so it would report similar to mine.  Records 4 & 5 are a good test as they land on the same page in the continuous report item.

I also changed the report name in your code.   I look forward to hearing from you.  

Thank you for your help.
Access-EEQ23831383DetermineWhatP.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22780756
bzspud,

I will work on this tonight

Jeff
0
 

Author Comment

by:bzspud
ID: 22783363
Thanks for the status update; I'll look for something from you in the morning.  If you nail this down, you WILL be the genius!!  Good luck!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22783484
0
 

Author Comment

by:bzspud
ID: 22787151
I'm wondering if this was the wrong file?  It didn't work at all...
G.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 22794098
0
 

Author Comment

by:bzspud
ID: 22815063
Yes!  Thanks!!
0
 

Author Closing Comment

by:bzspud
ID: 31508051
The solution was complete after requesting an updated file.  Thx.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22819474
;-)
0
 
LVL 7

Expert Comment

by:rheitzman
ID: 23031031
Would this order have worked:

SendKeys "{f5}"
SendKeys "27"
SendKeys "{enter}"
DoCmd.OpenReport "MyFlowers", acViewPreview

Counterintuitive but you have to load up the buffer first in some cases.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23035121
rheitzman ,

The issue was that they needed a way to find the "27".

A record is selected and they need to find tha page it starts on.
So the Sendkeys works as long as you know the page number ahead of time.

The need was for a way to determine the Page number.
(Then, perhaps use sendkeys where appropriate)

But yes, I have also had situations where reversing the order of sendkeys works.
;-)

Jeff
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question