Hyperlink and Database design

[Guest]

I have a database that list various invoice numbers from various vendor #. I
want to link the invoice number to a scanned copy of the invoice located on
the server. The invoices are stored in indivual folders based on the vendor
#. Is there a way to automaticly hyperlink to the proper folder and invoice?
If the Invoice doesn't exisist it notifys the user.

 

[Allen Browne]

So how does the database know the folder name to use for each client?

You could store the path (with trailing backslash) in a text field for each
client. Then store the document name (pathless) in a text field with the
document. Use the Click (or DblClick) event of the text box to concatenate
the 2 values and FollowHyperlink to open the document.

The code you need to get the path for the client record is here:
http://www.mvps.org/access/api/api0002.htm

And you can then set the initial folder correctly, and get the file name
with this code:
http://www.mvps.org/access/api/api0001.htm

Using Text fields like that generally works better than using a Hyperlink
field.

 

[Guest]

I was hoping that one field would be the Client and another field would be
the invoice, the client # would specify a piticular folder on my server and
the invoice # would specify the invoice.pdf. I will check out your links and
see if they will do what I am needing to do.

 

[Allen Browne]

Over time, one client may have more than one invoice.
You therefore need a table of clients, and a related table of invoices.

Because one invoice can contain multiple items, you probably need a related
table for the InvoiceDetails as well. For an example, open the Northwind
sample database, and see the Customer, Order and OrderDetail tables.