Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-20
16
Medium Priority
?
584 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

916 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