I have a function (in excel) that opens a new record in an existing access
file and inserts certain fields based on cells from that excell sheet. I am
trying to add a hyperlink to the actual excel file so that when clicked in
the access file, the orignal excel file is opened. I am not familiar with the
syntax for adding the hyperlink property. I can get the text to show up but
not the actual link address (which is the same for now, I will change the
text later). Any help on setting this property (and any other properties)
would be greatly appreciated. here is a copy of the code I am currently using
which adds the correct text to the link field (also the correct path to use
for the link property):

Path="J:\My Documents\"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=S:\Time Clock\NJC.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Jobs", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Date") = Now()
.Fields("Company") = Range("D4")
If Range("E2") 10000 Then
.Fields("Description") = "Accessories for APS Pickup
veh# " & Range("E2").Value & "."
Else
.Fields("Description") = "Accessories for APS Pickup
veh# 0" & Range("E2").Value & "."
End If
.Fields("HourlyCost") = 60
.Fields("HourlyPrice") = 80
.Fields("Status") = "C"
.Fields("EstimateTot") = Range("F25").Value
If Range("E2") 10000 Then
.Fields("Link") = Path & Left(Range("E2").Value, 2)
& " Series\" & Range("E2").Value & " Pickup " & Range("B1") & ".xls"
Else
.Fields("Link") = Path & "0" &
Left(Range("E2").Value, 1) & " Series\0" & Range("E2").Value & " Pickup " &
Range("B1") & ".xls"
End If
' add more fields if necessary...
.Update ' stores the new record
End With
Range("E1").Value = rs.Fields("JobNumber")
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Thanks in advance for any insight that you can give me on this.

Here's what I did, please let me know if someone has an easier way.
I created a button on the form in access with this code behind the click
procedu

Private Sub OrderForm_Click()
On Error GoTo Err_cmdExcel_Click
Dim Address As String
Me.Link.SetFocus
Address = Me.Link.Text
Dim oApp As Object

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True 'Only XL 97 supports UserControl Property
oApp.Workbooks.Open Filename:=Address
oApp.Visible = True
On Error Resume Next
oApp.UserControl = True
Me.OrderForm.SetFocus
Exit_cmdExcel_Click:
Exit Sub
Err_cmdExcel_Click:
MsgBox Err.Description
Resume Exit_cmdExcel_Click
End Sub

So-the test is transfered using the existing code and is accessed by the
button to open the correct excel file.

"JCanyoneer" wrote:

I have a function (in excel) that opens a new record in an existing access
file and inserts certain fields based on cells from that excell sheet. I am
trying to add a hyperlink to the actual excel file so that when clicked in
the access file, the orignal excel file is opened. I am not familiar with the
syntax for adding the hyperlink property. I can get the text to show up but
not the actual link address (which is the same for now, I will change the
text later). Any help on setting this property (and any other properties)
would be greatly appreciated. here is a copy of the code I am currently using
which adds the correct text to the link field (also the correct path to use
for the link property):

Path="J:\My Documents\"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=S:\Time Clock\NJC.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Jobs", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Date") = Now()
.Fields("Company") = Range("D4")
If Range("E2") 10000 Then
.Fields("Description") = "Accessories for APS Pickup
veh# " & Range("E2").Value & "."
Else
.Fields("Description") = "Accessories for APS Pickup
veh# 0" & Range("E2").Value & "."
End If
.Fields("HourlyCost") = 60
.Fields("HourlyPrice") = 80
.Fields("Status") = "C"
.Fields("EstimateTot") = Range("F25").Value
If Range("E2") 10000 Then
.Fields("Link") = Path & Left(Range("E2").Value, 2)
& " Series\" & Range("E2").Value & " Pickup " & Range("B1") & ".xls"
Else
.Fields("Link") = Path & "0" &
Left(Range("E2").Value, 1) & " Series\0" & Range("E2").Value & " Pickup " &
Range("B1") & ".xls"
End If
' add more fields if necessary...
.Update ' stores the new record
End With
Range("E1").Value = rs.Fields("JobNumber")
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Thanks in advance for any insight that you can give me on this.