How can we automatically view a PFD file which references a DB record? 2

[Sherman6789]

We have a database which records each payment made periodically by defendants per court order. Copies of the court order and check/money order payments are kept in folders for future reference.

We would like to begin scanning the court orders and payment checks/money orders into separate PDF format files which will be kept in a special folder on the computer server.

Our supervisor would like for us then to be able to click a button and a copy of the court order or individual check payment will appear on the screen for viewing or printing.

I think that each Court Order should be scanned into separate files with a file name such as: CO-23, CO-24, etc. where the number matches the record number of the defendant.
Also, the checks/money orders should be scanned separately and named something like: Pmt-23-02, Pmt-23-07, etc. where the first number (23) is the defendant's id record number and the (-02)or (-07) represents the record number for the given payment.

What is the best procedure to follow to be able to automatically call up a PDF file of a record while in the Microsoft database program?

 

[Remou]

I suggest a documents table with:

DocumentID
DocumentLocationShelf 'Physical location
DocumentLocationPath 'For example \Docs\Whatever\Doc1.pdf
DocumentType 'Payment, Court Order etc

And a table for people/document:

PersonID
DocumentID

This way an employee can get a list of documents for their clients, and a client form will show a list of documents. Double-clicking the location could run a little code:

FollowHyperLink "G:\Store\" & Me.txtDocumentLocation

http://lessthandot.com

 

[Sherman6789]

Thanks Remou,

Will this be clickable? Can the user, after finding the selected record in the database, click on the name and the PDF file will appear on the screen?

 

[Remou]

You will need to create a form and add some code to an event, I suggest the Double-Click event. FollowHyperlink will open a document (most things, in fact) with the registered program, so yes, with very little effort, this can be clickable. I use a very similar set up, for a very similar purpose.

http://lessthandot.com

 

[RoyVidar]

I am a fan of using the ShellExecute API for things like this. There's a handy wrapper function for it here

http://www.mvps.org/access/api/api0018.htm

So, with controls bound to fields having the full path and name of the file(s) on your form, some button code could simply call this wrapper with:

[tt]call fhandlefile(me!txtThePathAndName.Value, WIN_MAX)[/tt]

which should give it maximized.

Roy-Vidar

 

[Sherman6789]

Thanks to Remou and Roy Vidar for your suggestions and patience.

I have been studying the solutions and suggestions and I've verified that I don't know as much as I would like. Please tell me where I should put that code.

Also, I think that I must try to explain what we need a little better so that you might think of an easier alternative.

1. I have a database which lists persons who have been ordered to pay restitution into a fund on a monthly basis. This database contains their unique ID, address, probation officer, name of court, state, etc.

2. I also have a subfile (subform) which lists the payments as they are received. The two files are linked together. The subfile contains the payer's unique ID from the above file, the amount paid, the date on the check or money order, bank name, entered date, etc.

3. When the payer's information is displayed on the screen, a form listing all payment received in order date order is also shown. It can be updated, printed or just view.

4. A copy of the original court order and a photo copy of all checks are saved in a folder.

5. As of last month, the court order and all associated checks are scanned separtately into PDF files and saved in a special directory.

6. When a records is shown on the screen for viewing or updating, we would like to be able to press a button or click on the name of a file or link and a copy of the selected court order or a copy of any selected check will appear on the screen for viewing or printing. This will eliminate the need to look for the file in the folder, except on special occassions.

7. SInce I started this thread, I have learned a little about something called "OLE" which apparently can use *.jpg or *.bmp or *.pdf files for viewing through a form. I am not sure if this if correct. Originally, I thought that I wanted the file name to show on the record and a click of the name would open Adobe and show the check or letter on the screen while Access was still running. Now, I am wondering if it would be better to have the button open a form and the photo would show on the screen similar to a program which shows product photos or employee ID photos on the screen.

I'm am sure that I've either said enough or confused the issue.
Any help that you can give will be appreciated. I need to know where to put the code that you suggest.

Thanks.

WRS