By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,009 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Creating a Report which will Print Envelopes

P: 8
Dear Group

I have the database set up, and the query, which lists my name and addresses. Each time I attempt to create a report I fail to create it
right.

I want a simple layout, a graphic which is the return address, and the
names, title, organization, address 1, address 2 etc.

I think I don't want the header, detail and footer lines, which keep coming
up on my report.

Also, when I preview my report for printing, it's saying that the width of my
report is wider than the page it is printing on.

Other reports I have made, the return address, and the mailing address come
up on consecutive pages, when they need to be all laid out on the same page.

So I have made alot of attempts, and I'm pretty stuck. Suffice to say, there is something I am not seeing, or doing, that is the key that I am missing.

Thanks for your assistance guys!

Lisa
Sep 11 '08 #1
Share this Question
Share on Google+
16 Replies


P: 8
Hi there! I'm supposed to tell you.

I am using MS Access 2003, and OS Windows XP.

I think that is all you need.

Lisa
Sep 11 '08 #2

P: 8
Now I have the report, and when I click on
preview, a box asks me to put the
parameter value there.

What is the parameter value?

The print preview shows two of the parts
of the address, only the title, and the
suburb. But it omits to show the first
name, last name, title, etc.

I don't know why.
Sep 11 '08 #3

NeoPa
Expert Mod 15k+
P: 31,347
Lisa,

Let's try to do this again from scratch, but separating the problems clearly and logically.

Your explanations of what's wrong are a little bit disorganised (Referring to named items without any sort of explanation is confusing to say the least).

What say we start again from scratch, indicating clearly your problems in terms that mean something generally (to me) rather simply with reference to other items only you now of.

If it helps I don't see any of the problems you've hinted at being insuperable at all. With some clear questions I'm sure we can find simple answers for you.

Welcome to Bytes!
Sep 11 '08 #4

P: 8
Thankyou Neo Pa

I'll start another post, and describe what I need.

Sorry that my first post was so confusing. I guess I'm pretty confused as well!

Cheers! Lisa
Sep 17 '08 #5

NeoPa
Expert Mod 15k+
P: 31,347
Feel free to continue in this thread Lisa.

If you feel you'd prefer to post a separate thread, I will not notice it easily unless you post a link to it in here.
Sep 17 '08 #6

P: 8
Thankyou Neo Pah

I thought to start a new thread so that we could delete what I have written so far, but of course people will follow it down.

So I will attempt to be more methodical in my description this time.

__________________________________________________ _______________

THE TABLE

I have many tables in Microsoft Access, and I am using version 2003. The table that I am currently working with is called "invitations", and is a list of names and addresses. There is a yes no field called "print" and I tick which
addresses I want to print on the envelopes.

THE QUERY

I have set up a query called "Mail". This query is linked to "Invitations" table, and itemizes the following fields:-

First Name
Last Name
Title
Organization Name
Address
City
State
Postal Code
Print Yes No

This query selects which addresses by including each one which is ticked, and that means "yes" in the query design.

I have noticed that I don't know how to change the table that this query is linked to. I would find it useful to be able to look at and edit the table that a query is linked to. From what I can see however, I need to create a new query for every table that I want to print from.

This is not so important to me, because I seem to be competent in designing the query. This query seems to work the way I want it to.

THE REPORT

The object is to create a report (the first of many) that will layout the addresses on envelopes for printing. There is a piece of artwork to go in the left top corner, which has the return address on it. And the address needs to be laid out in the middle of the envelope. The page size needs to be set to DL, and the printer might need to be told that I am printing DL.

I haven't successfully created this report, and I would like you to provide some instructions please (smile).

One problem I come up with is that all the reports have three sections, top, middle and footer. I think I want it set up without this. Just the middle maybe.

One thing that I am doing right, is that the report is linking to the correct table. However, on some attempts the addresses were not complete, I was only getting two lines of the address, instead of the whole layout.

