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

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

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

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

_______________________
Naz,
London


"Jim Thomlinson" wrote:

> 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

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

Jim Thomlinson


"Naz" wrote:

> Sorry i should have clarified, when it doesn't work it goes to the
> errorhandler line
> MsgBox "No data found"
> --
>
> _______________________
> Naz,
> London
>
>
> "Jim Thomlinson" wrote:
>
> > 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

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

triggers the error handling
--

_______________________
Naz,
London


"Jim Thomlinson" wrote:

> Which line of code throws the error which sends the execution into the
> errorhandler?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Naz" wrote:
>
> > Sorry i should have clarified, when it doesn't work it goes to the
> > errorhandler line
> > MsgBox "No data found"
> > --
> >
> > _______________________
> > Naz,
> > London
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > 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

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.

--
- K Dales


"Naz" wrote:

> DoCmd.OpenForm "frmEOC", acNormal, "", "", , acNormal
>
> triggers the error handling
> --
>
> _______________________
> Naz,
> London
>
>
> "Jim Thomlinson" wrote:
>
> > Which line of code throws the error which sends the execution into the
> > errorhandler?
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Naz" wrote:
> >
> > > Sorry i should have clarified, when it doesn't work it goes to the
> > > errorhandler line
> > > MsgBox "No data found"
> > > --
> > >
> > > _______________________
> > > Naz,
> > > London
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > 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

Thanks !! That works a treat.

--

_______________________
Naz,
London


"K Dales" wrote:

> 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.
>
> --
> - K Dales
>
>
> "Naz" wrote:
>
> > DoCmd.OpenForm "frmEOC", acNormal, "", "", , acNormal
> >
> > triggers the error handling
> > --
> >
> > _______________________
> > Naz,
> > London
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Which line of code throws the error which sends the execution into the
> > > errorhandler?
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Naz" wrote:
> > >
> > > > Sorry i should have clarified, when it doesn't work it goes to the
> > > > errorhandler line
> > > > MsgBox "No data found"
> > > > --
> > > >
> > > > _______________________
> > > > Naz,
> > > > London
> > > >
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > 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