Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Better way to test for empty Recordset
I have this code:
'code.............. With cm .ActiveConnection = cn .CommandText = DesktopReportQuery .CommandType = adCmdStoredProc Set pm1 = .CreateParameter(InputField, adInteger, adParamInput) .Parameters.Append pm1 .Parameters(InputField) = ReportMonth Set pm2 = .CreateParameter(InputField, adInteger, adParamInput) .Parameters.Append pm2 .Parameters(InputField) = ReportYear End With rs.Open cm Set PC = ThisWorkbook.PivotCaches.Add (SourceType:=xlExternal) Set PC.Recordset = rs Set PT = PTSheet.PivotTables.Add(PivotCache:=PC, TableDestination:=PTSheet.Range("A9")) 'code............... It's always worked fine. However, I eventually ran into a situation where the Recordset is empty. So the pivot table creation fails. So I decided simply to use an If structure to first test if the Recordset was empty, like this: With cm .ActiveConnection = cn .CommandText = DesktopReportQuery .CommandType = adCmdStoredProc Set pm1 = .CreateParameter(InputField, adInteger, adParamInput) .Parameters.Append pm1 .Parameters(InputField) = ReportMonth Set pm2 = .CreateParameter(InputField, adInteger, adParamInput) .Parameters.Append pm2 .Parameters(InputField) = ReportYear End With if rs.EOF Then 'code to skip the creation of the pivot table Else Set PC = ThisWorkbook.PivotCaches.Add (SourceType:=xlExternal) Set PC.Recordset = rs Set PT = PTSheet.PivotTables.Add(PivotCache:=PC, TableDestination:=PTSheet.Range("A9")) End if It works if rs.EOF is True. But if there IS data in the Recordset, when it gets to the statement to put the pivot table in Cell A9, I get this error: 'One or More Accessor Flags were Invalid' Huh? The only difference is that it has to first test if rs.EOF is True. How can that cause this error? Better yet, is there a better way to run my query and then abort the creation of the pivot table if the recordset is empty? tod |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any way to test whether rows are empty or contain the word Pass ? | Excel Worksheet Functions | |||
IF function to test whether the cell value is equal to empty nest | Excel Worksheet Functions | |||
How can I test if a cell is empty? | Excel Discussion (Misc queries) | |||
test expression for empty cell in =SUMIF() | Excel Worksheet Functions | |||
Test if the range is empty | Excel Discussion (Misc queries) |