I checked the spelling of the fields to make sure they were spelt correctly, and they were.

I seem also to be having an issue with the page size that Access is laying the addresses on, although I can't understand why. I have selected DL envelope, but when I go to print, it says that the text is bigger than the page I am printing on, and won't let me print it.

I'm guessing that's enough. Suffice to say, I need instructions to write a report. Thankyou for allowing me to write here, and thanks to anyone who chooses to answer my query!

Yours very sincerely


Lisa Dean ** Link Removed **
Sep 18 '08 #7

P: 8
QUERY

I was just looking at the Query Desigm. and I found that the second line,
gives the table that the query relates to. That suggests to me, that I can change the table that the query relates to, by changing the names in this
way.

I can try this.

It also suggests that if there is a use, more than one table could be linked
to the query, by these boxes. Possibly.
Sep 18 '08 #8

P: 8
REPORT

When I click on "design" while the report is highlighted the following words come up:-

"The Section Width is greater than the page width, and there are no items in the additional space, so some pages may be blank".

Actually every second page is blank. The page size is set wrongly, but I can't find the place to change it.

I thought also to give you the address layout instructions I have used.

= Trim ((FIRST NAME) " & (LAST NAME))

= Trim ((ORGANIZATION NAME))

= Trim ((ADDRESS 1))

= Trim ((ADDRESS 2))

= Trim ((CITY) & " & (STATE)) & " " (POSTAL CODE))


I don't have the square bracket on my keyboard so I have given it to you as a round bracket.

I hope this is clearer for you to understand now, and that you can see my problem.

Thanking you most sincerely

Lisa Dean
Sep 18 '08 #9

NeoPa
Expert Mod 15k+
P: 31,347
...
I have noticed that I don't know how to change the table that this query is linked to. I would find it useful to be able to look at and edit the table that a query is linked to. From what I can see however, I need to create a new query for every table that I want to print from.

This is not so important to me, because I seem to be competent in designing the query. This query seems to work the way I want it to.
...
Lisa Dean ** Link Removed **
Before I start I must just bring to your attention the rules we have about what is , and isn't, allowed in signatures, in technical threads particularly (Signature Guidelines).

Moving swiftly on because I want to focus more on how much easier this appears to be to work with :) (although I haven't read through all of it yet). What I will try to do is pick up individual points and respond as I get to them.

First up Query Source modification.

As the source table is a fundamental part of a query and how it is executed (including optimisation), changing this within a QueryDef (a saved Access query) is possible, but not without causing the optimisations to be redone. It also leaves the Query in a less predictable state. You will not know next time what you will find in it.

Queries in Access are associated with SQL instruction strings (a simple example for illustration) :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [Invitations]
This would be the equivalent of opening the table itself.

To change the SQL of a QueryDef (note my earlier warning on this) is simply a matter of setting the .SQL property of the QueryDef to a new value.

What is possibly more appropriate in your circumstances, is for the report object to be designed with your QueryDef as the Record Source, but for some code to take the SQL from there at run-time and doctor it so that it runs from the table you want. The report then gets its Record Source changed to the new SQL string. For that run, it now uses that temporary SQL instead of the QueryDef.
Sep 18 '08 #10

NeoPa
Expert Mod 15k+
P: 31,347
...
THE REPORT

The object is to create a report (the first of many) that will layout the addresses on envelopes for printing. There is a piece of artwork to go in the left top corner, which has the return address on it. And the address needs to be laid out in the middle of the envelope. The page size needs to be set to DL, and the printer might need to be told that I am printing DL.

I haven't successfully created this report, and I would like you to provide some instructions please (smile).

One problem I come up with is that all the reports have three sections, top, middle and footer. I think I want it set up without this. Just the middle maybe.
...
I'm afraid I'm not the best to help with laying out reports. My skills lie away from the artistic/design side of things. I suggest you get on with it (it's fairly intuitive) and post if you come aross any specific problems.

Reports have the possibility of :
  • Report Headers/Footers.
  • Page Headers/Footers.
  • Section Headers/Footers.
  • Details.
The appearance of the Report & Page Header/Footers (in your design) are controlled by selections from your Report View menu. The Section Headers/Footers are controlled by Sorting and Grouping in your Report View menu. What controls whether or not they effect the report itself, is whether or not they have any Height. If this is set to 0 (They cannot contain anything for this) then it will not show.
Sep 18 '08 #11

NeoPa
Expert Mod 15k+
P: 31,347
...
I checked the spelling of the fields to make sure they were spelt correctly, and they were.

I seem also to be having an issue with the page size that Access is laying the addresses on, although I can't understand why. I have selected DL envelope, but when I go to print, it says that the text is bigger than the page I am printing on, and won't let me print it.
...
This is hard to help with without the actual subject to hand. There are just so many things involved. I suggest that you take the time to look through some of the available properties of all the items you're using. Even some of the items that are not immediately pertinent will be beneficial to look at for later. With some guesswork I may highlight some of the items that are effecting you, but don't simply rely on these.

Look out for the Can Grow & Can shrink properties of the TextBox controls. In your case I suggest you want neither to be set true. Consider separate TextBoxes for the separate lines of the address, rather than one that can grow/shrink.

Your margins may be skewing your understanding of available widths for you. Pay a lot of attention to these, and everything found at File / Page Setup.
Sep 18 '08 #12

NeoPa
Expert Mod 15k+
P: 31,347
QUERY

I was just looking at the Query Desigm. and I found that the second line,
gives the table that the query relates to. That suggests to me, that I can change the table that the query relates to, by changing the names in this
way.

I can try this.

It also suggests that if there is a use, more than one table could be linked
to the query, by these boxes. Possibly.
You can also see the SQL of a query from Design mode by selecting View / SQL. This is actually editable there too (Be very careful with this).

As a very basic technique for changing a FROM table in a QueryDef :
  • Open QueryDef in Design view.
  • Switch to SQL view (Alt-V, Q).
  • Copy Contents to Clipboard (Ctrl-C).
  • Open (or switch to) text editor (Notepad or other).
  • Paste contents of Clipboard (Ctrl-V).
  • Use Find and Replace facility to change all occurrences of old filename to new filename.
  • Copy updated contents to Clipboard.
  • Switch back the the QueryDef (still SQL view).
  • Paste contents of Clipboard.
  • Switch view back to Design (Alt-V, D).
  • You now have a new query under the old name (not saved yet though).
Sep 18 '08 #13

NeoPa
Expert Mod 15k+
P: 31,347
REPORT

When I click on "design" while the report is highlighted the following words come up:-

"The Section Width is greater than the page width, and there are no items in the additional space, so some pages may be blank".

Actually every second page is blank. The page size is set wrongly, but I can't find the place to change it.
...
From Design mode select the Detail section and press Alt-Enter to open (or switch to) the properties pane. If at the top, in the (generally) blue bar, it does NOT say "Report", then you need to go back and retry selecting the Report itself. A Grey, unused area of the report should select the report if clicked on.

One of the Report properties is Width. If you set this to 0, it will reset itself to the lowest value consistent with all your existing report contents. Try this and see if that fixes your problem.
Sep 18 '08 #14

NeoPa
Expert Mod 15k+
P: 31,347
...
I don't have the square bracket on my keyboard so I have given it to you as a round bracket.
...
You don't find them immediately to the right of your "P" key?

If really not available, they can generally be produced, assuming you have a numeric keypad, by holding down the Alt key while typing 9 then 1 (for [) or 9 then 3 (for ]). Remember, the Alt key goes down before the 9 and doesn't come up until after the 1 (or 3) is released.

PS. I think this enough on this one thread for today ;)
Sep 18 '08 #15

P: 8
Dear Neo Pa

Firstly thankyou for all your great suggestions. My apologies for two things, firstly the inappropriate signature. I should know better. And secondly, for taking so long to reply. The reason for the delay is that I want to avoid clogging up the thread with unhelpful messages, to make it more useful for someone who might be browsing to find the answer to a similar problem.

TEXT IS BIGGER THAN THE PAGE SIZE.

Now, regarding the Query Source modification. I haven't succeeded in doing this the way you are telling me, although I do seem to have succeeded in doing it in my own way, by copying and pasting the query and simply changing the name of the database that the query is connected to.

The instructions you have given me, are not clear, though I do not criticize what you have written, only that my own experience with the program and the language is basic, and I will read it over many more times, and eventually get it.

I'm very interested in this SQL and I found the link to this under the drop down menu at the top of my Access program.

Somewhere, in the next post you explained about the Page Setup, under the file menu. And I realized also that I could alter the size of the page, in design view. So I changed the size of the design view detail to 220 times 110 which is the DL size. The margin currently allocated is 5ml, or ten ml, and the program subtracts this from the page size, and calculates the size of the document.

Page Setup has been very helpful and I spent hours in there, fiddling with the page setup to try to solve my problem. (I have been going into the program daily to try). Theh page setup kept changing what I set it to. I found this annoying, and suspected this was not meant to happen. Then when I finally thought I had the setttings correct, and went to print, the program froze.

So I decided to download a new version of Access without the bug. Because it was clear to me that there was a bug in the program. I managed to get a copy of Access 2002 from a friend, and have downloaded this, and it is working better.

The persistant problem is that I get a message each time I go to print preview, that the image size is bigger than the page size.

TEXT IS BIGGER THAN THE PAGE SIZE.

I have also been reading a beautifully written manual about Microsoft Access, published by Microsoft Press. I managed to source two books from a TAFE library.

Now, a work associate has given me the tip of what to do with this problem. As follows:-

"With regard to the error message "address box too big " or whatever it says...try reducing the font size of the address text.
It is very fiddly and might take a bit of tweaking.
You can reduce the text size and / or reduce the size of the bounding box that contains the address text
He says it is a real pain and might take a while to get right
try one or both and see what happens
I would suggest making it very small to begin with, testing to see if it prints, then increasing the font size in increments while testing the print, then you will arrive at the maximum setting
Once you have it, record the settings somewhere for future reference

This makes sense to me, and I'm going to try this next. I'll let you know how I go. I never would have thought of this as the problem! How interesting!

I hope you feel good about helping me Neo Pa, I am so very grateful for all your attempts at explaining the ins and outs of the program. I haven't finished studying all your notes, and hope to learn more and to become more clear about the program, over a period of time. Unfortunately, my understanding is basic, and so, it will take some time for me to get my head around all these special words and understand the right place to click, and why I am clicking there. lol.

Okay, and I hope this post helps someone else who is having this problem.

TEXT IS BIGGER THAN THE PAGE SIZE.

I think I am getting very close.

Regards

Lisa Dean
Entertainer
Sep 23 '08 #16

NeoPa
Expert Mod 15k+
P: 31,347
What a wonderful post. It's always gratifying to help, and the effort you've gone to to express your situation so clearly is equally good to see :) It's always a pleasure dealing with someone, such as yourself, who displays such good manners.

As to your text size issue, the solution propsed would surprise me, but try it anyway. I'm not so fully experienced that I can't still be surprised sometimes, and I'm not a great user of reports. Most of my users want the basic info provided rather than swish layouts.

PS. If you would like a more detailed explanation of any of the earlier posts you merely have to ask. I would be happier knowing you have grasped a technique for the future than thinking you were almost there, but my explanation wasn't quite clear enough.

Updating SQL code in a text editor can be an extremely powerful technique. One additional point I would make on that though, is that Access sometimes mucks it up (just with subqueries I think). Access QueryDefs Mis-save Subquery SQL should help with that if you need it though.
Sep 23 '08 #17

Post your reply

Sign in to post your reply or Sign up for a free account.