Link to home
Start Free TrialLog in
Avatar of Amine400

asked on

Microsoft access report design

I have a big list of products(15000), i want to print this list in an microsoft access report with a thumb index(tabs in the right edge of each page containing the first letter of the product's name)
Avatar of Robert Sherman
Robert Sherman
Flag of United States of America image

So you just want 26 thumbs (A-Z) that indicate the first letter of the first product on a page?  I'm assuming the list has more than one product per page (unless you're printing out a 15,000 page report).

Do you need the thumbs to be printed at the immediate edge of the page?   If so, the first thing you need to determine is whether your printer/print driver will print all the way to the edge of the page.   Otherwise, you'll have tabs at whatever distance from the edge your printer's margin is.

The second issue is how to place the tabs on the page (since the tab will span multiple "detail" items in the report).   This will require a bit of code in the OnPrint event to position a graphical tab item... if this all sounds right so far, I could drum up some sample code...
If you could post a sample your db with a few records (excluding any private info) along with the report you made, we can make some modifications and see if that's what you're after.

Avatar of Amine400


-my report should contain 500 pages( 15000 products with 30 lines per page)
-26 tabs [A-Z]
-the position of the tab change when the first letter changeUser generated image
Just want some clarification.
You say you want 26 tabs but your illustration shows 1 tab for every 2 letters.
I'm assuming that the first record with a new letter starts on a new page, if so, will you have anything for the letter X tab?  
If you don't have any entries for the letter X or any other letter, will there be a need to still show those tabs with no records?
- In every page, there is only 1 tab
-  when all the entries in the page starts by the letter "A", The tab should show the letter "A".
- When the first letter change to "B", the tab should descend and show the letter "B".
- When the first letter change to "C", the tab should descend more and show the letter "C"......
-If there is no entries with first letter x, there is no need to show a tab with the letter "x"
Take a look at this sample db I made and open up the report in Print Preview.   I use 2 lines of code in the page header of the report to take care of the side tabs.
     'Prevent moving to the next printing location on the page
    Me.MoveLayout = False
    'position the letter tabs 500 twips apart
    Me.txtLetter.Top = (Asc(Me.txtLetter) - 64) * 500

Open in new window

Since I use the Page Header section for the alphabetical side tabs, the Group Letter section is used for the label headers.  The RepeatSection property for this Group needs to be set to "Yes" so that the labels repeat on every page.  In addition, the ForceNewPage property needs to be set to "Before Section" in order to start each letter on its own page.

- The tabs should be in the detail section of the report.
- If "500" in the code is the number of page, i'm asking if your code will work when the number of pages increase ( more products)
- what is " 64" ?
thumb index already exist in microsoft outlook.User generated image User generated image
- The tabs should be in the detail section of the report.
Did you view the report in Page Preview?  The tabs show up on the side of the report starting from the top and all the way down to the bottom just like the image you posted.  The actual control for the tab cannot be in the detail section otherwise it would show up on every record.

500 is the number of twips which is the unit of measurement used in Access forms and reports.  There are 1440 twips in an inch so the tabs are .347 of an inch apart.  

The ASCII value of each letter is used to calculate the position of the tab.  The ASCII for "A" is 65, "B" is 66, "C" is 67, etc. so the calculation comes up like so:
A:  (65 - 64) * 500 ===> (1) * 500 ===> 500
B:  (66 - 64) * 500 ===> (2) * 500 ===> 1000

I'm not sure why you mentioned Microsoft Outlook.  You said you wanted this in an Access report.  Here's a snapshot of some pages from the db I posted.  As you can see, the tabs move down the side:
User generated image
Great Job , Thank you
The problem now is that I can't apply this example in my db.
can you please show me , step by step , how did you creat your report from the table , and how did you add the tab in the header page.
I accept a video or snapshot.
I was under the assumption you already had a report that you wanted to add tabs to.  If that's the case, you just need to follow the instructions I posted earlier and use my sample db to help you.  If you don't know how to create a report, then use the Wizard on the Ribbon bar.  You just need to attempt to try this yourself and then post where you're having problems.  
User generated image
Excellent solution IrogSinta
Thanks Pat :-)
May I ask why you only assigned a grade of B?  What information was missing for you to complete the task?  If you ran into other problems, all you needed to do was say so as I mentioned earlier.

the problem is that i can't creat the tab (txtletter) in my report
i need a step by step tutorial for this, or i need to make an effort ))
i'm a beginner
So what have you tried and how far where you able to get to?  Did you use the Wizard to create your report as I suggested?
Yes i did , everything work fine , but I wanna show my report's title and my table's labels in all pages
Then move the title into the section that has the column headers and shrink the page header to nothing.
Avatar of IrogSinta
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@PatHartman : thank you
@IrogSinta : thank you
and now , you deserve the grade "A" and "Favorite Expert"
Just remember...
"You will find that help will always be given at EE to those who ask for it"
                                                                                                    ~ Albus Dumbledore
Short, fast and perfect solution.