Object variable not set???

[samonwalkabout]

From a form i have a command button that gives an excel out put of a 3 querys onto 3 tabs of an excel file. It then opens the file and formats the data slightly. This works fine. Once then if i try the same thing again it give an error. If i close the db re-open and try again it works but only the frist time. Error is

"Object variable or With Block not set"

Code is

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, "c:\BudgetExcelLO.xls" ' create sheet

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryeeLO", "c:\BudgetExcelLO.xls", , "AllRawMaterials" 'export1

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryEESolLO", "c:\BudgetExcelLO.xls", , "JustSolvents" ' export2

MsgBox "The Excel Sheet BudgetExcelLO has been created on the C Drive of this computer" 'tell user

Call OpenExcel("c:\BudgetExcelLO.xls") ' open sheet

ActiveWindow.TabRatio = 0.503
Sheets("AllRawMaterials").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-1],qryrates!R2C[-5]:R5C[-4])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F53")
Range("F2:F53").Select
Range("G2").Select
ActiveCell.FormulaR1C1 = "=(RC[-3]*RC[-1])"
etc etc etc etc etc etc etc

Code complies fine and only gives error the 2nd time i run it.

Any ideas?

 

[The_Doc_Man]

Have you ever used the Access debugger?

I would put a breakpoint in the top of your module and single-step through the code until you find the step that dies. In later versions of Access, if you have enabled it, a module that is opened by a breakpoint will support a "tool tip" (mouse-over situation) showing the value of each variable.

In general, it sounds like you have something that is somehow left 'dirty' after the first run.

I'm going to stab at this and ask if you remembered to always CLOSE what you OPEN. 'cause the CLOSE action was buried in your 'etc etc etc'

 

[samonwalkabout]

The code breaks before the excel formating begins, but only the second time it runs, im thinking access is passing a default value the frist time it runs and works fine but the second time access starts looking for an object defintion and i havent set it in the code.

 

[The_Doc_Man]

The code breaks before the excel formating begins, but only the second time it runs, im thinking access is passing a default value the frist time it runs and works fine but the second time access starts looking for an object defintion and i havent set it in the code.

With due respect, this is not likely. You have a side-effect somewhere and are using a variable that is "Own" (permanent) or a channel that is no longer valid. Do you delete anything from within your code represented by the etc. etc. etc.?

The thing that makes me say this is a case of something not being closed right is that it works if you kill the app and restart it, but fails if you leave the app running and retrigger the action. This is an indicator of a variable that resides in the private memory of Access or is bound to Access through a system-level data structure. The reason it works if you kill and restart Access is because when you kill Access, 'process rundown' occurs. Windows does a really valiant effort to reclaim all the memory you have used up. But it fails miserably sometimes 'cause... well DUH ... it's Windows!

Here is an experiment to try. The location of this option depends on your version of Windows, but you want to find the system performance monitoring tools and monitor: Swap Space; Free Memory; Resources; Buffers; Open Files. Might be a couple more, but those jump out at me.

OK, let the perf. monitor run in the foreground for a moment. See the level of the monitored resources. Watch the pattern of usage as you sit idle.

Now trigger Access. Check your monitored resources.

Now trigger your formatting app. Immediately switch to the perf. monitor window and watch the monitored resources. Watch VERY carefully as your formatting code terminates. I'm guessing that one of the resources I named will become over 50% consumed and won't go back down to its previous level. (That's at best an educated guess.)

Run the formatting app again. See if one of the monitored resources pegs out at the top of the scale. If so, that's the one you are over-consuming. If it is swap file space or free memory, you have a performance problem. If it is some other resource, it might get trickier and the solution depends on your version of Windows. But at least you could look up "Depletion of xxxxxx" or "Out of xxxxxx" (where xxxxxx is the depleted resource) in the MS Knowledge Base.

 

[samonwalkabout]

No seems to be fine falls back down to noraml levels ......but i cant seem to re-create the error anymore either. the etc etc etc code is just a excel macro that runs in the active window it just formats some cells in the sheet. Will keep experimenting with diffrent arrangements

 

[samonwalkabout]

Okay when i fail to select a Location from a drop down i get a msgbox "Select Location" i then select a location and run the export to excel all works fine....

the second time the location is in so the error msgbox doesn't appear the export happens but the code doesnt run properly... here is the code

stDocName = "qryrates"

If Me.loco = "1" Then
MsgBox "LO Report"

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, "c:\BudgetExcelLO.xls" ' create sheet
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryeeLO", "c:\BudgetExcelLO.xls", , "AllRawMaterials" 'export1
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryEESolLO", "c:\BudgetExcelLO.xls", , "JustSolvents" ' export2
MsgBox "The Excel Sheet BudgetExcelLO has been created on the C Drive of this computer" 'tell user
Call OpenExcel("c:\BudgetExcelLO.xls") ' open sheet

ElseIf Me.loco = "2" Then

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, "c:\BudgetExcelLI.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryEELI", "c:\BudgetExcelLI.xls", , "AllRawMaterials"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qryEESOLLI", "c:\BudgetExcelLI.xls", , "JustSolvents"
MsgBox "The Excel Sheet BudgetExcelLI has been created on the C Drive of this computer"
Call OpenExcel("c:\BudgetExcelLI.xls")

Else

MsgBox "Select Location"

End

End If

ActiveWindow.TabRatio = 0.503
Sheets("AllRawMaterials").Select
Range("F2").Select
ActiveCell.FormulaR1C1 = "=LOOKUP(RC[-1],qryrates!R2C[-5]:R5C[-4])"
Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F53")

etc etc etc etc etc


I know this must be simple whats going wrong!!

 

[samonwalkabout]

you were right i was missing 3 little letters at the end of my code

END

now i can go home!!