UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Working With Word Docx As Attachment, Access 2013    
 
   
EdNerd
post Dec 6 2017, 05:04 PM
Post#1



Posts: 774
Joined: 23-May 11



I'm feeling like I'm missing something really simple here, but I'm gonna ask any way -- dunce.gif

I need a Word docx to go with my database. A screen shot of the doc's contents is included in the opening form. But someone else, who is not versed in the inner workings of Access (like how to open to see the Navigation panel and get to the table with the attachment) is going to be managing this db - including changing any wording on the doc.

In my searching, I have not yet uncovered an easy way to open this docx file as a Word document, edit it, and then save it back as the attachment.
And to allow the user to take a screen shot of the changed doc and SaveAs a certain file name, which will also upload as an attachment and show up in the form.

Am I thinking this should be simple, but it's really complex?
Or am I just missing something simple?

Ed
Go to the top of the page
 
theDBguy
post Dec 6 2017, 05:14 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Ed,

Does the Word document contain graphics or font styles making it necessary to use an image to display the contents? Or, would displaying the contents as plain Text be enough?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Dec 6 2017, 07:24 PM
Post#3



Posts: 774
Joined: 23-May 11



Yes, there's some font styling and colors in the text to emphasize certain critical instructions. That's why I take a screen shot (using the Windows Snip tool) after making any changes.
Go to the top of the page
 
EdNerd
post Dec 7 2017, 10:45 AM
Post#4



Posts: 774
Joined: 23-May 11



Well, more searching says it is a bit more complicated than I hoped for, but not unmanageable.
Found code I could tweak to open the docx in Word. Seems kinda convoluted.

You'd think that if they created an attachment field, they would have also created a Field.Attachment object with attributes that can be read.

Okay - next phase: uploading the new docx and screenshot attachment to the correct tables and fields.
I will post code when I'm done.

Ed
Go to the top of the page
 
theDBguy
post Dec 7 2017, 10:49 AM
Post#5


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Ed,

Good luck! By the way, an Attachment field has three properties you can read: FileData, FileName, and FileType

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
theDBguy
post Dec 7 2017, 10:56 AM
Post#6


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Ed,

Also, you might want to take a look at this old thread.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
theDBguy
post Dec 7 2017, 11:03 AM
Post#7


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


And this one too!

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Dec 7 2017, 05:14 PM
Post#8



Posts: 774
Joined: 23-May 11



I cobbled together some code to open the file as Word doc to allow editing. So that's finished.

To upload the saved doc and the new screen shot as attachments, though, I actually had to delete and recreate the tables. Trying to delete the attachment was not going well - making new tables was easier.

I have one more step in this process that I haven't yet figured out, even after all day in Google and the VBE! iconfused.gif crazy.gif I can't get the new image to upload into the Image control on my form and stick. I can step through my code and watch the old one vanish and the new image insert. And then I save and close the form. But when I re-open, it's the old image!?! pullhair.gif I even tried to delete the image control and set a new one, but found I couldn't do that outside of Design view - and that was another can of worms!

Maybe I'm going about it the wrong way??

I'm assuming that selecting a new graphic, using the File Open dialog to get the full path and name, and using that as the Picture property value should change the image displayed in the control. I'm also assuming that most standard picture formats should work, including JPG, GIF, and PNG.

Is the image control the best one to use here? Since the image is stored as an attachment, can I use a control to show the image based on [Table].[field] ControlSource, and that will automatically change when I update the image (the table, field, and image will all have the same names as before, and this attachment image is the only item in the only field in the only record in this table)?

Ed
Go to the top of the page
 
theDBguy
post Dec 7 2017, 05:24 PM
Post#9


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Ed,

What happens if you use an Attachment control rather than an Image control? Try different formats for the Attachment control starting with a PNG image.

If it doesn't work, I can tell you how I display an attached image in one of my dbs.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Dec 7 2017, 06:19 PM
Post#10



Posts: 774
Joined: 23-May 11



I inserted an Attachment control - but can't readily see how to point it to the attachment in a table. I can use the Expression Builder to navigate to the table and field, but the Control Source property field stays blank?? I'll dig around a bit more on the Attachment control and see what comes up.

