Open Access form from excel hyperlink

This might be a 2 parter-not sure.
I need to use code to set the value in a cell (eg. 101012) to be a hyperlink
which will open an Access file called NewJobs.mdb and open the form Jobs and
goto record 101012 (field name is JobNumber). Say the hyperlinked cell is
cell D2 which is also where the original 101012 would be.

Can anyone help me with this?

Since I have gotten no replies, here is what I can up with. Instead of using
a hyperlink, I inserted a button with this code behind it:

Global oApp As Object
Sub OpenAccess()

Dim LPath As String
Dim LCategoryID As Long

If Range("D1") > 17000 Then
'Path to Access database
LPath = "S:\Time Clock\NJC.mdb"

'Open Access and make visible
Set oApp = CreateObject("Access.Application")
oApp.Visible = True

'Open Access database as defined by LPath variable
oApp.OpenCurrentDatabase LPath

'Open form called Categories filtering by CategoryID
LCategoryID = Range("D1").Value
oApp.DoCmd.OpenForm "Jobs", , , "JobNumber = " & LCategoryID
Else
If Range("D1") = "" Then
MsgBox ("This Order Form does not have a Job Number")
Else
MsgBox ("No job is linked to this Order Form")
End If
End If
End Sub

It seems to work fine except that I would like to be able to check if that
Access file is already open, then it shouldn't open a new instance but
instead, open the form in the instance of Access that is already open. Anyone
have any ideas on this?

"JCanyoneer" wrote:

> This might be a 2 parter-not sure.
> I need to use code to set the value in a cell (eg. 101012) to be a hyperlink
> which will open an Access file called NewJobs.mdb and open the form Jobs and
> goto record 101012 (field name is JobNumber). Say the hyperlinked cell is
> cell D2 which is also where the original 101012 would be.
>
> Can anyone help me with this?