Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    189

    Question Unanswered: Problem with On Error Resume Next on ACC2007

    Hello,
    I have this problem with Error handling.

    I'm using Access 2007. I say this, because works well on Access 2003.

    Here is the code:

    Code:
        'Abro la tabla para cambiar las qTemp
        Set rsQuerys = CurrentDb.OpenRecordset("SELECT * FROM [tTabla SQLs para Formularios] WHERE [Nombre Formulario]=""" & Me.dLista.ItemData(Seleccion) & """;") ' WHERE [Nombre Query] Not Like '~*';")
            rsQuerys.MoveFirst
        
        Do While Not rsQuerys.EOF
            'Reemplazo texto en los SQL
                'anho_desde
                On Error Resume Next
                vTextoBuscar = Mid(rsQuerys![SQL Query], InStr(rsQuerys![SQL Query], " >= "), 10)
                vNuevoSQL = Replace(rsQuerys![SQL Query], vTextoBuscar, " >= " & Me.danho_hasta.Value & "01")
    As you can see, I'm "looking" for some text on some SQL that I have in a table.

    The problem is with the On Error Resume Next, when the Mid() gets error.
    The field rsQuerys![SQL Query] could get values where the Instr or the Mid will became error... So I add the On Error Resume Next to pass that -I don't care about what was the error-, but here on Access 2007:
    Boom, error (see attach).

    Any ideas how to solve the problem? How to manage the Error on the Mid and Instr?

    Thanks in advance.
    Saludos,
    pepemosca
    Attached Thumbnails Attached Thumbnails Dibujo.jpg  

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it is not brilliant in A'03 !

    imagine that vTextoBuscar ends up empty or Null because of some error in Mid() (should be Mid$() by the way)

    the next line would attempt to generate rather strange SQL like:
    SELECT * FROM myTable WHERE >= Xxxx01

    ...maybe you should care what was the error
    --or at least check between those two lines that vTextoBuscar holds a meaningful value before building SQL
    if lenb(nz(vTextoBuscar, "")) = 0 then
    'ooooops

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2008
    Posts
    189
    First,
    What's the difference between Mid and Mid$?

    Second,
    Thank you for the tip about the Nz(). I didn't knew about that VB function.

    Third,
    I get your idea about the error... But truly, when I have that error, is because I won't need that SQL code.
    I see what I should do is just save the error and not use the On Error Resume Next.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    First
    Mid$() is +/- twice as fast as Mid() when operating on strings such as your SQL

    Third
    agreed you wont (!! cant) use the resulting SQL if vTextoBuscar is null/empty. better to avoid generating broken SQL, no? your resume next will encourage an error in the Mid() line to create nonsense SQL

    Resume Next has it's place when you are 100% certain that your code can steamroller over ALL possible errors without falling off its bike (apologies for mixed metaphor).

    e.g. in tidy-up code when you are trying to close/kill objects, it is completely reasonable (and arguably essential) to use

    my_exit_routine:
    on error resume next
    close MyRecordset
    set MyRecordset= nothing
    exit sub

    from the limited details i have for your situation, i would not use resume next in your context
    i would divert errors to a handler (on error goto blahblah and then quit the routine if it was not an error i could fix & resume from the handler)
    AND
    verify after the Mid() line that vTextoBuscar holds a meaningful value (quitting the routine if not)
    ...all to avoid building nonsense SQL that is presumably going to be used elsewhere

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Apr 2008
    Posts
    189
    izy, thank you for all your insights.

    I did what you recomend, I modify the code in order to only do de Mid() and all that stuff when is really needed.
    I wont see a faster running, but is more stable -the code is not that complex-.

    Also, about the Mid$(), I will change the code.

    About the On Error Resume Next, thank you for the example of a good use of that "functionality" of VBA.

    Thank you again for your advices and help.
    Saludos,
    pepemosca

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •