Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
using word ole object on access form
- Thread starter MarkS
- Start date
marks, Create an OLE field in your table. Then you can store any OLE object you wish in that field.
You can drag and drop (or copy and paste) Word documents directly into the table field. When you add data to an OLE field, you can decide if the data is linked or embedded.
Embed: create and place an independent copy of the original.
Link: create a pointer to the original, so that acting on the linked object and acting on the original are really one and the same.
If you want to use a form to enter (and edit) Word documents, include your table in the form's .RecordSource, and use a bound object frame control with its .ControlSource set to the OLE object field you created. (I indicate *bound* because you said you want to store it in a table.) Then you've got all kinds of ways to enter (and edit) the data, as long as the bound object frame control has its .Locked property set to False.
To add a document to your table, you can drag and drop, copy/cut and paste, or enter the data programmatically through VB. Look at the bound object frame control's properties and methods to see how you could do it through VB. Pay particular attention to how .Action is used, and decide if you want to embed the Word document or link to it.
And decide if you want the user to be able to edit the document once it is entered; if so, decide if you want the user to a) edit it in-place within the Access window, b) edit it externally in a full-blown application window for the application that created it (in your case, editing in a normal Word window), or c) get to choose either option.
----
Just to provide some ideas of where you could go from here, and get you thinking about how you might like to implement your "Word document" fields ...
I've done this a lot, and worked with it quite a bit in recent months. In one application, we have a form for entering and reviewing information which includes Word or Excel documents for every record. (Each document contains a picture and callouts, textboxes, captions, etc.) So for new records, a Word or Excel document must be entered, and for existing records, those documents must sometimes be edited. These documents are contained within the database itself, not externally.
My supervisor doesn't want independent files floating around, a) taking up space in the server's directories, b) where anyone can modify or delete them. So he has instead opted to have them taking up space in the database itself, as embedded objects, where form-based security and permissions can handle protecting the data. A Word or Excel document is created for a given record, then (a copy of it is) embedded in the database field via the form, and the original is deleted, leaving the data in the database intact.
When I initially created the form, documents could be entered by either dragging and dropping, or cutting/copying and pasting, the desired file from an Explorer window into the form field. And to edit the document in-place, the user could only-double-click it; editing always took place in a little window on the form with Word/Excel menus blended with the Access menus in the Access window. The form was used this way for a couple of weeks.
Then, when the basics were handled, I made it a little fancier and lot more convenient.
First -- since finding a field with no border whose background matches the form's background can be difficult, and since we wanted to avoid accidental activation of the OLE objects by clicking -- I disabled activation via double-clicking of the object, and created a command button that would activate the objects for editing using VB. I removed the label that said "double-click picture to edit", and set the new command button's caption to "Edit Picture...". And to give the user a choice, rather than force my choice on them, I created a toggle button butted up against the command button to its right. When the toggle button is up, its caption is set to read, "...In Place", and when it is down, its caption is set to read, "...Outside Access"; and just to make the change more apparent to the user, the color is different for each caption also. When clicked, the "Edit Picture..." button checks the value of the toggle button and either opens the document for editing in place within the form window, or externally in a full-blown window for the app that created the document in question. Here's the relevant section of code:
Both of the above verbs open the OLE object for editing, for both Excl and Word. What differs is where the editing takes place.
Next, I added another convenient way for the user to find and add the correct Excel or Word Document to a given record. They can still drag and drop or cut/copy and paste, as before. They also now have a "Get Part Drawing" command button that opens the Open (file) Common Dialog. (I had used the Common Dialog Control in full-blown VB apps before, but it wasn't available with my version of Access 97. However, the common dialog ships with every instance of Windows, and I finally found out how to use it via the APIs, thanks to some information and code I found by searching through Tek-Tips. In fact, I think there are FAQs in one of the Access forums on this topic.) When the user browses to and chooses a file, VB automatically embeds a copy in the field for them. Since all of the documents' filenames contain the part number, I grab the part number from a text box on the form and use it to set the Open dialog's File name field to *<part#>*.* . And for convenience, I provide four filters for the dialog:
This Part (*<part#>*.*) {default filter}
All Files (*.*)
Excel Files (*.xls)
Word Files (*.doc)
Now users can use my interface for very convenient browsing and auto-opening of the documents! (I actually wanted to implement this from nearly the time I started on this form, but I had to wait until I found out how I could use the Common dialog without a Common Dialog *Control*. Then I did, so I implemented my cool original plans![[pipe]](/data/assets/smilies/pipe.gif "[pipe] [pipe]")
.)
So there's what I did most recently with this. If you have any other good ideas or implementations, I'd love to hear about them, and this thread is probably an appropriate place to post them for future readers, too. (I never hesitate to add to even a really old thread, because we encourage each other to look for their question already asked and answered before posting their question; and so someone in the future might come across this question and benefit from any additional information already contained there.)
Good Luck!
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers"
You can drag and drop (or copy and paste) Word documents directly into the table field. When you add data to an OLE field, you can decide if the data is linked or embedded.
Embed: create and place an independent copy of the original.
Link: create a pointer to the original, so that acting on the linked object and acting on the original are really one and the same.
If you want to use a form to enter (and edit) Word documents, include your table in the form's .RecordSource, and use a bound object frame control with its .ControlSource set to the OLE object field you created. (I indicate *bound* because you said you want to store it in a table.) Then you've got all kinds of ways to enter (and edit) the data, as long as the bound object frame control has its .Locked property set to False.
To add a document to your table, you can drag and drop, copy/cut and paste, or enter the data programmatically through VB. Look at the bound object frame control's properties and methods to see how you could do it through VB. Pay particular attention to how .Action is used, and decide if you want to embed the Word document or link to it.
And decide if you want the user to be able to edit the document once it is entered; if so, decide if you want the user to a) edit it in-place within the Access window, b) edit it externally in a full-blown application window for the application that created it (in your case, editing in a normal Word window), or c) get to choose either option.
----
Just to provide some ideas of where you could go from here, and get you thinking about how you might like to implement your "Word document" fields ...
I've done this a lot, and worked with it quite a bit in recent months. In one application, we have a form for entering and reviewing information which includes Word or Excel documents for every record. (Each document contains a picture and callouts, textboxes, captions, etc.) So for new records, a Word or Excel document must be entered, and for existing records, those documents must sometimes be edited. These documents are contained within the database itself, not externally.
My supervisor doesn't want independent files floating around, a) taking up space in the server's directories, b) where anyone can modify or delete them. So he has instead opted to have them taking up space in the database itself, as embedded objects, where form-based security and permissions can handle protecting the data. A Word or Excel document is created for a given record, then (a copy of it is) embedded in the database field via the form, and the original is deleted, leaving the data in the database intact.
When I initially created the form, documents could be entered by either dragging and dropping, or cutting/copying and pasting, the desired file from an Explorer window into the form field. And to edit the document in-place, the user could only-double-click it; editing always took place in a little window on the form with Word/Excel menus blended with the Access menus in the Access window. The form was used this way for a couple of weeks.
Then, when the basics were handled, I made it a little fancier and lot more convenient.
First -- since finding a field with no border whose background matches the form's background can be difficult, and since we wanted to avoid accidental activation of the OLE objects by clicking -- I disabled activation via double-clicking of the object, and created a command button that would activate the objects for editing using VB. I removed the label that said "double-click picture to edit", and set the new command button's caption to "Edit Picture...". And to give the user a choice, rather than force my choice on them, I created a toggle button butted up against the command button to its right. When the toggle button is up, its caption is set to read, "...In Place", and when it is down, its caption is set to read, "...Outside Access"; and just to make the change more apparent to the user, the color is different for each caption also. When clicked, the "Edit Picture..." button checks the value of the toggle button and either opens the document for editing in place within the form window, or externally in a full-blown window for the app that created the document in question. Here's the relevant section of code:
Code:
With Me.tglEditInPlaceOutside
Select Case .Value
Case True
Me.[olePic].Verb = acOLEVerbOpen 'outside access
Case False
Me.[olePic].Verb = acOLEVerbShow 'in place
End Select
End WithNext, I added another convenient way for the user to find and add the correct Excel or Word Document to a given record. They can still drag and drop or cut/copy and paste, as before. They also now have a "Get Part Drawing" command button that opens the Open (file) Common Dialog. (I had used the Common Dialog Control in full-blown VB apps before, but it wasn't available with my version of Access 97. However, the common dialog ships with every instance of Windows, and I finally found out how to use it via the APIs, thanks to some information and code I found by searching through Tek-Tips. In fact, I think there are FAQs in one of the Access forums on this topic.) When the user browses to and chooses a file, VB automatically embeds a copy in the field for them. Since all of the documents' filenames contain the part number, I grab the part number from a text box on the form and use it to set the Open dialog's File name field to *<part#>*.* . And for convenience, I provide four filters for the dialog:
This Part (*<part#>*.*) {default filter}
All Files (*.*)
Excel Files (*.xls)
Word Files (*.doc)
Now users can use my interface for very convenient browsing and auto-opening of the documents! (I actually wanted to implement this from nearly the time I started on this form, but I had to wait until I found out how I could use the Common dialog without a Common Dialog *Control*. Then I did, so I implemented my cool original plans
.)So there's what I did most recently with this. If you have any other good ideas or implementations, I'd love to hear about them, and this thread is probably an appropriate place to post them for future readers, too. (I never hesitate to add to even a really old thread, because we encourage each other to look for their question already asked and answered before posting their question; and so someone in the future might come across this question and benefit from any additional information already contained there.)
Good Luck!
-- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers"
(I haven't mentioned it yet, but my context for all answers here has been and is Access 97.)
You might want either or both of two things, and these two things apply to a *form*, not a *report*. (You asked about a report, markstn, but I typed a bunch before I realised that, and I'm not going to delete it ;-) . But I will also speak of reports at the end. I'll also suggest one other long-shot possibility.)
First thing:
Check the OLE control's .SizeMode property. It's values can be set as follows (from the help file):
----
The SizeMode property uses the following settings:
Clip:
(Default) Displays the object at actual size. If the object is larger than the control, its image is clipped on the right and bottom by the control's borders.
Stretch:
Sizes the object to fill the control. This setting may distort the proportions of the object.
Zoom:
Displays the entire object, resizing it as necessary without distorting the proportions of the object. This setting may leave extra space in the control if the control is resized.
----
I almost always use Zoom for my purposes; for a Word document, I almost certainly would. Remember that it resizes the object, though, so it will make your text smaller or bigger.
Another option would be to use the "Clip" setting to avoid any resizing, attaching scrollbars to the control, and in the scroll bars' code changing the portion of the frame's contents which is visible. This technique is only available if you use a linked object, not embedded. You would change which portion of the frame's contents are visible by using the frame's .SourceItem property. Quoting the help:
-----
"You can set the SourceItem property by specifying data in units recognized by the application supplying the object. For example, when you link to Microsoft Excel, you specify the SourceItem property setting by using a cell or cell-range reference such as R1C1 or R3C4:R9C22 or a named range such as Revenues." [...] "Remarks: The control's OLETypeAllowed property must be set to Linked or Either when you use this property. Use the control's SourceDoc property to specify the file to link."
-----
So in the scroll bars' code, as they move, the .SourceItem property of the frame could be changed in proportion, in effect scrolling the contents of the frame.
I don't know why that won't work with an embedded object, but that seems to be what the help is saying; I don't think you can use "either" and "embedded", and also set the .SourceItem property to any effect. If you try it anyway, definitely share the results (positive or negative) back here, please!
Second thing:
You might be wanting to resize the whole control, which you would do by changing the .Height and .Width properties of the frame, same as any other control. You would have to set code in place to detect the conditions under which you want to do this, which might be the tough part, depending on what you want, behavior-wise.
Strictly Educational Supposition: The reason that the two types of frame controls don't have .CanGrow and .CanShrink is that they have specific properties related to changing the size of contents to fit them, rather than changing their own size to fit the contents (like a growing/shrinking TextBox does).
OKAY, NOW TO TALK ABOUT REPORTS:
marstn wanted to know about how to make an OLE frame behave like a TextBox with .CanGrow and .CanShrink set to Yes, in a report. Can it be done? Probably, yes.
If you don't mind the text growing or shrinking to fit the size of your frame, then set the .SizeMode property of the frame to "Zoom". And you'll need to make the frame big enough that the largest amount of text will still be legible. Of course, a potential side-effect will be fields with brief text printed ridiculously large.
If you don't want to change the size of the text automatically, then you'll want to make use of the .Height and .Width properties mentioned above. You'll probably need to put your code in the Format event handler for the appropriate section(s) of your report. The question is: How can you use code to determine how much vertical space is taken up by text in the frame? I don't know how, off the top of my head. Maybe you can interact with Word through Automation or through its exposed OLE "stuff" to retrieve information like that, but it would probably also require dealing with screen/printer pixel/twip/inch sizes and stuff like that. Maybe you can just figure out how much vertical space a single line would take up and somehow easily count the number of lines then mutiply that number by your constant HEIGHT_OF_A_LINE, and resize the control accordingly.
You can certainly resize controls in the Format event handler; I just tested with a TextBox. I'll leave it to you or others to figure out how to calculate the correct size to use.
Maybe you can do the text editing within an MS-Word TextBox, which does support formatting. Maybe Word exposes its TextBoxes' height through its object model (through VB); if so, you could get Word to check the TextBox's height and pass the value back to Access to resize the frame.
Whatever you do to calculate the appropriate size, make sure it isn't so slow that it makes your report unmanageable.
ANOTHER LONG-SHOT APPROACH:
The basic problem is that Access doesn't have a RichFormatText data type for table fields. If it did, you'd be set. There is a Rich Format TextBox control available for VB out there; it exists, and I *believe* it is called an RFTextBox. I've used it in full-blown Visual Basic apps before (some years ago). But there's that basic problem just mentioned.
If you could find a way to store an RFTextBox in an OLE field, you might have something simpler and more manageable than MS-Word for this purpose. Instantiate an RFTextBox and set the SourceObject to that. On the other hand, it may not be at all possible; it might require an app to act as an OLE server for it to go in one of the frames.
...Just a wild idea...
Hope any of ths helps you out ! -- C Vigil =)
(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers"
You might want either or both of two things, and these two things apply to a *form*, not a *report*. (You asked about a report, markstn, but I typed a bunch before I realised that, and I'm not going to delete it ;-) . But I will also speak of reports at the end. I'll also suggest one other long-shot possibility.)
First thing:
Check the OLE control's .SizeMode property. It's values can be set as follows (from the help file):
----
The SizeMode property uses the following settings:
Clip:
(Default) Displays the object at actual size. If the object is larger than the control, its image is clipped on the right and bottom by the control's borders.
Stretch:
Sizes the object to fill the control. This setting may distort the proportions of the object.
Zoom:
Displays the entire object, resizing it as necessary without distorting the proportions of the object. This setting may leave extra space in the control if the control is resized.
----
I almost always use Zoom for my purposes; for a Word document, I almost certainly would. Remember that it resizes the object, though, so it will make your text smaller or bigger.
Another option would be to use the "Clip" setting to avoid any resizing, attaching scrollbars to the control, and in the scroll bars' code changing the portion of the frame's contents which is visible. This technique is only available if you use a linked object, not embedded. You would change which portion of the frame's contents are visible by using the frame's .SourceItem property. Quoting the help:
-----
"You can set the SourceItem property by specifying data in units recognized by the application supplying the object. For example, when you link to Microsoft Excel, you specify the SourceItem property setting by using a cell or cell-range reference such as R1C1 or R3C4:R9C22 or a named range such as Revenues." [...] "Remarks: The control's OLETypeAllowed property must be set to Linked or Either when you use this property. Use the control's SourceDoc property to specify the file to link."
-----
So in the scroll bars' code, as they move, the .SourceItem property of the frame could be changed in proportion, in effect scrolling the contents of the frame.
I don't know why that won't work with an embedded object, but that seems to be what the help is saying; I don't think you can use "either" and "embedded", and also set the .SourceItem property to any effect. If you try it anyway, definitely share the results (positive or negative) back here, please!
Second thing:
You might be wanting to resize the whole control, which you would do by changing the .Height and .Width properties of the frame, same as any other control. You would have to set code in place to detect the conditions under which you want to do this, which might be the tough part, depending on what you want, behavior-wise.
Strictly Educational Supposition: The reason that the two types of frame controls don't have .CanGrow and .CanShrink is that they have specific properties related to changing the size of contents to fit them, rather than changing their own size to fit the contents (like a growing/shrinking TextBox does).
OKAY, NOW TO TALK ABOUT REPORTS:
marstn wanted to know about how to make an OLE frame behave like a TextBox with .CanGrow and .CanShrink set to Yes, in a report. Can it be done? Probably, yes.
If you don't mind the text growing or shrinking to fit the size of your frame, then set the .SizeMode property of the frame to "Zoom". And you'll need to make the frame big enough that the largest amount of text will still be legible. Of course, a potential side-effect will be fields with brief text printed ridiculously large.
If you don't want to change the size of the text automatically, then you'll want to make use of the .Height and .Width properties mentioned above. You'll probably need to put your code in the Format event handler for the appropriate section(s) of your report. The question is: How can you use code to determine how much vertical space is taken up by text in the frame? I don't know how, off the top of my head. Maybe you can interact with Word through Automation or through its exposed OLE "stuff" to retrieve information like that, but it would probably also require dealing with screen/printer pixel/twip/inch sizes and stuff like that. Maybe you can just figure out how much vertical space a single line would take up and somehow easily count the number of lines then mutiply that number by your constant HEIGHT_OF_A_LINE, and resize the control accordingly.
You can certainly resize controls in the Format event handler; I just tested with a TextBox. I'll leave it to you or others to figure out how to calculate the correct size to use.
Maybe you can do the text editing within an MS-Word TextBox, which does support formatting. Maybe Word exposes its TextBoxes' height through its object model (through VB); if so, you could get Word to check the TextBox's height and pass the value back to Access to resize the frame.
Whatever you do to calculate the appropriate size, make sure it isn't so slow that it makes your report unmanageable.
ANOTHER LONG-SHOT APPROACH:
The basic problem is that Access doesn't have a RichFormatText data type for table fields. If it did, you'd be set. There is a Rich Format TextBox control available for VB out there; it exists, and I *believe* it is called an RFTextBox. I've used it in full-blown Visual Basic apps before (some years ago). But there's that basic problem just mentioned.
If you could find a way to store an RFTextBox in an OLE field, you might have something simpler and more manageable than MS-Word for this purpose. Instantiate an RFTextBox and set the SourceObject to that. On the other hand, it may not be at all possible; it might require an app to act as an OLE server for it to go in one of the frames.
...Just a wild idea...
Hope any of ths helps you out
! -- C Vigil =)(Before becoming a member, I also signed on several posts as
"JustPassingThru" and "QuickieBoy" -- as in "Giving Quick Answers"
- Status