Insert or update filepath of Word Doc back to Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lgray - 28 May 2008 21:38 GMT

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.

Signature

Linda

Reply to this Message

Doug Robbins - Word MVP - 29 May 2008 01:13 GMT

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

Signature

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

> Doug,
> This will probably sound convoluted, but, is there a VBA function that
[quoted text clipped - 8 lines]
> 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.

Reply to this Message

lgray - 29 May 2008 18:03 GMT

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

Signature

Linda

> Among other things, the following code inserts information from Word into an
> Access database. It could be modified to do what you want
[quoted text clipped - 103 lines]
> > 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.

Reply to this Message

Doug Robbins - Word MVP - 29 May 2008 23:46 GMT

Now it is getting convoluted.

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

Signature

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

> 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
[quoted text clipped - 118 lines]
>> > am
>> > trying to automate the file path upon save of their Word Documents.

Reply to this Message