Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 27th, 2004, 08:37 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default Error displayed with error trap code

Is there some library that handles errors?. Form some strange reason error messages are displayed even when I write error trapping codes.

One line codes as On Error Resume Next still display error


The code below still displays error and error number as 3144


Private Sub Form_Unload(Cancel As Integer)

    On Error GoTo Form_Err
    DoCmd.SetWarnings False
    strSQL = "UPDATE tblSystem SET tblSystem.BankID = " & Forms!frmTransactions!BankID
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

Exit_Form_Err:
    Exit Sub


Form_Err:

    Select Case Err.Number
        Case 3144
            Resume Exit_Form_Err
        Case Else
            msg = MsgBox(Err.Number)
            Resume Exit_Form_Err
    End Select

End Sub

Thanks for any suggestions
Reply With Quote
  #2 (permalink)  
Old February 27th, 2004, 09:57 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can learn a bit more about the error by using the description property and include it in the message. The err.Number is the default property of the error so it is the same as err. I'm not seeing the return from the MsgBox function dimensioned nor the strSql variable so it looks like you don't have option Explict turned on in the code behind the form. The cause of the error 'Syntax error in UPDATE statement.' that is reported could be because the control reference is invalid or there is a null in the control. You would be better served to set the cancel parameter true in the error handler and advise the user that the value in the control is invalid if it is null.

A good way to test the sql statement is to debug.print it in the error handler so you can paste it into the SQL view of a new query to see what the problem is.

In addition to showing the err.Description in the message, I like to insert the procedure name so it is easy for a user to report the error. I would also place any cleanup code in the Exit_Form_Err portion of the routine. Tyically this is where you would have cleared the hourglass set at the start of a longer running query as well as cleared all local object references (or as in my commented out example, reset the system progress bar). I like to use a resume 0 which you can uncomment when the code stops for an error. That causes the code to resume at the line that raised the error making it much easier to identify cause of the problem.

Exit_Form_Err:
    On Error Resume Next
    'SysCmd acSysCmdRemoveMeter or close and set to nothing, & _ cancel hourglass
    Exit Sub
Form_Err:
    With Err
        Select Case .Number
            Case 3144
                Resume Exit_Form_Err
            Case Else
                MsgBox .Number & vbCrLf & .Description, & _
                    vbInformation, "Error - Procedurename"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
Exit Sub

The best solution to your problem is not to use docmd to run an update query. Essentially that is code calling a macro to do what can be done and handled in code directly. Macros have no capability to trap error so Access provides the setwarnings connection to macro methods. If you use the Execute method of a database object, you get no errors and you don't need code to call a macro to turn warnings on and off either.

Private Sub Form_Unload(Cancel As Integer)

    Dim strSQL As String
    Dim db As Database

    On Error GoTo Form_Err
    Set db = CurrentDb
    strSQL = "UPDATE tblSystem SET tblSystem.BankID = " & Forms!frmTransactions!BankID
    db.Execute strSQL
    If Not(db.RecordsAffected) Then 'recordsaffected is a number_ 0 is false therefore failure
        If MsgBox("Database not updated. Check valid" & _
          "entry in 'textbox'. Do you wish to close the " & _
          "form anyway?", vbYesNo) = vbYes Then
        Else
            Cancel = True
        End If
Exit_Form_Err:
    On Error Resume Next
    db.Close
    Set db = Nothing
    Exit Sub

Form_Err:
    Select Case Err.Number
        'Case 3144 - no longer an issue, add others as required
        Case Else
            MsgBox .Number & vbCrLf & .Description, & _
                vbInformation, "Error - Procedurename"
    End Select
    'Resume 0
    Resume Exit_Form_Err
End Sub
Reply With Quote
  #3 (permalink)  
Old February 28th, 2004, 04:51 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

As well as the sound advice given above check in the options for code editing. Do you have 'Break on all errors' chosen instead of 'Break on unhandled errors'?

Joe

--

Joe
Reply With Quote
  #4 (permalink)  
Old February 28th, 2004, 01:24 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the insight Joe. I have Opiton Explicit set to on and I use msg as Global variabel in a module. That is why you did not see a Dim line.

I am wondering if I have to use DAO to run the procedure you gave me. If so, is it possible for you to rewrite it with ADO since I am trying to write all my apps with ADO. I am new to Access and since DAO is on its way out I did not go into learnind it.
Thanks
Reply With Quote
  #5 (permalink)  
Old February 28th, 2004, 02:53 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In ADO you don't have the CurrentDb function to return a database against which to run the .Execute method. Instead you use the .Execute method of a Connection object that you create and set pointing to the appropriate data provider and path to database. The online help is pretty clear.

Just for the record, Access still uses DAO internally for bound recordsets and list and combo box row sources. Things like Docmd and domain aggregate functions do also. Why? Because the ADO and ADOX are more generic in order to work against a broader range of data providers. All the extra weight and overhead of being able to work against various data providers adds a layer of translation code between Access and the backend data whereas DAO uses Jet natively which is the direct mdb data provider. ADO is like having a ballerina wear a pair of work boots over her slippers. She can go to a construction site with the boots, but her dancing on the stage suffers. If she's going to dance on a stage, she does better without the extra layer of indirection but the boots allow her to go to job sites where she may be able to do some work, but she's somewhat out of her element in those environments.

ADO adds some versatility because you can hit Oracle or SQL Server backends and these can provide data more efficiently, but Access is not always the best front end for industrial strength disconnected transaction based data applications. If you want to upgrade to those kinds of backends, you would be well advised to work with ADO. However, your typical Mom & Pop to 20 local user 50 Megabyte databases (or fewer user/less size) are going to have to give up some performance to use ADO.

Ciao
Jurgen Welz
Edmonton AB Canada
jwelz@hotmail.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trap error on Jpeg Image - unsafe array echovue Classic ASP Professional 2 April 24th, 2006 11:47 PM
this page can not be displayed "error" p2ptolu Classic ASP Databases 12 July 8th, 2005 12:04 PM
Terrible error: The page cannot be displayed csun ASP.NET 1.0 and 1.1 Basics 2 November 9th, 2004 10:16 AM
How can I trap for a connection error cdukes77@bellsouth.net Classic ASP Databases 1 April 27th, 2004 03:09 AM
Error Message: The page cannot be displayed BSkelding ASP.NET 1.0 and 1.1 Basics 2 April 23rd, 2004 11:27 AM



All times are GMT -4. The time now is 12:58 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.