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

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

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

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

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

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

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

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

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

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

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

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

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