Can I use a report to do this?

  • Thread starter Thread starter Turtle Turtle
  • Start date Start date
T

Turtle Turtle

I'm creating a database that will print a paper form spanning 1 to
several pages depending on the number of records associated with an
individual. The formatting of this form is very specific. The form
I'm referring to can be seen in this PDF on page 80:

http://www.e-publishing.af.mil/shared/media/epubs/AFI11-202V2.pdf

Basically each person has a series of qualifications that appear
across multiple sheets. If the number of qualifications fits on one
page (that is 7 or less qualifications), then a final supervisor
element goes on the last line (the 8th slot). If the number of
qualifications exceeds 7 elements, then the first page is filled with
7 qualifications, the 8th is filled with a supervisor annotation on
the 8th slot. Any remainder qualifications are overflowed onto a
second page. If the number of qualifications is less than 7, then the
supervisor approval goes on the next slot after the last
qualification. Here's a text example:

=======Page 1==============
Slot 1 : Qualification 1
Slot 2 : Qualification 2
Slot 3 : Qualification 3
Slot 4 : Qualification 4
Slot 5 : Qualification 5
Slot 6 : Qualification 6
Slot 7 : Qualification 7
Slot 8 : Supervisor Approval

=======Page 2==============
Slot 1 : Qualification 1
Slot 2 : Qualification 2
Slot 3 : Supervisor
Approval

Each slot will be filled by data elements extracted from multiple
queries.

I've designed a few forms and reports in Access before but this one
perplexes me because the syntax of the data is slightly different. I
would need to divide the master query into different "pages" that
permits me to view 7 qualifications at a time while always filling up
Slot 8 or the last filled slot + 1 with the supervisor approval.

The last issue is that the formatting of this form needs to be almost
verbatim to the example in the PDF above. I've done some things with
forms and subforms but they tend to use more space than required and
cause formatting irregularities with large margins in subforms. It's
just not easy to design a form like this using sub-forms and nested
fields.

I don't even know where to start on this one. I'd like to get ideas
as to how to do this. Ideally I would store the data in a normalized
database and somehow populate a PDF of the blank form. This is
something I don't know how to do with VBA.

Thanks for any help or advice.
 
I'm creating a database that will print a paper form spanning 1 to
several pages depending on the number of records associated with an
individual.
Click to expand...

Ugly.

I'd be real tempted to use a temporary table with various relevant
fields with an integer field we'll call RecordID. Now these
relevant fields may only be a foreign key or two pointing to other
tables.

Don't forget to put an index on the field called RecordID. Now if you
are running a report for one person at a time this likely isn't really
necessary as there won't be much of a performance hit.

Execute a delete query to clear the temporary table.

Execute an insert query to fill the temporary table with records of
the relevant foreign keys in the appropriate sequence if there is
such. I'd also have an autonumber field in there.

Then using DAO recordset code, based on a query sequenced by the
autonumber field, update the RecordID field for the first seven
records with 1 through 7. If more than 7 records then write the
supervisory record with a RecordID of 8 to the end of the same table.

Now update the 8th record through 14th with an ID of 11 and continue
to 17. Then write another supervisory record and continue.

So the physical order of the records in this temp table with the
RecordID field might be, for example.
1,2,3,4,5,6,7,11,12,13,14,15,16,17,21,22,23,8,18,24.
But a query based on the RecordID field will, of course, sequence them
as desired.

Now create a query based on the RecordID you've just updated along
with the relevant foreign keys joined to your data tables. Run your
report based on that query.

See the Sample Code illustrating looping through a DAO recordset page
at the Access Email FAQ at
http://www.granite.ab.ca/access/email/recordsetloop.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
Ideally I would store the data in a normalized
database and somehow populate a PDF of the blank form. This is
something I don't know how to do with VBA.
Click to expand...

That's a totally different problem. I'd suggest asking that as a
separate question with an appropriate subject. There may not be any
open source solutions.

Here is one possibility - http://www.pdfhacks.com/pdftk/. However
this solution requires that you purchase licenses for commercial use.
And your IT department, especially if military, may be very reluctant
to install outside software.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
That's a totally different problem.   I'd suggest asking that as a
separate question with an appropriate subject.   There may not be any
open source solutions.

