it works, it doesn work, its works....and so on.

[Naz]

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

 

[Jim Thomlinson]

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.

 

[Naz]

Sorry i should have clarified, when it doesn't work it goes to the
errorhandler line
MsgBox "No data found"

 

[Jim Thomlinson]

Which line of code throws the error which sends the execution into the
errorhandler?

 

[Naz]

DoCmd.OpenForm "frmEOC", acNormal, "", "", , acNormal

triggers the error handling

 

[K Dales]

The line in question should be:
appAccess.DoCmd.OpenForm "frmEOC", acNormal,...
otherwise you are not referring to the Access object model but to Excel. My
only question is why it would work at all (on the first & third times
through), since this error should occur every time the code hits those lines.

 

[Naz]

Thanks !! That works a treat.