Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink formula works, and then doesn't work | Excel Discussion (Misc queries) | |||
SUMIF formula works in Excel 2003, does not work in Excel 2007 | Excel Discussion (Misc queries) | |||
Simple Macro, works in Excel 2002, 2003 but won't work in 2000 | Excel Programming | |||
How do I convert works file to excel without works software? | Excel Discussion (Misc queries) | |||
How can I get EXCEL formulas to work in Works? | Excel Worksheet Functions |