ERROR TRAP IN VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a crosstab query that sends back a variable number of columns based on
the particular criteria feed to the underlying query...that of course is the
nature of a crosstab. However, if I look for a particular column that is
possible through the recordset index (rs(x).Name="...") AND the value
("....whatever") for a particular column name is NOT returned due to the
particular parameters of this query (e.g., columns are all possible product
codes, and only a subset of these values are returned for a particular sales
person because they only sold certain items in a particular period being
queried), then when the statement IF rs(x).Name="A", for example, is
encountered, an error message is generated, which is "item not found in this
collection". I NEED TO TRAP THIS ERROR SO I KNOW WHEN A PARTICULAR COLUMN IS
OR IS NOT RETURNED IN A CROSSTAB QUERY. This is exactly what I need specific
help with....can ANYONE help me with this?

So far I have gotten several suggestions which are completely off the mark
and the people replying do not seem to understand what I need. CAN ANYONE
HELP...this is a rather straightforward question! THANKS SOOOO MUCH FOR ANY
HELP!!!!

MS
 
Maybe something like:

....(code)
On Error Resume Next
strTest = rs(x).Name
Select Case Err.Number
Case 3265
On Error GoToErrHandler
' Item Not found in this collection
' what do you want to do?
Case Else
' Includes Err.Number = 0 (= no error raised)
On Error GoToErrHandler
Select Case strTest
Case "A"
...yada, yada
Case Else

End Select
End Select

