ACCESS FEED: Re: [MS_AccessPros] Excel question

Kamis, 28 Januari 2016

Re: [MS_AccessPros] Excel question

Bob,

although this is an Access forum, we may be able to help if you give us more information. First, add an Error Handler to the code so you can see WHICH statement is causing the problem.

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   'set up Error Handler
   On Error GoTo Proc_Err[/code]

'   ... then your statements

'put this at the end of the procedure

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
   On Error Resume Next
   'release object variables if applicable-- ie:
'   if Not rs is Nothing then
'      rs.close
'      set rs = Nothing
'   end if
'   set db = nothing
   Exit Function 'or Exit Sub

Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
     & "   ProcedureName"   '--------- CUSTOMIZE

   Resume Proc_Exit
   Resume
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set Resume to be the next statement (right-click and choose Set Next Statement)
   Resume 'press F8 to execute one statement at a time
         ' -- the next one will be what caused the error)

On Error GoTo 0
means go back to the default of having no error handler

how can you remember this?
0 = False
= Cancel any previous plan that might have been in place
Go to false? What? There is no plan ... if something unexpected happens -- panic! Do whatever comes to your mind in the emergency. In programming, just as real life, it is better to have a plan in place .. but you need some way to cancel a possible previous plan and announce to Access that you have no plan (there are exceptions)

On Error ... anything else with accepted syntax
tells Access what to do if an error happens

On Error Resume Next
says two things:
   1. ignore the statement with the problem
   2. resume with the next statement
(the door you are approaching is on fire, try the next one)
-- so there is an error handler (instructions for what to do)

What is an error handler?
It is an emergency plan.

Think about:
-- fire drills in grade school
-- the diagram of the plane and all the exits when you fly
-- lighted Exit signs in crowded places
-- storm cellars
-- run-away exit ramps
... a plan in case something goes wrong

What happens when there is no plan? The value of global variables can be lost (thanks for that knowledge, Walter). Who knows what else... what happens with panic?

On Error GoTo 0
= No Plan

On Error ... [acceptable alternate syntax]

acts as a detour sign to tell Access what to do if an error happens. If the next thing is 0 (zero), that means nothing is specified so the default behaviour will be assumed (no error handling)

If you want to handle the error, you have other choices. For instance...

At the top of your program, right after the procedure declaration (skip a line first for better readability), tell Access there is a plan in place.
(If the car breaks down, the mechanic's number to call is specified)

then come the statements of your procedure

then the lines at the bottom -- be sure to replace ProcedureName

This is my "shell" error handling code:

'~~~~~~~~~~~~~~~~~~~~~~
   'set up Error Handler
   On Error GoTo Proc_Err
'~~~~~~~~~~~~~~~~~~~~~~

'   ... then your statements

'put this at the end of the procedure

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
   On Error Resume Next
   'release object variables if applicable-- ie:
'   if Not rs is Nothing then
'      rs.close
'      set rs = Nothing
'   end if
'   set db = nothing
   Exit Function 'or Exit Sub

Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
     & "   ProcedureName"

   Resume Proc_Exit

   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set Resume to be the next statement (right-click and choose Set Next Statement)
         ' -- the next one will be what caused the error)
   Resume 'press F8 to execute one statement at a time

'~~~~~~~~~~~~~~~~~~~~~

Proc_Err:
   MsgBox Err.Description, , _
     "ERROR " & Err.Number _
     & "   ProcedureName"

   Resume Proc_Exit

   'if you want to single-step code to find error,
   '   CTRL-Break at MsgBox, Debug
   '   then set Resume to be the next statement
   'right-click on 'Resume' and
   ' choose 'Set Next Statement' from shortcut menu
   Resume
   ' then press F8 to execute one line at a time
   ' so you can fix problem
'~~~~~~~~~~~~~~~~~~~~~

WHERE
ProcedureName is the name of your procedure so you can identify what code the problem is in when you see the error

The line labels do not matter (Proc_Exit:, Proc_Err:), I like to use the same ones all the time -- they only have to be unique within a procedure.

if you get an error, press CTRL-BREAK when the message box pops up,
click Debug to temporary halt processing and go into the code

right-click on the *Resume* statement
from the shortcut menu, choose --> Set Next Statement

then press F8 to resume with the statement that caused the problem -- you can fix it! -- or at least see what the problem is

pressing F8 executes one statement at a time

press F5 to continue execution automatically

~~~
While I am developing, I like to make the error handler go to the line that caused the problem so I can see where it is. Resume goes back to the offending line. When code Stops, press F8 to execute one statement at a time.

_____________________________________
if at any time, you want to instruct Access to skip the error and the handler code, your handler can be to Resume the Next Statement ... or to go to false -- whatever would just happen when there is no plan ...

Error handling is the same in Excel and Access ~

warm regards,
crystal

~ have an awesome day ~

On 1/28/2016 3:15 AM, 'Bob Phillips' bob.phillips@dsl.pipex.com [MS_Access_Professionals] wrote:

1) Lots of things can be wrong, without knowing the data and seeing the code we can only speculate

2) This is an Access forum, not Excel. Try ExcelVBA@yahoogroups.com

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 27 January 2016 16:08
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Excel question

I am very new at Excel and am using Excel 2003. I am trying to generate a data entry form. I am quite sure my code is correct but I get a "Run-time error '1004" every time I try running it. What can be wrong?