Insert or update filepath of Word Doc back to Access

[lgray]

Doug,
This will probably sound convoluted, but, is there a VBA function that will
allow you to update/insert the filepath into a Hyperlink table field in
access on Close of the Word document. The purpose is to create a direct path
in Access to a modified Word file without having to go through the hoops of
establishing the Hyperlink in Access. The end-users of this database are not
database savvy and could easily be intimidated by this approach, so I am
trying to automate the file path upon save of their Word Documents.

 

[Doug Robbins - Word MVP]

Among other things, the following code inserts information from Word into an
Access database. It could be modified to do what you want

Public db As DAO.Database
Public rs As DAO.Recordset
Public numrecs As Long
Public FileName As String
Public ContractID As Long
Public Sequence As Long
Public mdata As Variant
Public Reference As String
'Created by Doug Robbins
'SkypeName dougmvp
'SkypeIn Number +61 7 3102 3802
'Mobile Number +61 417 714 723



Private Sub cmdContinue_Click()

'Open the database - modify the path as required Set db =
OpenDatabase("P:\9_Client_GAC\9.62_Administration\Data
Management\Templates\CR_GAC_be.mdb")
'Retrieve the recordset for the selected Contract Set rs =
db.OpenRecordset("Select * from tblMSPO") 'Determine the number of records
With rs
If .RecordCount > 0 Then
.MoveLast
numrecs = .RecordCount
.MoveFirst
Else
numrecs = 0
End If
End With
'The sequence number for document being generated is one more than the
number of records Sequence = numrecs + 1 'Set the FileName for the Document
FileName = "P:\9_Client_GAC\9.50_Procurement_&_Contracts\Miscellaneous
Supply Purchase Orders\MSPO-" & Format(Sequence, "000") & ".doc"
'store the information about this document in the database With rs
.AddNew
!Sequence = Sequence
!Supplier = TxtSupplier.Text
!Address1 = txtAddress1.Text
!Address2 = txtAddress2.Text
!Attention = txtAttention.Text
!Email = txtEmail.Text
!phone = txtPhone.Text
!Shipto = txtShipto.Text
!Supply = txtSupply.Text
!CER = txtCER.Text
!Currency = txtCurrency.Text
!Amount = txtAmount.Text
.Update
End With
'Transfer the information from the form into the document With
ActiveDocument
If optSupplies.Value = True Then
.Variables("varType").Value = "SUPPLIES"
.Variables("varvendtype").Value = "Supplier"
Else
.Variables("varType").Value = "SERVICES"
.Variables("varvendtype").Value = "Consultant"
End If
.Variables("varSequence").Value = Format(Sequence, "000")
.Variables("varSupplier").Value = TxtSupplier.Text
.Variables("varAddress1").Value = txtAddress1.Text
.Variables("varAddress2").Value = txtAddress2.Text
If Len(txtAttention.Text) > 0 Then
.Variables("varAttention").Value = txtAttention.Text
Else
.Variables("varAttention").Value = " "
End If
If Len(txtEmail.Text) > 0 Then
.Variables("varemail").Value = txtEmail.Text
Else
.Variables("varemail").Value = " "
End If
If Len(txtPhone.Text) > 0 Then
.Variables("varphone").Value = txtPhone.Text
Else
.Variables("varphone").Value = " "
End If
.Variables("varShipTo").Value = txtShipto.Text
.Variables("varSupply").Value = txtSupply.Text
If txtCER.Text <> "" Then
.Variables("varCER").Value = txtCER.Text
Else
.Variables("varCER").Value = " "
End If
.Variables("varCurrency").Value = txtCurrency.Text
.Variables("varAmount").Value = Format(txtAmount.Text, "#,###.00")
.Range.Fields.Update
.SaveAs FileName
End With
Me.hide
End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[lgray]

Thank you. I will work on implementation. Does this need to be in Word
Form format, or can it be done in a MailMerge Doc as well. And if it could
be done in the MailMerge, do I just create a Field type to hold the data. I
am much more well versed in Access/VBA than in Word, and know the
functionality/capabilities are very different

 

[Doug Robbins - Word MVP]

Now it is getting convoluted.

Can you advise exactly what the whole process if to do?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP