What I'm trying to do:
Within my database, I have a table with approximately 80 entries. Each entry is associated with a Word document and up to 2 Excel documents. The majority of the Word documents are 2-3 pages long.
I also have a form that contains a dropdown list for the primary key on this table. When a user selects a value from that list, I need to have the associated Word document appear in a control on that form, with scroll bars on the side.
Ideally, I would like to have a separate form where the user can select a number from a dropdown, and then can switch between the Word document and the 2 Excel documents using command buttons, and have them display within a control on the form. However, if this proves too difficult, this secondary form can just launch the files in their native applications in a separate window (which I already know how to do).
My limitations:
The back-end of the database is going to be stored on a Sharepoint site, so I cannot use the OLEObject field in my table, because it does not support it.
It doesn't make a huge difference if the files are stored within the database (as Attachments) or externally (as separate files with links stored as text). But I can't just cut & paste the data, because these documents will likely need to be modified in the future, and they need to continue to be stored as Word files.
If I do store them as attachments, Sharepoint will only allow a single file for each record in the table (even though Access allows multiple attachments as a multi-value field). I have two workarounds, one is a separate table with three records tied to each primary key value, and another field distinguishing what type of file they are (so I can retrieve the right file using an SQL query). Alternatively, I can just store the Word document as the single attachment, and link to the external Excel files (which is slightly more confusing, but doable).
The problem with attachments is that, as far as I can tell, the "attachment" control on the form does not actually display the attachment, unless it's an image. From my research online, it seems that for non-image files it only displays an icon representing the file, which you can double-click to open. I want the contents of the document to display.
For a while it seemed like using an Unbound Object Field and linking to the separate file would work, and it almost does. The problem is that it only returns the first page of the document, and almost all the documents are 2-3 pages.
My thoughts:
I've considered creating a temporary table through VBA code, creating a record containing an OLEObject field, and importing the file data to display it in a Bound Object Field. However, I don't know if that will work with Sharepoint, or if it will drastically slow down performance every time someone changes their selection.
I've also considered converting the file data to a BLOB, and trying to import and display it that way. I don't know a whole lot about this, or if it's a good idea or not.
Is it possible to convert on-the-fly the contents of a Word document to, say, HTML so it can be displayed in a RichText Memo textbox? This might also be a solution, as long as everything displayed correctly. It probably wouldn't work as well with the Excel files, but the Word file is my main concern.
Thank you very much for taking the time to read this and give me your feedback, if this is in the wrong forum please move it as necessary.
Within my database, I have a table with approximately 80 entries. Each entry is associated with a Word document and up to 2 Excel documents. The majority of the Word documents are 2-3 pages long.
I also have a form that contains a dropdown list for the primary key on this table. When a user selects a value from that list, I need to have the associated Word document appear in a control on that form, with scroll bars on the side.
Ideally, I would like to have a separate form where the user can select a number from a dropdown, and then can switch between the Word document and the 2 Excel documents using command buttons, and have them display within a control on the form. However, if this proves too difficult, this secondary form can just launch the files in their native applications in a separate window (which I already know how to do).
My limitations:
The back-end of the database is going to be stored on a Sharepoint site, so I cannot use the OLEObject field in my table, because it does not support it.
It doesn't make a huge difference if the files are stored within the database (as Attachments) or externally (as separate files with links stored as text). But I can't just cut & paste the data, because these documents will likely need to be modified in the future, and they need to continue to be stored as Word files.
If I do store them as attachments, Sharepoint will only allow a single file for each record in the table (even though Access allows multiple attachments as a multi-value field). I have two workarounds, one is a separate table with three records tied to each primary key value, and another field distinguishing what type of file they are (so I can retrieve the right file using an SQL query). Alternatively, I can just store the Word document as the single attachment, and link to the external Excel files (which is slightly more confusing, but doable).
The problem with attachments is that, as far as I can tell, the "attachment" control on the form does not actually display the attachment, unless it's an image. From my research online, it seems that for non-image files it only displays an icon representing the file, which you can double-click to open. I want the contents of the document to display.
For a while it seemed like using an Unbound Object Field and linking to the separate file would work, and it almost does. The problem is that it only returns the first page of the document, and almost all the documents are 2-3 pages.
My thoughts:
I've considered creating a temporary table through VBA code, creating a record containing an OLEObject field, and importing the file data to display it in a Bound Object Field. However, I don't know if that will work with Sharepoint, or if it will drastically slow down performance every time someone changes their selection.
I've also considered converting the file data to a BLOB, and trying to import and display it that way. I don't know a whole lot about this, or if it's a good idea or not.
Is it possible to convert on-the-fly the contents of a Word document to, say, HTML so it can be displayed in a RichText Memo textbox? This might also be a solution, as long as everything displayed correctly. It probably wouldn't work as well with the Excel files, but the Word file is my main concern.
Thank you very much for taking the time to read this and give me your feedback, if this is in the wrong forum please move it as necessary.