Here is one possibility -http://www.pdfhacks.com/pdftk/.   However
this solution requires that you purchase licenses for commercial use.
And your IT department, especially if military, may be very reluctant
to install outside software.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/
Click to expand...

Hi
The form on page 80 is a bit messy but not really that hard. The
report on page 80 is not really like the one in your email. The report
writer will take care of the pagination for you. Even the example on
page 80 is not restricted to 7 qualification per page. it does not go
qualification 1,2,3,4 instructor instead it goes
qualification 1 .(details) ..instructor ,supervisor
qualification 2 ..(details) ...instructor ,supervisor

first build a query with all the required data in it then use the ms
access report builder to create the report based on the query.
select airman, course, instructor, examiner date course .... from
general records group by airman where airman number = XXXXX

It is not obvious how many tables you need to link to get the
required information.

As I said it s a bit messy but not really a problem to make it look
exactly like Page 80. the only real issue is to make the instructor
and approving offer on one column it would be easier to make then each
a column.

I would recommend a better report writer I use Chrystal reports ( Not
free) as it allows you to export the final document to PDF
if you want.

I would be a bit concerned about publishing military documents on the
web not really sure but they tend to take this very serious. Your
problem is a bit to heavy for this group ie if you are being paid to
solve this type of problem you probably need a bit more experience or
an IT specialist to help you.
 
I'd certainly disagree with Derek... few experienced Access developers of my
acquaintance prefer Crystal Reports to Access' own Report function; most of
the ones who do started using Crystal with classic Visual Basic, which had
no Report Writer of its own, were familiar with Crystal, and never put in
the effort to learn Access reporting. Just BTW, there's no "h" in
"Crystal".

And, as for creating PDF reports, that is a built-in feature of the current
version of Access, but I have been printing reports to PDF for years...
simply select a PDF printer in the print dialog (I use the open source PDF
Creator that I obtained from Sourceforge, I'm told that Adobe Acrobat -- not
free, of course -- works well), and there are other PDF creation software
packages available, too. If you don't want to manually select the printer,
you may find some useful code at http://www.lebans.com. I haven't used
Stephen's code, but others have, and posted in newsgroups that it was
useful. But, again, if you have the current version of Access (or perhaps
even one version back), "print to PDF" is a built-in feature.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


That's a totally different problem. I'd suggest asking that as a
separate question with an appropriate subject. There may not be any
open source solutions.

Here is one possibility -http://www.pdfhacks.com/pdftk/. However
this solution requires that you purchase licenses for commercial use.
And your IT department, especially if military, may be very reluctant
to install outside software.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated seehttp://www.autofeupdater.com/
Click to expand...

Hi
The form on page 80 is a bit messy but not really that hard. The
report on page 80 is not really like the one in your email. The report
writer will take care of the pagination for you. Even the example on
page 80 is not restricted to 7 qualification per page. it does not go
qualification 1,2,3,4 instructor instead it goes
qualification 1 .(details) ..instructor ,supervisor
qualification 2 ..(details) ...instructor ,supervisor

first build a query with all the required data in it then use the ms
access report builder to create the report based on the query.
select airman, course, instructor, examiner date course .... from
general records group by airman where airman number = XXXXX

It is not obvious how many tables you need to link to get the
required information.

As I said it s a bit messy but not really a problem to make it look
exactly like Page 80. the only real issue is to make the instructor
and approving offer on one column it would be easier to make then each
a column.

I would recommend a better report writer I use Chrystal reports ( Not
free) as it allows you to export the final document to PDF
if you want.

I would be a bit concerned about publishing military documents on the
web not really sure but they tend to take this very serious. Your
problem is a bit to heavy for this group ie if you are being paid to
solve this type of problem you probably need a bit more experience or
an IT specialist to help you.
 
The form on page 80 is a bit messy but not really that hard. The
report on page 80 is not really like the one in your email. The report
writer will take care of the pagination for you. Even the example on
page 80 is not restricted to 7 qualification per page. it does not go
qualification 1,2,3,4 instructor instead it goes
qualification 1 .(details) ..instructor ,supervisor
qualification 2 ..(details) ...instructor ,supervisor
Click to expand...

Good point. When I review the PDF file I see what you mean. Then
it's just an ordinary report.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
Back
Top