Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 8th, 2004, 08:55 AM
Authorized User
 
Join Date: Aug 2004
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default First and Last Record in Report Header

I am try to get a report to display the first date or record and the last date or record in the header. I can not seem to get the last record to appear correctly. I can get the first and last date on page in the footer by refrencing a textbox in the header. I did this by writing some code in the on format of the footer, but I can't get it to work in the header, can anyone help.

Thanks

Reply With Quote
  #2 (permalink)  
Old September 8th, 2004, 12:10 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Instead of referring to a textbox in the report, put an UNbound textbox in the header and in its ON FORMAT event, use something like this (change the names to match your REAL names):

Me.txtFirstDate = DMIN("[dtmDate]", "tblMyTable")
Me.txtLastDate = DMAX("[dtmDate]", "tblMyTable")

Where dtmDate is your date field and tblMyTable is your table (presumeably the same one as the recordsource for your report). You can replace the table reference to the name of a query if the recordsource of the report is a query instead, i.e.

Me.txtFirstDate = DMIN("[dtmDate]", "qryMyQuery")
Me.txtLastDate = DMAX("[dtmDate]", "qryMyQuery")


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #3 (permalink)  
Old September 8th, 2004, 01:40 PM
Authorized User
 
Join Date: Aug 2004
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the help. However, I some 100 records and have allowed only 30 records to appear per page. Therefore, I wanted to show the first and last date of that page. Here in lies the problem. I can get the very last date, like how you have explained, but can not get it do show last date on page one, and then page two and so on. Any more ideas??!! Thanks for the suggestions

Reply With Quote
  #4 (permalink)  
Old September 8th, 2004, 01:44 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

OooOoOoo... I see... like on a dictionary's pages, the top says something like "Aardvark to About" and then "About to Abrupt", etc. except you want that with dates. You want to place the range that the dates cover for each of your pages in your report.

OK... let me think about that one and I'll get back to you... In the meantime, someone else may answer more quickly.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #5 (permalink)  
Old September 8th, 2004, 02:24 PM
Authorized User
 
Join Date: Aug 2004
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Your discription is exactly what I am trying to do. Any ideas would be great. Thanks

Reply With Quote
  #6 (permalink)  
Old September 10th, 2004, 03:54 AM
Friend of Wrox
 
Join Date: Jul 2004
Location: , , India.
Posts: 345
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to gokul_blr Send a message via Yahoo to gokul_blr
Default

Keep a page count and the no. of record displayed in the page no. 1 1-30 (initial Back is disabled & next is enabled if the max. records are >30 otherwise disabled and go on)

When user hits next page increate the current page view to 2 (internally) and 31-60
based on the max record the enable/disable back & next buttons

<<Back>> 31-60 <<Next>>

or
<<Prev>> Page 2 <<Next>>


Gokul




Reply With Quote
  #7 (permalink)  
Old September 10th, 2004, 08:37 AM
Authorized User
 
Join Date: Aug 2004
Location: , , .
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Gokul this sounds good but could you expand a little and next and back, I don't know really what you are talking about.
Thansk CC16


Reply With Quote
  #8 (permalink)  
Old September 10th, 2004, 09:37 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

I have it working but the text you seek, i.e. "Range X to Y", is only obtainable in the footer, not the header section of the report.

Place an unbound textbox in the detail section of your report. Call it, say, txtCounter. In the properties box, set its control source to

=1

set its Running Sum property to

OVER ALL

and set its VISIBLE property to NO.

In the page header section of the report, place a second unbound textbox. Call it, say, txtFirst. It has no control source and its Running Sum property is set to NO. Set its VISIBLE property to NO.

In the page header section's ON FORMAT event, place this code

Me.txtFirst = Me.txtCounter

Now place a third textbox in the report's page footer section. Call that one, say, txtLast. This one has no control source and its Running Sum property is set to NO, also. Set its VISIBLE property to YES.

On the page footer's ON FORMAT event, place this code

Me.txtLast = Me.txtFirst & " to " & Me.txtCounter

What happens is this. When the report opens, the page header textbox is set to = the counter = 1. The counter counts all the records for that page, say 30. Then the footer kicks in and the page footer textbox gets the page header textbox (which was set to 1) to the current count (30). So you see "1 to 30".

For page two, the header textbox kicks in again and gets the current count (31). The detail section counter increments again. The footer kicks in and gets the header count (31) to the current count again (say, 54). The footer text box now reads "31 to 54".

And so on...


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Report Header not printing lryckman Access VBA 3 November 26th, 2007 09:58 AM
Remove Header and Trailer record??? gzura SQL Server DTS 2 April 27th, 2005 12:48 PM
Report Header JohnBoyUK BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 October 28th, 2004 07:10 AM
Getting to the recordset in report header badgolfer Access 3 June 16th, 2004 11:35 AM
Report header anees81 Crystal Reports 0 September 25th, 2003 06:10 AM



All times are GMT -4. The time now is 07:39 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.