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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
Sorry i should have clarified, when it doesn't work it goes to the
errorhandler line
MsgBox "No data found"
 
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.
 
Back
Top