"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!