However, your post leaves me a little confused at what you really doing (I
suspect it's not quite what you typed here).
Assuming x is numeric (which AFAIK, it has to be in that syntax),
rs(x).Name
would only generate an error if x is larger than the number of fields, and
the error would be "subscript out of bounds". If you use
For x = 1 to rs.Fields.Count
then that would never generate an error, while still allowing you to test
all fields for specific names.

On the other hand, if you were to use
strTemp = rs.Fields("SomeNameThatMayNotExist").Value
*that* could easily generate the "item not found" error, but that is *not*
what you posted (and may explain why the help you've recieved so far has
been "off the mark"?)

In any case:
HTH,
 
George, so I am assuming that the actual error code for "item not in this
collection.." is 3265?! I fully understand the error trap construct, I just
didn't know the 3265! Thanks sooo much....you would not believe some of the
convoluted responses I've gotten that were way off the mark!

ms
 
Actuall George, I just read the rest of your note, so I want to send you this
code and explain my problem a bit better:

rs.MoveFirst
Do While Not rs.EOF
intSPV = 0
intRVV = 0
intOther = 0
xRV = 0
xSP = 0
x = 2
'
Do While x <= intcol
If rs(x).Name = "A" And rs(x).Value = rs![Total] Then
intA = intA + 1
ElseIf rs(x).Name = "F" And rs(x).Value = rs![Total] Then
intF = intF + 1
ElseIf rs(x).Name = "L" And rs(x).Value = rs![Total] Then
intL = intL + 1
ElseIf rs(x).Name = "RV" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "SP" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "RV" And rs(x).Value > 0 Then
intRVV = -1
xRV = x
ElseIf rs(x).Name = "SP" And rs(x).Value > 0 Then
intSPV = -1
xSP = x
ElseIf rs(x).Name = "A" Or rs(x).Name = "F" Or rs(x).Name = "L"
Or rs(x).Name = "SP" Or rs(x).Name = "RV" Then
ElseIf rs(x).Value > 0 Then
intOther = -1
End If
x = x + 1
Loop
'
If intSPV = 0 And intRVV = 0 And intOther = 0 Then
If rs![A] > 0 And rs![F] > 0 And rs![L] > 0 Then <--- problem!!
intAFL = intAFL + 1
ElseIf rs![A] > 0 And rs![F] > 0 And IsNull(rs![L]) Then
intAF = intAF + 1
ElseIf rs![A] > 0 And IsNull(rs![F]) And rs![L] > 0 Then
intAL = intAL + 1
ElseIf IsNull(rs![A]) And rs![F] > 0 And rs![L] > 0 Then
intFL = intFL + 1
End If
ElseIf intSPV = -1 And intRVV = -1 And intOther = 0 Then
If rs(xRV).Value + rs(xSP).Value = rs![Total] Then
intSO = intSO + 1
End If
End If
rs.MoveNext
Loop

You can see that I loop through each column of each row looking for values
for business reasons of this particular report, then move to the next row and
continue the process. The problem comes when I have finished moving through
all the columns for a row, I need to evaluate a compound set of data
circumstances based on column values just examined. HOWEVER, there are three
columns as you can see from where I marked the "problem" that the business
logic requires me to examine, A, F, and L. However, when I step into this
point in the code, and ask:

If rs![A] > 0 And rs![F] > 0 And rs![L] > 0 Then

along with the elseif statements, I don't know if rs![A] exists as a column
in the results of this particular crosstab query results set. Here is where
the "item not found...." is hit, and ideally I want to basically know if
rs![A] or rs![F] or rs![L] exists or not so I can react accordingly.

Is my issue clear and how would you appoach this??? Thanks for taking time
to help!
 
Just an idea, since it seems you don't really care whether the field exists,
only if it has a value >0...

Dim varData As Variant
varData = Array("A","F","L")
On error resume next
For i = Lbound(varData) to Ubound(varData)
intTemp = 0
intTemp = rs.Fields(varData(i))
If intTemp>0 then
strAFL = strAFL & varData(i)
End If
next i
on error goto errhandler

Select Case strAFL
Case "AFL"
intAFL = intAFL + 1
Case "AF"
intAFL = intAF + 1
Case "AL"
intAL = intAL + 1
Case "FL"
intFL = intFL + 1
Case "A", "F", "L", ""
'Do Nothing?
End Select

--
George Nicholson

Remove 'Junk' from return address.


Mike S. S. said:
Actuall George, I just read the rest of your note, so I want to send you
this
code and explain my problem a bit better:

rs.MoveFirst
Do While Not rs.EOF
intSPV = 0
intRVV = 0
intOther = 0
xRV = 0
xSP = 0
x = 2
'
Do While x <= intcol
If rs(x).Name = "A" And rs(x).Value = rs![Total] Then
intA = intA + 1
ElseIf rs(x).Name = "F" And rs(x).Value = rs![Total] Then
intF = intF + 1
ElseIf rs(x).Name = "L" And rs(x).Value = rs![Total] Then
intL = intL + 1
ElseIf rs(x).Name = "RV" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "SP" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "RV" And rs(x).Value > 0 Then
intRVV = -1
xRV = x
ElseIf rs(x).Name = "SP" And rs(x).Value > 0 Then
intSPV = -1
xSP = x
ElseIf rs(x).Name = "A" Or rs(x).Name = "F" Or rs(x).Name = "L"
Or rs(x).Name = "SP" Or rs(x).Name = "RV" Then
ElseIf rs(x).Value > 0 Then
intOther = -1
End If
x = x + 1
Loop
'
If intSPV = 0 And intRVV = 0 And intOther = 0 Then
If rs![A] > 0 And rs![F] > 0 And rs![L] > 0 Then <---
problem!!
intAFL = intAFL + 1
ElseIf rs![A] > 0 And rs![F] > 0 And IsNull(rs![L]) Then
intAF = intAF + 1
ElseIf rs![A] > 0 And IsNull(rs![F]) And rs![L] > 0 Then
intAL = intAL + 1
ElseIf IsNull(rs![A]) And rs![F] > 0 And rs![L] > 0 Then
intFL = intFL + 1
End If
ElseIf intSPV = -1 And intRVV = -1 And intOther = 0 Then
If rs(xRV).Value + rs(xSP).Value = rs![Total] Then
intSO = intSO + 1
End If
End If
rs.MoveNext
Loop

You can see that I loop through each column of each row looking for values
for business reasons of this particular report, then move to the next row
and
continue the process. The problem comes when I have finished moving
through
all the columns for a row, I need to evaluate a compound set of data
circumstances based on column values just examined. HOWEVER, there are
three
columns as you can see from where I marked the "problem" that the business
logic requires me to examine, A, F, and L. However, when I step into this
point in the code, and ask:

If rs![A] > 0 And rs![F] > 0 And rs![L] > 0 Then

along with the elseif statements, I don't know if rs![A] exists as a
column
in the results of this particular crosstab query results set. Here is
where
the "item not found...." is hit, and ideally I want to basically know if
rs![A] or rs![F] or rs![L] exists or not so I can react accordingly.

Is my issue clear and how would you appoach this??? Thanks for taking
time
to help!

George Nicholson said:
Maybe something like:

....(code)
On Error Resume Next
strTest = rs(x).Name
Select Case Err.Number
Case 3265
On Error GoToErrHandler
' Item Not found in this collection
' what do you want to do?
Case Else
' Includes Err.Number = 0 (= no error raised)
On Error GoToErrHandler
Select Case strTest
Case "A"
...yada, yada
Case Else

End Select
End Select

However, your post leaves me a little confused at what you really doing
(I
suspect it's not quite what you typed here).
Assuming x is numeric (which AFAIK, it has to be in that syntax),
rs(x).Name
would only generate an error if x is larger than the number of fields,
and
the error would be "subscript out of bounds". If you use
For x = 1 to rs.Fields.Count
then that would never generate an error, while still allowing you to test
all fields for specific names.

On the other hand, if you were to use
strTemp = rs.Fields("SomeNameThatMayNotExist").Value
*that* could easily generate the "item not found" error, but that is
*not*
what you posted (and may explain why the help you've recieved so far has
been "off the mark"?)

In any case:
HTH,
Click to expand...
Click to expand...
 
Hi,



Someone can also check if the PIVOT's expression returns a record, on the
original set. Illustration is easier than words:


TRANSFORM ... SELECT ... FROM myTable ... PIVOT expression(f1)


Then, to know if field ABB is ever created by this crosstab:



SELECT COUNT(*) FROM myTable WHERE 'ABB' = expression(f1)


would return one record, one field, with a 0 if the field ABB won't be
created by the crosstab, something else than 0 if the field would be created
by the first crosstab.




Hoping it may help,
Vanderghast, Access MVP



George Nicholson said:
Just an idea, since it seems you don't really care whether the field
exists, only if it has a value >0...

Dim varData As Variant
varData = Array("A","F","L")
On error resume next
For i = Lbound(varData) to Ubound(varData)
intTemp = 0
intTemp = rs.Fields(varData(i))
If intTemp>0 then
strAFL = strAFL & varData(i)
End If
next i
on error goto errhandler

Select Case strAFL
Case "AFL"
intAFL = intAFL + 1
Case "AF"
intAFL = intAF + 1
Case "AL"
intAL = intAL + 1
Case "FL"
intFL = intFL + 1
Case "A", "F", "L", ""
'Do Nothing?
End Select

--
George Nicholson

Remove 'Junk' from return address.


Mike S. S. said:
Actuall George, I just read the rest of your note, so I want to send you
this
code and explain my problem a bit better:

rs.MoveFirst
Do While Not rs.EOF
intSPV = 0
intRVV = 0
intOther = 0
xRV = 0
xSP = 0
x = 2
'
Do While x <= intcol
If rs(x).Name = "A" And rs(x).Value = rs![Total] Then
intA = intA + 1
ElseIf rs(x).Name = "F" And rs(x).Value = rs![Total] Then
intF = intF + 1
ElseIf rs(x).Name = "L" And rs(x).Value = rs![Total] Then
intL = intL + 1
ElseIf rs(x).Name = "RV" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "SP" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "RV" And rs(x).Value > 0 Then
intRVV = -1
xRV = x
ElseIf rs(x).Name = "SP" And rs(x).Value > 0 Then
intSPV = -1
xSP = x
ElseIf rs(x).Name = "A" Or rs(x).Name = "F" Or rs(x).Name =
"L"
Or rs(x).Name = "SP" Or rs(x).Name = "RV" Then
ElseIf rs(x).Value > 0 Then
intOther = -1
End If
x = x + 1
Loop
'
If intSPV = 0 And intRVV = 0 And intOther = 0 Then
If rs![A] > 0 And rs![F] > 0 And rs![L] > 0 Then <---
problem!!
intAFL = intAFL + 1
ElseIf rs![A] > 0 And rs![F] > 0 And IsNull(rs![L]) Then
intAF = intAF + 1
ElseIf rs![A] > 0 And IsNull(rs![F]) And rs![L] > 0 Then
intAL = intAL + 1
ElseIf IsNull(rs![A]) And rs![F] > 0 And rs![L] > 0 Then
intFL = intFL + 1
End If
ElseIf intSPV = -1 And intRVV = -1 And intOther = 0 Then
If rs(xRV).Value + rs(xSP).Value = rs![Total] Then
intSO = intSO + 1
End If
End If
rs.MoveNext
Loop

You can see that I loop through each column of each row looking for
values
for business reasons of this particular report, then move to the next row
and
continue the process. The problem comes when I have finished moving
through
all the columns for a row, I need to evaluate a compound set of data
circumstances based on column values just examined. HOWEVER, there are
three
columns as you can see from where I marked the "problem" that the
business
logic requires me to examine, A, F, and L. However, when I step into
this
point in the code, and ask:

If rs![A] > 0 And rs![F] > 0 And rs![L] > 0 Then

along with the elseif statements, I don't know if rs![A] exists as a
column
in the results of this particular crosstab query results set. Here is
where
the "item not found...." is hit, and ideally I want to basically know if
rs![A] or rs![F] or rs![L] exists or not so I can react accordingly.

Is my issue clear and how would you appoach this??? Thanks for taking
time
to help!

George Nicholson said:
Maybe something like:

....(code)
On Error Resume Next
strTest = rs(x).Name
Select Case Err.Number
Case 3265
On Error GoToErrHandler
' Item Not found in this collection
' what do you want to do?
Case Else
' Includes Err.Number = 0 (= no error raised)
On Error GoToErrHandler
Select Case strTest
Case "A"
...yada, yada
Case Else

End Select
End Select

However, your post leaves me a little confused at what you really doing
(I
suspect it's not quite what you typed here).
Assuming x is numeric (which AFAIK, it has to be in that syntax),
rs(x).Name
would only generate an error if x is larger than the number of fields,
and
the error would be "subscript out of bounds". If you use
For x = 1 to rs.Fields.Count
then that would never generate an error, while still allowing you to
test
all fields for specific names.

On the other hand, if you were to use
strTemp = rs.Fields("SomeNameThatMayNotExist").Value
*that* could easily generate the "item not found" error, but that is
*not*
what you posted (and may explain why the help you've recieved so far has
been "off the mark"?)

In any case:
HTH,
--
George Nicholson

Remove 'Junk' from return address.


I have a crosstab query that sends back a variable number of columns
based
on
the particular criteria feed to the underlying query...that of course
is
the
nature of a crosstab. However, if I look for a particular column that
is
possible through the recordset index (rs(x).Name="...") AND the value
("....whatever") for a particular column name is NOT returned due to
the
particular parameters of this query (e.g., columns are all possible
product
codes, and only a subset of these values are returned for a particular
sales
person because they only sold certain items in a particular period
being
queried), then when the statement IF rs(x).Name="A", for example, is
encountered, an error message is generated, which is "item not found
in
this
collection". I NEED TO TRAP THIS ERROR SO I KNOW WHEN A PARTICULAR
COLUMN
IS
OR IS NOT RETURNED IN A CROSSTAB QUERY. This is exactly what I need
specific
help with....can ANYONE help me with this?

So far I have gotten several suggestions which are completely off the
mark
and the people replying do not seem to understand what I need. CAN
ANYONE
HELP...this is a rather straightforward question! THANKS SOOOO MUCH
FOR
ANY
HELP!!!!

MS
Click to expand...
Click to expand...
Click to expand...
 
Back
Top