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: 764
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,196
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: 764
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: 764
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,196
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,196
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,196
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: 764
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,196
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: 764
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: 764
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,196
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
 


Custom Search
RSSSearch   Top   Lo-Fi    10th December 2017 - 06:59 PM