I've only been coding in VB for about 5 months, plese excuse this if
it's elementary.

I'm creating a program in VB6 which will go through the list of
customer accounts in our SQL-2000 database and do some cleanup. For a
few hundred of our subcontracted accounts, the USPS ZipFour database
for that block of zip codes and street segments hasn't been imported
to our database, these accounts cause errors in my program and need to
be skipped.

I've created an Error handler which is supposed to jump to the end of
the loop for my ADODB recordset. It works fine the first time, the
error clears and the program continues looping through the recordset.
On the second account, however, the program seems to ignore the On
Error statement and comes crashing to a halt with the run-time
messagebox.

>SNIP<
        
        
        sZipCode = rsAcct("ServiceZip") & ""
        
        sSQL = "SELECT PrimLowAddress, PrimHighAddress, CarrierRt,
PrimEOB, streetPreDirection, " & _
                "StreetPostDirection FROM [" & sTable & "] A " & _
                "WHERE A.StreetName = '" & sStreetName & "' AND
A.ZipCode = '" & sZipCode & "' " & _
                "ORDER BY PrimLowAddress, PrimHighAddress;"
                
        'for records where Zip code table is not in database
        If sTable = "596" Then
        StrError = "***Zip code not in database " & sTable
            GoTo invalidName
        End If
        If IsNull(rsAcct!servicezip) = True Then
            StrError = "No Zip code"
            GoTo invalidZip
        End If       
        For intCheck = 1 To Len(sStreetName)
            If Mid(sStreetName, intCheck, 1) = Chr(39) Then
            StrError = "Apostrophe in name"
            GoTo invalidName
            End If
        Next
'CODE CRASHES HERE (ON RECORDSET OPEN)              
        rsZip.Open sSQL, cn, adOpenStatic, adLockReadOnly
        intMatch = 0
        If Not rsZip.EOF Then
            rsZip.MoveLast
            rsZip.MoveFirst
        End If
        'check Even/Odd/Both status, if both then count by one else
count by two
        Do Until rsZip.EOF

>SNIP SNIP<


invalidZip:
        StrError = ""
        If intMatch = 1 Then intMatchCount = intMatchCount + 1
        rsAcct.MoveNext
    Loop
    
    lblLabel.Caption = "FINISHED." & "Found " & intMatchCount & "
entries out of " & rsAcct.RecordCount
  
        
    'release recordsets from memory
    Set rsZip = Nothing
    Set rsAcct = Nothing
    Set cn = Nothing
Exit Sub

ErrorHandling:
    Debug.Print "Error " & Err.Number & "   " & Err.Description
    Select Case Err.Number
        Case -2147217865 'Object not found (ZIP code table not in
database)
            Debug.Print "Zip prefix " & sZipCode & " is not loaded in
the database. Record will be skipped."
            Err.Clear
            GoTo invalidZip
    End Select
    
End Sub

"Andre" <andreranieri@hotmail.com> wrote in message <news:87a2cf3f.0306271456.5be3040a@posting.google.com>...

> I've only been coding in VB for about 5 months, plese excuse this if
> it's elementary.
>
> I'm creating a program in VB6 which will go through the list of
> customer accounts in our SQL-2000 database and do some cleanup. For a
> few hundred of our subcontracted accounts, the USPS ZipFour database
> for that block of zip codes and street segments hasn't been imported
> to our database, these accounts cause errors in my program and need to
> be skipped.
>
> I've created an Error handler which is supposed to jump to the end of
> the loop for my ADODB recordset. It works fine the first time, the
> error clears and the program continues looping through the recordset.
> On the second account, however, the program seems to ignore the On
> Error statement and comes crashing to a halt with the run-time
> messagebox.

You're exiting the error handler with a GoTo, when you need to
use Resume.  Without seeing the rest of your code, I can't tell
if your use of GoTos is really the best strategy or not.  Most
will tell you that GoTo is evil, but sometimes GoTo just sucks
less than the alternatives.

> >SNIP<
>
>
>         sZipCode = rsAcct("ServiceZip") & ""
>
>         sSQL = "SELECT PrimLowAddress, PrimHighAddress, CarrierRt,
> PrimEOB, streetPreDirection, " & _
>                 "StreetPostDirection FROM [" & sTable & "] A " & _
>                 "WHERE A.StreetName = '" & sStreetName & "' AND
> A.ZipCode = '" & sZipCode & "' " & _
>                 "ORDER BY PrimLowAddress, PrimHighAddress;"
>
>         'for records where Zip code table is not in database
>         If sTable = "596" Then
>         StrError = "***Zip code not in database " & sTable
>             GoTo invalidName
>         End If
>         If IsNull(rsAcct!servicezip) = True Then
>             StrError = "No Zip code"
>             GoTo invalidZip
>         End If

>         For intCheck = 1 To Len(sStreetName)
>             If Mid(sStreetName, intCheck, 1) = Chr(39) Then
>             StrError = "Apostrophe in name"
>             GoTo invalidName
>             End If
>         Next

You should be able to replace this entire loop with just,
          If InStr(1, sStreetName, "'", vbBinaryCompare) > 0 Then
              StrError = ... etc etc etc

> 'CODE CRASHES HERE (ON RECORDSET OPEN)
>         rsZip.Open sSQL, cn, adOpenStatic, adLockReadOnly
>         intMatch = 0

>         If Not rsZip.EOF Then
>             rsZip.MoveLast
>             rsZip.MoveFirst
>         End If

The above shouldn't be needed if you're just going to iterate
through the records anyway.

>         'check Even/Odd/Both status, if both then count by one else
> count by two
>         Do Until rsZip.EOF
>
> >SNIP SNIP<
>
>
> invalidZip:
>         StrError = ""
>         If intMatch = 1 Then intMatchCount = intMatchCount + 1
>         rsAcct.MoveNext
>     Loop
>
>     lblLabel.Caption = "FINISHED." & "Found " & intMatchCount & "
> entries out of " & rsAcct.RecordCount
>
>
>     'release recordsets from memory
>     Set rsZip = Nothing
>     Set rsAcct = Nothing
>     Set cn = Nothing
> Exit Sub
>
> ErrorHandling:
>     Debug.Print "Error " & Err.Number & "   " & Err.Description
>     Select Case Err.Number
>         Case -2147217865 'Object not found (ZIP code table not in
> database)
>             Debug.Print "Zip prefix " & sZipCode & " is not loaded in
> the database. Record will be skipped."
>             Err.Clear
>             GoTo invalidZip
>     End Select
>
> End Sub

--
Joe Foster <mailto:jlfoster%40znet.com>     Got Thetans? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!