I would like my database to simply display my custom error message, and preferably the error details below, or in another box or something, then just stop....no go on about Halting macros and all this caper!
Can anyone help get me out of my quandry of errors!?!
Okay, I have searched around a bit, and I think I need to error trap.......HELP!
I have been at this database for a long time, but have no idea how to error trap, can someone tell me how....I have little or no knowledge of writing VB code.
Here is an example of using error trapping and also trapping for two specific errors.
Code:
Private Sub bDeleteRecord_Click()
On Error GoTo bDeleteRecord_Click_Err
Beep
If MsgBox("Delete current record?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
bDeleteRecord_Click_Exit:
Exit Sub
bDeleteRecord_Click_Err:
If Err = 2046 Then 'The command or action 'DeleteRecord' isn't available now
Exit Sub
ElseIf Err = 2501 Then 'The RunCommand action was canceled
Exit Sub
Else
MsgBox Err.Number & " - " & Err.Description
Resume bDeleteRecord_Click_Exit
End If
End Sub
Something to bear in mind is that you cannot trap errors in macros.
If you wish to trap these errors you will have to convert your macros to VB.
Just to explain a little bit further the concept of Error handling;
When an Error occurs the 'Err' object is called.
The Err object contains information about the last Error that occured.
Each Error has a unique Error number
To check what the Error is you check the Err object's Error Number & Error Description
Code:
Err.Number 'Error Number
Err.Description 'Error Description
You decide what action to take based upon the Error Number.
I understand that this can be daunting if you don't have VBA knowledge.
If you post more detail about the errors & where they occur I'll write a handler for you.
GHudson, I tried your code, and it worked great...I am pretty sure I can adapt this for the other buttons at least.
My main problem is that i have several (about 10) forms, all with identical buttons on. Is there a way to make them all get their code from the same place? So I don't have to enter it everytime.
Also, is there some kind of reference for those error message numbers?
Could I ask someone to tell me what the VB is for the nav buttons (they are listed below), as I am currently using macros.
For the moment at least my problems are with standard nav buttons (first, previous, new, delete, next, last), an error about not having a field in another table that it needs, and not being able to find the backend.
Thanks for any help - trust me - I am VERY grateful!
Testing, testing and more testing of your forms, command buttons, functions and events
is the only way to find out what types of errors your users might encounter. All subs and
functions should have error trapping. Any decent programmer is going to attempt to trap
for all possible errors.
Here is the VBA for some of the standard buttons...
Here some code that will create a table detailing most (not all) common error numbers and
their descriptions. It is from Microsoft and I have made a few modifications to it.
Code:
'The following procedure creates a table containing many of the error codes and
'strings used or reserved by Microsoft Access and by the Microsoft Jet database
'engine. Not all error codes are included in the resulting table, as some exist
'outside the range of error codes evaluated by this procedure (0 to 4500).
Function AccessAndJetErrorsTable() As Boolean
On Error GoTo Err_AccessAndJetErrorsTable
Dim dbs As Database, tdf As TableDef, fld As Field
Dim rst As Recordset, lngCode As Long
Dim strAccessErr As String
Const conAppObjectError = "Application-defined or object-defined error"
' Delete tAccessAndJetErrors table if it already exists
If CurrentDb.OpenRecordset("SELECT 1 FROM MSysObjects WHERE Type=1 AND Flags=0 AND Name='tAccessAndJetErrors'").RecordCount = 1 Then DoCmd.DeleteObject acTable, "tAccessAndJetErrors"
' Create Errors table with ErrorNumber and ErrorDescription fields.
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("tAccessAndJetErrors")
Set fld = tdf.CreateField("ErrorCode", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("ErrorString", dbMemo)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
' Open recordset on Errors table.
Set rst = dbs.OpenRecordset("tAccessAndJetErrors")
' Loop through error codes.
For lngCode = 0 To 3500
On Error Resume Next
' Raise each error.
strAccessErr = AccessError(lngCode)
DoCmd.Hourglass True
' Skip error numbers without associated strings.
If strAccessErr <> "" Then
' Skip codes that generate application or object-defined errors.
If strAccessErr <> conAppObjectError Then
' Add each error code and string to Errors table.
rst.AddNew
rst!ErrorCode = lngCode
' Append string to memo field.
rst!ErrorString.AppendChunk strAccessErr
rst.Update
End If
End If
Next lngCode
' Close recordset.
rst.Close
DoCmd.Hourglass False
RefreshDatabaseWindow
Set dbs = Nothing
Set tdf = Nothing
Set fld = Nothing
Set rst = Nothing
Beep
MsgBox "Access and Jet errors table was created.", vbInformation, "tAccessAndJetErrors"
AccessAndJetErrorsTable = True
Exit_AccessAndJetErrorsTable:
Exit Function
Err_AccessAndJetErrorsTable:
Beep
MsgBox "Runtime Error # " & Err.Number & vbCrLf & vbLf & Err.Description
Resume Exit_AccessAndJetErrorsTable
End Function
How do you make that code make the table? I have put it into the VB editor thing in Access, and tried to make it run from a button, but that doesn't work, then tried a macro, but it didn't find any functions.
When I try to click Run in the VB editor, it just asks me for a macro to run?!?!
Private Sub NextButton_Click()
On Error GoTo NextButton_Click_Err
NextButton_Click_Exit:
Exit Sub
NextButton_Click_Err:
If Err = 2046 Then 'The command or action 'Next Record' isn't available now
Exit Sub
ElseIf Err = 2501 Then 'The RunCommand action was canceled
Exit Sub
Else
MsgBox Err.Number & " - " & Err.Description
Resume NextButton_Click_Exit
End If
Looks good except I did not see the actual command.
Code:
Private Sub NextButton_Click()
On Error GoTo NextButton_Click_Err
[COLOR=blue]DoCmd.GoToRecord , , acNext[/COLOR]
NextButton_Click_Exit:
Exit Sub
NextButton_Click_Err:
If Err = 2046 Then 'The command or action 'Next Record' isn't available now
Exit Sub
ElseIf Err = 2501 Then 'The RunCommand action was canceled
Exit Sub
Else
MsgBox Err.Number & " - " & Err.Description
Resume NextButton_Click_Exit
End If
End Sub
Thanks LOADS ghudson, that worked a treat.....I even figured out how it was done in the end (from yours!).
Anyway, my new question is, in that list, I can't seem to find anything about a missing backend. I would like to error trap the message that comes up when the db can't find it's backend.
This is how I test if the user has access to the directory that the back end is located on.
I use this in the OnOpen event of the main form.
Code:
If Dir("\\Server\Partition\Folder", vbDirectory) = "" Then
'If Dir("X:\Folder", vbDirectory) = "" Then
Beep
MsgBox "Your PC is not connected to the network or you are not authorized to access the X:\Database\ directory!", vbCritical, "Database Connection Error"
DoCmd.Quit acQuitSaveNone
Else
'MsgBox "OK - Network access to folder is verified"
End If
This is one way to test if the users computer can find the backend file.
Code:
If Dir("\\Server\Partition\Folder\BackendDB.mdb") = "" Then
'If Dir("X:\Folder\BackendDB.mdb") = "" Then
Beep
MsgBox "Your PC can not find the backend of the database!", vbCritical, "Database Connection Error"
DoCmd.Quit acQuitSaveNone
Else
'MsgBox "OK - found the backend file"
End If
I am sure that there is a way to test if the connection to the linked tables is OK
but I have never had to do that.
Is there a way of creating a list of A2000 error msg's?
Downloaded the above sample and it worked a treat but seemed to refer to A97 (Or are errors consistant across versions?) hevean help me - no - it is a Microsoft product
Dave
When I make a database I create a table primarily for collecting errors.
On every event I send the error details to a function that writes the information into the table with other stuff such as the date and time and the user who created the error.
This way I can look at my error log and find out which controls are causing which errors.
