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/share...FI11-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.

On Thu, 14 Jul 2011 20:26:07 -0700 (PDT), Turtle Turtle
<(E-Mail Removed)> wrote:

>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.

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/

On Thu, 14 Jul 2011 20:26:07 -0700 (PDT), Turtle Turtle
<(E-Mail Removed)> wrote:

>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.

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/

On Jul 16, 3:49*pm, Tony Toews <tto...@telusplanet.net> wrote:
> On Thu, 14 Jul 2011 20:26:07 -0700 (PDT), Turtle Turtle
>
> <turtle1...@gmail.com> wrote:
> >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.
>
> 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/

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


"(E-Mail Removed)" <(E-Mail Removed)> wrote in message
news:912570f5-85a6-4574-8a71-(E-Mail Removed)...
On Jul 16, 3:49 pm, Tony Toews <tto...@telusplanet.net> wrote:
> On Thu, 14 Jul 2011 20:26:07 -0700 (PDT), Turtle Turtle
>
> <turtle1...@gmail.com> wrote:
> >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.
>
> 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/

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.

On Mon, 18 Jul 2011 09:09:45 -0700 (PDT), "(E-Mail Removed)"
<(E-Mail Removed)> wrote:

>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

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/