And of course, any help you are willing to share is always greatly appreciated. notworthy.gif

Update:
I did create a bound form using the wizard that did display the attachment. So I suppose I could always add this into the main form as a subform? And since the source path (table > field > name) will always be the same, it should always show the latest upload??

Ed
This post has been edited by EdNerd: Dec 7 2017, 06:34 PM
Go to the top of the page
 
EdNerd
post Dec 8 2017, 09:28 AM
Post#11



Posts: 774
Joined: 23-May 11



Okay - got it licked!! Many thanks to the leadings of theDBguy!! notworthy.gif thanks.gif

First problem: opening the Word doc stored as an attachment. In my project, I have a table with one record with one field, and the only thing in it is this Word docx as an attachment. I created a form with two buttons - the first opens the Word doc for editing.
CODE
Option Compare Database
'
Dim db As dao.Database
Dim rs As dao.Recordset
Dim fld As dao.Field

Dim strTemp As String 'Temp file directory
Dim strFName As String 'File name
Dim strFPath As String 'File path
'

Private Sub btnOpenDoc_Click()

strTemp = Environ("temp")
If Right(strTemp, 1) <> "\" Then _
  strTemp = strTemp & "\"

'Debug.Print strTemp

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from tblWordDoc")
rs.MoveFirst

strFName = "Opening.docx"
strFPath = strTemp & strFName

'Debug.Print strFPath

On Error Resume Next
  Kill strFPath
On Error GoTo 0

Set fld = rs.Fields("OpeningDoc")

OpenAttachment rs, fld.Name, strTemp

rs.Close
Set rs = Nothing

End Sub

Function OpenAttachment(ByRef rs2 As dao.Recordset, _
          ByVal strFld As String, ByVal strPath As String) As String

Dim rstChild As dao.Recordset2
Dim fldAttach As dao.Field2
Dim strFile As String

Set rstChild = rs2.Fields("OpeningDoc").Value

Set fldAttach = rstChild.Fields("FileData")

fldAttach.SaveToFile strFPath

rstChild.Close

VBA.Shell "Explorer.exe " & Chr(34) & strFPath & Chr(34), vbNormalFocus

End Function


Next issue: After editing the Word doc, the user makes a "screen shot" using the Windows Snipping tool. This screen shot is also saved as an attachment (again, one table, one record, one field). I also needed to upload the edited doc as an attachment. Finding and replacing the attachments in the existing tables proved to be more difficult than just deleting and re-creating the tables and then adding the items as new attachments.
CODE
Private Sub btnSavePic_Click()

Dim rsAttach As dao.Recordset2
Dim tblDef As dao.TableDef

Set db = CurrentDb
Set rs = Nothing

strTemp = Environ("temp")
If Right(strTemp, 1) <> "\" Then _
  strTemp = strTemp & "\"

'Must close main form
DoCmd.Close acForm, "frmUpdate"

'First, delete the current attachments
'** Delete WordDoc table
DoCmd.DeleteObject acTable, "tblWordDoc"
'** Create new WordDoc table
Set tblDef = db.CreateTableDef("tblWordDoc")
With tblDef
  .Fields.Append .CreateField("OpeningDoc", dbAttachment)
End With

db.TableDefs.Append tblDef
db.TableDefs.Refresh

Set tblDef = Nothing

'** Delete ScreenShot table
DoCmd.DeleteObject acTable, "tblScreenShot"
'** Create new ScreenShot table
Set tblDef = db.CreateTableDef("tblScreenShot")
With tblDef
  .Fields.Append .CreateField("ScreenShot", dbAttachment)
End With

db.TableDefs.Append tblDef
db.TableDefs.Refresh

Set tblDef = Nothing

'Second, upload the new attachments
'** Load Word doc
strFName = "Opening.docx"
strFPath = strTemp & strFName

Set rs = db.OpenRecordset("tblWordDoc", dbOpenDynaset)

rs.AddNew
Set rsAttach = rs.Fields("OpeningDoc").Value
rsAttach.AddNew
rsAttach.Fields("FileData").LoadFromFile strFPath
rsAttach.Update
rs.Update

