Solved

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

Posted on 2008-10-20
16
571 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now