file name to hyperlink field 1

[swtrader]

Am putting together a document management db. Briefly, certain info is selected from combo boxes on form (from table: FileNames) and a concatenated string is created getting something similar to this: ABC.SignedLease.DSmith.20110901.FileNo1 which will be saved as a pdf file, OR
DAR.RentIncome.AptComplex23.20110901.FileNo2 which will be saved as a xlsm file.
I have a 'copy string to Clipboard' button on my form, so once the string is created, it is copied to the Clipboard. I then paste it into the file name 'field' under Save As...thus ABC.SignedLease.DSmith.20110901.pdf is the name of the saved file. ALL of the files regardless of type or subject are saved in one central folder. I extract that list of files from the folder via VBA and INSERT them into a table 'myFiles'.

I have a field on the form (that is in the FileNames table) that is set to Hyperlink. THE GOAL/QUESTION: How can I populate that Hyperlink field with the correct Path & FileName, including the file type (pdf, xlsm, doc, docx, etc.) so that the Hyperlink will open the file when clicked? I could have a ListOfFileTypes on the form where I could select one while creating the 'SaveAs' string but that seems too cumbersome.

I have created an Update query that uses a Select query based on what record on the FileName form has the focus...but that only updates one Hyperlink in the FileName table at a time. I would like to loop through the table and update all Hyperlink fields.

an aside...the easiest way to accomplish what I need would probably be to update FileName.HyperlinkField with the full file name as each is extracted from the folder. But I haven't tried to figure that out.

Thanks in advance.

 

[addy]

Well, I do a lot of Document Management DB work. What I usually have is a unique ID for each 'Document Record' and then this unique ID can be related to the filename (to be honest, I don't normally save the filename into a table but use the unique identifier to create a filename so I can create the full file path on the fly as and when needed).

However, I assume that in your table myFiles it references a Document Number or similar so that you know which file is related to which document record you have?

If so, on your form which displays the document data, you can set the hyperlink of the button to the relevant file path using the OnCurrent event.

So, typically I have a button called btnView Document. In my OnCurrent event, I will usually do a validation to check I have a file for that record, if I don't, I'll disable the button, if I do, I'll create (or DLookup the file path in your case) for the specific record I am looking at and will assign this to my btnView.HyperlinkAddress property.

 

[swtrader]

addy,

Thanks for your help. It made me realize that I really didn't have a common ID between the two files -- which made it impossible to run an Update query. I bootstrapped getting the FileNo out of the FileNames file into the myFiles file(which has the files listed from the system folder). What I have built is very cumbersome...so I re-posted asking for help in editing Allen Browne's code. Regards.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.