Re: Format report, fixed height for entries that must shrink?
- From: "John Spencer" <spencer@xxxxxxxxx>
- Date: Mon, 29 Oct 2007 16:09:15 -0400
Your first problem seems to be that your data structure is wrong.
You should really have a table that records the item donated, the person
donating, and the quantity.
With that structure it would straight forward to list the number of items
and the amounts donated without hiding/showing a lot of fields. Plus if you
add another donation category you won't have to rewrite all your forms,
codes, queries, and reports.
You need a table for
Donors
ItemsDonatedByDonor
DonationItemType
If you have to live with the current structure then you would need to use
some vba to pull the information into one field. Would you care to post
your data structure. I am guessing you have something like
DonorId
DonorName
Cups
Plates
Saucers
Apples
And then you enter the number of cups in the Cups field, the number of
Apples in the apples field. IF that is the case, you could copy and paste
the following expression (with the needed changes) into a VBA module and
then call it from your query.
Field: ListOfItems: strListItems([DonationID])
Assumptions:
-- Number field that identifies the donation
-- Field names are the names of the items to be donated
-- The fields are number fields and contain
Public Function strListItems(DonationID) As String
Dim strSQL As String
Dim rstAny As DAO.Recordseet
Dim I As Long
Dim strReturn As String
strSQL = "SELECT Cups, Plates, Saucers, Apples " & _
" FROM YourDonationTable " & _
" WHERE DonationID= " & DonationID
Set rstAny = CurrentDb().OpenRecordset(strSQL)
If rstAny.RecordCount > 0 Then
For I = 0 To rstAny.Fields.Count - 1
If Nz(rstAny.Fields(I), 0) > 0 Then
strReturn = strReturn & vbCrLf & rstAny.Fields(I).Name & ": " &
rstAny.Fields(I)
End If
Next I
strReturn = Mid(strReturn, Len(vbCrLf) + 1)
End If
strListItems = strReturn
End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
"Darwinlady" <Darwinlady@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E8F52C25-9798-40F4-A061-BD98865D6370@xxxxxxxxxxxxxxxx
Please forgive the length, I will try to condense this explanation as much
as
possible while still conveying the essentials. I am trying to create a
report which prints 2-to-a-page "receipts," essentially, to hand out to
customers when they donate items to our organization. I have been
struggling
for several weeks to find a way to format this report so that each entry
is
exactly 1/2-page, so I can print out a daily report (~20-30 pages) and cut
the stack in half with a paper cutter. The report pulls from a query
which
pulls from 2 tables, customer info and donation info (detail section.)
Formatting the detail section was the first problem. When the order is
taken from a list of about 55 items, a quantity is typed in for which
items
they are donating, and I only want those entries with a quantity to show
up
on the report, the other ones to disappear and the whole section to
collapse
so there's not empty space. Ideally, the list would be arranged in 4
columns
for a cleaner layout, but I couldn't figure out how to make items from all
4
columns align up to the top, squeezing out the empty space left by the
items
not showing. I solved this by designing the report with all 55 items in
one
long column to the left, aligning them all to the top, and setting
everything
to Grow/Shrink, then using "Me!item_Label.Visible = Not IsNull(Me!item) on
format (one for each item in the list).
Problem is, this solution allowed each entry to shrink up and not take up
extra space, but I can't set the section to a fixed height because the
single-column list of items in the design view cannot fit within that
height.
Between the header and footer for each entry and all the donation info, I
absolutely cannot get the design of the report to fit within a 5.5"
height,
even though each receipt in print view will always be less because most of
that column disappears (there's usually only quantities entered for a
handful
of items on the list.)
I even tried setting the report to landscape so the 2-to-a-page are side
by
side, hoping some kind of column formatting would work, but I can't get
that
to go either.
Is it possible to either set a fixed height for the print layout only, or
modify my 55-item list into 4 columns so that they tidy themselves up
properly when I'm only printing a receipt with a few items? (The
horizontal
shrink seems to be the hangup.) I built this project because I thought I
had
a decent knowledge of access, but unfortunately I don't know VB and did
not
think I would need it... so please forgive me in advance but I must
request
that if your advice involves coding that you are very explicit in your
instructions. (The code above that I used, I found on these forums and
used
trial and error to figure out how to make it work for myself and where to
put
it.)
Thank you so, so much and, again, my apoligies for the length.
.
- Follow-Ups:
- Re: Format report, fixed height for entries that must shrink?
- From: Darwinlady
- Re: Format report, fixed height for entries that must shrink?
- Prev by Date: Re: Wrapped Cells
- Next by Date: Re: Create Report from Database
- Previous by thread: Re: configurare outlook
- Next by thread: Re: Format report, fixed height for entries that must shrink?
- Index(es):
Relevant Pages
|
