When you say it doesn't work what exactly do you mean. Does it crash. Does it
enter the error handler. The only thing that I notice is that you are not
destroying your objects when you are done with them. Try setting the objects
to nothing before the procedure ends.

set appAccess = nothing

Also you should be explicitly defining your variables with Dim statements. I
doubt that this is the cause of your difficulties but it wouldn't hurt.
--
HTH...

Jim Thomlinson


"Naz" wrote:

Hi all,

I have the following macro in Excel to open a form in Access and go to a
specific record.
The first time the macro runs it work, the second time its doesn't the third
it does. and so on. The first time i open the sheet and run it it will work.
When it doesn't run it simply displays the " No data found " message.
I think it may have something to do with Access not closing properly after
the 1st run, simply because in Task Manager in the processes i see MSACCESS.

Dim appAccess As Access.Application

Sub DisplayOASSISForm()

FindRef = ActiveCell

On Error GoTo Error_Handler

' Initialize string to database path.
Const strConPathToSamples = "c:\eoc.mdb"

' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToSamples

' Open Orders form.
DoCmd.OpenForm "frmEOC", acNormal, "", "", , acNormal
DoCmd.FindRecord FindRef, acEntire, False, , False, acCurrent, True

Exit Sub

Error_Handler:

appAccess.Visible = False
MsgBox "No data found"
appAccess.Quit acQuitSaveNone

End

End Sub


--

_______________________
Naz,
London