Set rsAttach = Nothing
Set rs = Nothing

'** Load PNG file
strFPath = SelectFile
'Debug.Print strFName
'Stop
'Stop

Set rs = db.OpenRecordset("tblScreenShot", dbOpenDynaset)

rs.AddNew
Set rsAttach = rs.Fields("ScreenShot").Value
rsAttach.AddNew
rsAttach.Fields("FileData").LoadFromFile strFPath
rsAttach.Update
rs.Update

Set rsAttach = Nothing
Set rs = Nothing

DoCmd.Close acForm, Me.Name, acSaveYes

End Sub

Function SelectFile()

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
  .AllowMultiSelect = False
  .Title = "Please select saved Snip"
  If .Show = True Then
    SelectFile = .SelectedItems(1)
  Else
    Exit Function
  End If
End With

Set fd = Nothing

End Function


The final head scratcher was updating the image in the main form (frmUpdate) after uploading the new screen shot. I resolved this by creating a form bound to the table containing the screen shot attachment and inserting that into frmUpdate as a subform.

So -- now that I've gotten the instruction "page" taken care of, I can get down to the *real* work this project is supposed to accomplish! compute.gif

Ed
uarulez2.gif
Go to the top of the page
 
theDBguy
post Dec 8 2017, 11:05 AM
Post#12


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Ed,

Congratulations! I will say the same advise as I did in the other thread. Please consider submitting a demo in the Code Archive.

Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Dec 11 2017, 11:22 AM
Post#13



Posts: 774
Joined: 23-May 11



I've got a stripped-down and sanitized version completed.
Do I just post it as a zipped file in the Code Archive forum?
Or does it need to be sent elsewhere for approval first?

Ed
Go to the top of the page
 
theDBguy
post Dec 11 2017, 11:27 AM
Post#14


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Ed,

Yes, you can just create a new topic in the Code Archive forum with an explanation of your demo and then attach it there. You won't see your submission until it's approved.

Someone will review it, test it, and then approve it or ask for clarifications, if needed.

Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Dec 11 2017, 04:59 PM
Post#15



Posts: 774
Joined: 23-May 11



Done!!

uarulez2.gif
Ed
Go to the top of the page
 
theDBguy
post Dec 12 2017, 11:23 AM
Post#16


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Ed,

Thanks. I took a quick look and have two issues you might want to check out.

1. I use Access 2010 and got an error for a missing Excel reference. I don't think your demo needs a reference to Excel, correct? If so, you can probably remove it.

2. I can click the "Open" button to modify the attached Word document, but I don't see a way to save it back into the attachment field.

Just curious... what was the problem with deleting the attachment and replacing it? Deleting and recreating the table might be too expensive as a process.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Dec 13 2017, 12:23 PM
Post#17



Posts: 774
Joined: 23-May 11



Thanks for looking!!

1. No, the demo does not need an Excel reference. I'll remove that.

2. There should be two buttons on the second form: the top one opens the Word doc for editing, and the bottom one uploads the edited doc and the new screen shot as attachments.

Everything I tried to simply delete the existing attachment and upload a new one failed. Most of the time the code sailed right on through - but when I checked the actual attachment, it was the same one. That's when I tried deleting the table, and then creating a new table and uploading the new attachment.

Ed
Go to the top of the page
 
theDBguy
post Dec 13 2017, 01:29 PM
Post#18


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi Ed,

Thanks for the clarification.

I guess I just got confused by the button's label of "Upload Screen Shot" and thought it was only for the screen image file. May I suggest re-labeling the button with something more like "Update Attachments" to reduce or avoid any confusion.

You can upload the new version here, and I can replace the one in the Code Archive.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Dec 13 2017, 01:36 PM
Post#19



Posts: 774
Joined: 23-May 11



Done!!

Thank you!!
Attached File(s)
Attached File  AttachmentsDemo.zip ( 753.68K )Number of downloads: 2
 
Go to the top of the page
 
theDBguy
post Dec 13 2017, 01:50 PM
Post#20


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Cool, thanks!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 12:06 AM