How to Design PDF Repository to Link with DB Form

[MEB]

Hello. I am designing a database that manages construction permit
applications. As part of the process to go digital, our intern pulls the
paper file and inputs the relevant information into the appropriate fields on
the form. Each paper file also has a schematic diagram. What I want to do is
to scan these into as PDFs and then have them related to the correct
application in the database and accessible via the form. I'm envisioning on
the form a field where a unique PDF ID or even filename is inputted.

What is the best way to do this? Is it to create a separate folder to dump
all the PDFs and then use a PDF ID field for each application record? How
would I get it to point to the correct path and how would I launch it from
the form? How do I actually program this into the database - both table
fields and forms?

Many thanks.

 

[MEB]

After a bit more thought, here's what I would ideally like. As each
application may have more than one associated PDF, I think perhaps that the
best thing would be to have a subform on the main application form. The
subform could then be used to keep multiple records (each record being a PDF)
for that specification application record.

Furthermore, it would be nice for the subform to have a GetFile dialog
button or something for the user to browse the folder where all the PDFs are
kept and select the proper one.

Does this sound good or is there a better way?

 

[MEB]

OK Lastly (!) (I don't see a place to delete posts, so it's turning into a
blog )

I've been digging up codes and what not and found the basic module code to
create a command button that opens the standard Browse window and puts the
chosen filename and path into a text box as referenced here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;303066

Two issues with this:
1. The filename is stored in the form, apparently. If possible, I would like
this stored in a table instead (I have a separate table called PDF which is
related to the Application table) -- a field in that table called "Path" or
something. I think it may allow for better data management down the road.

2. On the form, I would also like the user to be able to actually launch the
PDF (i.e., the filename that is sitting there in the forms' text box) as well
as browse to input the filename.

So perhaps the ideal scenario is a subform (main form is Application) which
is solely for linking to these external PDFs. The subform is setup in a table
format and for each record, there can be multiple entries. Each entry has a
Browse command button, a text field containing the path and filename (or just
filename) and a button to launch the PDF in Acrobat Reader.

I guess it also may be possible to have just one Browse and/or Launch button
but that is what I'm seeing.

So how can this be done? Many thanks.

 

[AndrewDB]

Hi MEB. I have a similar type of system that I use and I have found that by
creating a field as a hyperlink which is used to save the file information
works best. The user just clicks the hyperlink to open the file.