Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Q and A _ Working With Word Docx As Attachment

Posted by: EdNerd Dec 6 2017, 05:04 PM

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

Posted by: theDBguy Dec 6 2017, 05:14 PM

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?

Posted by: EdNerd Dec 6 2017, 07:24 PM

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.

Posted by: EdNerd Dec 7 2017, 10:45 AM

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

Posted by: theDBguy Dec 7 2017, 10:49 AM

Hi Ed,

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

Cheers!

Posted by: theDBguy Dec 7 2017, 10:56 AM

Hi Ed,

Also, you might want to take a look at http://www.UtterAccess.com/forum/index.php?showtopic=2008703.

Hope it helps...

Posted by: theDBguy Dec 7 2017, 11:03 AM

And http://www.UtterAccess.com/forum/index.php?showtopic=2031019

Cheers!

Posted by: EdNerd Dec 7 2017, 05:14 PM

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

Posted by: theDBguy Dec 7 2017, 05:24 PM

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.

Posted by: EdNerd Dec 7 2017, 06:19 PM

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

Posted by: EdNerd Dec 8 2017, 09:28 AM

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

Posted by: theDBguy Dec 8 2017, 11:05 AM

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.

Posted by: EdNerd Dec 11 2017, 11:22 AM

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

Posted by: theDBguy Dec 11 2017, 11:27 AM

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!

Posted by: EdNerd Dec 11 2017, 04:59 PM

Done!!

uarulez2.gif
Ed