File / Attachment Management within Form

[Rheged85]

Good afternoon, All

I'm currently working a supplier database, and one of the things we need to do as part of that is collect information about the suppliers' insurance & accreditations which will need to be audited. I have put together a form wherein the user populates fields with key info e.g., type of policy, expiry date etc. Each policy / accreditation gets its own child-record linked to the supplier's master record.

I have also been asked to keep copies of the documents that the suppliers send over, but I'm trying to avoid actually storing the files as attachments within the database to avoid bloating over time. So to achieve this I would like to do as follows: -

User drags / drops file into the form for a given accreditation / insurance policy child-record, which then uploads the file to a SharePoint folder, renames files to avoid overwriting, storing the hyperlink for the file in the child-record & displaying it in the form in case anyone wants to check the file. From the form, I would also like the option of the User being able to click a button next to a given file's hyperlink to delete the file, e.g., in case redundant or mistakenly uploaded.

I have a Plan B in case this is not achievable, but would like your thoughts - please be merciful as I'm still quite new to MS Access and databases generally!

For info, the database front-end / back-end will be split with the front-end being kept locally on users laptops and the back-end kept in SharePoint (we're only a small-ish company of around 50 employees at present).

Thanks!

 

[GaP42]

I'll leave out drag and drop - don't know how that is enabled.
I utilise a form for motor vehicle insurance - fake data displayed:

The subform is used because multiple documents may relate to the insurance - and has a feature to allow linking between document records is possible (thru setting of a parent/primary document)
The Changelink button opens the file dialog to enable selection of a document. The location is stored in location / link which on double click will open the document using followhyperlink. Selection of a file can be followed by copy and store in a defined location, with (re)naming- perhaps appending the date to the file name or other manipulation of the filename string.
Deletion of a hyperlink is one action - as delete the record. Deleting the file is another, which may be undertaken if it is a copy. The sequence of actions needs attention.

 

[Gasman]

Not even sure you can do drag and drop? I would probably open Explorer with FileDialog to allow the user to select relevant file, then put it whereever needed.

 

[theDBguy]

I seem to recall you can do drag and drop with hyperlink fields.

 

[mike60smart]

Good afternoon, All

I'm currently working a supplier database, and one of the things we need to do as part of that is collect information about the suppliers' insurance & accreditations which will need to be audited. I have put together a form wherein the user populates fields with key info e.g., type of policy, expiry date etc. Each policy / accreditation gets its own child-record linked to the supplier's master record.

I have also been asked to keep copies of the documents that the suppliers send over, but I'm trying to avoid actually storing the files as attachments within the database to avoid bloating over time. So to achieve this I would like to do as follows: -

User drags / drops file into the form for a given accreditation / insurance policy child-record, which then uploads the file to a SharePoint folder, renames files to avoid overwriting, storing the hyperlink for the file in the child-record & displaying it in the form in case anyone wants to check the file. From the form, I would also like the option of the User being able to click a button next to a given file's hyperlink to delete the file, e.g., in case redundant or mistakenly uploaded.

I have a Plan B in case this is not achievable, but would like your thoughts - please be merciful as I'm still quite new to MS Access and databases generally!

For info, the database front-end / back-end will be split with the front-end being kept locally on users laptops and the back-end kept in SharePoint (we're only a small-ish company of around 50 employees at present).

Thanks!

This is one method