?
Solved

Need help fixing error handler in access vba.

Posted on 2012-08-31
9
Medium Priority
?
585 Views
Last Modified: 2012-08-31
I have to error handlers followed by resume next that for some reason when it hits the resume next it goes back and loops through my errhndlr 3 times before moving on.

Here is the code;
   On Error GoTo errhndlr1
    myRec.Fields("DateProgrammed") = xlsht.Cells(8, "c")
errhndlr1:
    MsgBox "Unable to convert " & xlsht.Cells(8, "c") & " to date format"
    Resume Next
   
   
    On Error GoTo errhndlr
    myRec.Fields("DateLastRan") = xlsht.Cells(8, "e")
errhndlr:
    MsgBox "Unable to convert " & xlsht.Cells(8, "e") & " to date format"
    Resume Next
   


In this code if either of these have errors it will go to the errhndlr and display the msgbox, then goes to Resume Next and then back to the msgbox above resume next 3 times before moving on to the next line of code.


Thanks in advance for the help!
0
Comment
Question by:SeyerIT
9 Comments
 
LVL 58
ID: 38355000
A. Make sur eyou have an Exit Sub/Function above the handlers.

B. Probably not a wise idea to reference the cell which may have generated the error.

Jim.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 38355012
Did you check the value of xlsht?  If xlsht is not instantiated and has a value of "nothing" then that would cause an error within your error routine..... and possibly have the unintended consequence that you are experiencing.

as a work-around... you could check xlsht for nothing prior to referencing it in your error handler.... something like this:
    On Error GoTo errhndlr
    myRec.Fields("DateLastRan") = xlsht.Cells(8, "e")
errhndlr:
    If xlsht is nothing then
          MsgBox "xlsht is not initialized"
    Else 
          MsgBox "Unable to convert " & xlsht.Cells(8, "e") & " to date format"
    End If   
Resume Next

Open in new window

0
 
LVL 2

Author Comment

by:SeyerIT
ID: 38355032
Hey Jim,

A. Make sur eyou have an Exit Sub/Function above the handlers.

How should I do this? My objective was to tell the user that when it's importing the date from xlsht.cells(8, "c") that if it is not in a date format then it could not import and they could see what the value was in the message box.

Can you recommend a different way to do this? I was under the impression that once it would hit the Resume Next it would move on.

B. Probably not a wise idea to reference the cell which may have generated the error.
The cell that generating the error is just because it's trying to import it into a date field and some of them were typed in as text. I just want the user to know what the text is if it couldn't import it. Should I be doing something different for this? Or should I just tell them there was a problem without including the text?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:SeyerIT
ID: 38355046
Did you check the value of xlsht?  If xlsht is not instantiated and has a value of "nothing" then that would cause an error within your error routine..... and possibly have the unintended consequence that you are experiencing.

as a work-around... you could check xlsht for nothing prior to referencing it in your error handler.... something like this:

I haven't had a problem with this.. If the field is blank it just imports it as so and doesn't cause any issues.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 38355102
On the first, normally you structure like this:


Public Sub mySub()

  On Error Goto mySubError

  ' Do some stuff

mySubExit:
  On Error resume next

   ' Do cleanup tasks

  Exit Sub

mySubError:
    Msgbox "Unexpected error "
    Resume mySubExit

End Sub

   
 and possibly Resume next at the end, although I don't usually resume next with this type of setup.  Instead, I'll do in-line error handling if  I have an error that I really don't care about or if I have the same type of error possible in two different places, but want to react differently.   For example, a 2501 from canceling a report and the procedure calls one report that is OK for the user to cancel, but another it calls is not.

The in-line handling looks like this:


   ' Turn off error handler
    On Error Resume Next

    ' Clear the error indicator
    Err = 0

    ' Do operation here
   
    ' Do this when we want to check if an error occured.  If not, don't bother.
    If Err<>0 then
       ' something happened
    End if
   
    ' Go back to regular error handling
    On Error Goto mySubError


<<Or should I just tell them there was a problem without including the text? >>

 That's what I'd do.  I generally try to avoid giving the actual data and just give a reference to it.  Enough that they can identify it and look at it.

Jim.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 38355126
Ok so perhaps I could skip error handling all together and just test xlsht.Cells(8, "c") and xlsht.Cells(8, "e") to see if they are dates.

if xlsht.Cells(8, "c") = date then
myRec.Fields("DateProgrammed") = xlsht.Cells(8, "c")
else
end if

if xlsht.Cells(8, "e") = date then
myRec.Fields("DateLastRan") = xlsht.Cells(8, "e")
else
end if

I really don't even need to tell the users about it so this would work if I knew how to check the xlsht cell to see if it's a date.

Any idea how to just do that?
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 38355145
if IsDate(xlsht.Cells(8, "c")) = True then

Jim.
0
 
LVL 2

Author Closing Comment

by:SeyerIT
ID: 38355170
Thanks for all your help Jim.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38355194
<off topic>
@jim
can you help this guy.. sorry, don't have much time to spare..

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27849134.html

thanks
rey
</off topic>
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question