Solved: Error handling

[Ryanr]

Hello there! I am a newbie to coding in access, and I am trying to make a simple DB. I am having major problems when it comes to error handling on one of the tables. It was working perfectly fine, and then after fiddling around with other parts of my DB, I seem to have completely buggered it. Here is my current code


On Error GoTo err_error1
If Dealt_ = False Then Text5 = "Your problem is still being dealt with"
If Dealt_ = True Then Text5 = "Your problem has been dealt with"

err_error1:
MsgBox ("The ID number cannot be found. Please make sure that it is valid")
DoCmd.Close
Exit Sub
End Sub


Basically, this code is in a form, and it uses a query to search for an ID number, and different messages will be returned depending on whether the checkbox has been ticked or not, however, the error code stops the query from searching for valid ID numbers, while it lets the user search for ID numbers that don't exist, and therefore it returns an error code, which is the complete opposite of what should be happening!

Thank you in advance!

EDIT: Oh it works perfectly fine if I remove the error catching code, but of course I would like this to catch the errors that occur when no numbers are entered

EDITEDIT: Now it catches errors, but it's still not letting me search for anything valid! I am using access 2003

[orange]

Hello there! I am a newbie to coding in access, and I am trying to make a simple DB. I am having major problems when it comes to error handling on one of the tables. It was working perfectly fine, and then after fiddling around with other parts of my DB, I seem to have completely buggered it. Here is my current code


On Error GoTo err_error1
If Dealt_ = False Then Text5 = "Your problem is still being dealt with"
If Dealt_ = True Then Text5 = "Your problem has been dealt with"

err_error1:
MsgBox ("The ID number cannot be found. Please make sure that it is valid")
DoCmd.Close
Exit Sub
End Sub

Basically, this code is in a form, and it uses a query to search for an ID number, and different messages will be returned depending on whether the checkbox has been ticked or not, however, the error code stops the query from searching for valid ID numbers, while it lets the user search for ID numbers that don't exist, and therefore it returns an error code, which is the complete opposite of what should be happening!

Thank you in advance!

EDIT: Oh it works perfectly fine if I remove the error catching code, but of course I would like this to catch the errors that occur when no numbers are entered

EDITEDIT: Now it catches errors, but it's still not letting me search for anything valid!
Here is a link that explains error handling with some examples.
http://allenbrowne.com/ser-23b.html

[Ryanr]

Of course I can :) There's not much too it though as this is meant to be a one function form

Option Compare Database
Private Sub Close_Click()
On Error GoTo Err_Close_Click

DoCmd.Close
Exit_Close_Click:
Exit Sub
Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub
Private Sub Form_Load()
On Error GoTo err_error1
If Dealt_ = False Then Text5 = "Your problem is still being dealt with"
If Dealt_ = True Then Text5 = "Your problem has been dealt with"
err_error1:
MsgBox ("The ID number cannot be found. Please make sure that it is valid")
DoCmd.Close
Exit Sub
End Sub

[orange]

Of course I can :) There's not much too it though as this is meant to be a one function form

Option Compare Database
Private Sub Close_Click()
On Error GoTo Err_Close_Click

DoCmd.Close
Exit_Close_Click:
Exit Sub
Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub
Private Sub Form_Load()
On Error GoTo err_error1
If Dealt_ = False Then Text5 = "Your problem is still being dealt with"
If Dealt_ = True Then Text5 = "Your problem has been dealt with"
err_error1:
MsgBox ("The ID number cannot be found. Please make sure that it is valid")
DoCmd.Close
Exit Sub
End Sub
You responded while I was editing.
Take a look at Allen's example. Notice how the code is structured.

As for your code, I don't know what Dealt_ refers to.
If Text5 is a text box on your form, you should refer to it by
Me.Text5

Your If may be easier to read using:


If Dealt_ = False Then
Text5 = "Your problem is still being dealt with"
Else
Text5 = "Your problem has been dealt with"
end if
You might want to use Option Explicit in your
Option Compare Database. This will identify unDimmed variables and spelling mistakes.

Here are a couple of useful Access links
http://www.allenbrowne.com/casu-22.html Crystal
http://www.fontstuff.com/siteindex.htm#access Martin Green
http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx Bob Larson

[Ryanr]

Well what do you know.. I followed Allens example and it worked! I do not know where I went wrong to be honest though.. I first cleaned up my code as you said, and then I used Allens example as a guideline, heres my code

Private Sub Form_Load()

On Error GoTo err_error1
If Dealt_ = False Then
Me.Text5 = "Your problem is still being dealt with"
Else
Me.Text5 = "Your problem has been dealt with"
End If

Exit_Id:
Exit Sub

err_error1:
MsgBox Err & " " & Error$
Resume Exit_Id
Exit Sub
End Sub

Could you possibly offer me any insight as to why my example failed?
- 'Dealt' refers to a checkbox

Also, although it regonizes what is the error now, my form still opens onto a blank page, what is the correct method for shutting it off?

Thank you so much by the way, this was stressing me out for like an hour haha :)

[orange]

Well what do you know.. I followed Allens example and it worked! I do not know where I went wrong to be honest though.. I first cleaned up my code as you said, and then I used Allens example as a guideline, heres my code

Private Sub Form_Load()

On Error GoTo err_error1
If Dealt_ = False Then
Me.Text5 = "Your problem is still being dealt with"
Else
Me.Text5 = "Your problem has been dealt with"
End If

Exit_Id:
Exit Sub

err_error1:
MsgBox Err & " " & Error$
Resume Exit_Id
Exit Sub
End Sub
Could you possibly offer me any insight as to why my example failed?
- 'Dealt' refers to a checkbox

Also, although it regonizes what is the error now, my form still opens onto a blank page, what is the correct method for shutting it off?

Thank you so much by the way, this was stressing me out for like an hour haha :)

Glad you got it working.
As for the blank page, what is the recordsource of the form?

What makes you think it should open to a specific record?
What is the related Table or query?

[Ryanr]

Ah the recordsource is a query, and when the query cannot return a value, it just returns an empty table, so I'm guessing this is why it returns a blank table :)

Ok another problem to bug you all with... I'm trying to create an IF statement, so basically, if some textboxes still haven't been filled in, then messageboxes will pop up telling the user that they haven't filled in the textboxes, and the process should cancel, however, while the textboxes pop up, the process still continues, while this is a minor issue for my database, I would like to learn for future reference

Private Sub Add_New_Problem_Click()
Dim answer As Integer
If IsNull(Name_of_requestee) Then MsgBox ("Please fill in the form fully!")

If IsNull(Department_of_work) Then MsgBox ("Please fill in the form fully!")

If IsNull(Problem) Then MsgBox ("Please fill in the form fully!")
If IsNull(Combo39) Then MsgBox ("Please fill in the form fully! You need to select the issue type") Else
answer = MsgBox("Do you wish to send this issue?", vbYesNo)

If answer = vbYes Then
MsgBox ("Your problem has been recorded! Please not that your Id number is " & Me.Text35)
DoCmd.GoToRecord , , acNewRec
Problem.Text = "Please enter your problem"
If answer = vbNo Then MsgBox ("Not sent!")
End If
Exit_Command12_Click:
Exit Sub
Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click
End Sub


Thank you in advance :)

[geekgirlau]

Private Sub Add_New_Problem_Click()
Select Case True
' check the required fields
Case IsNull(Name_of_requestee), _
IsNull(Department_of_work), _
IsNull(Problem), _
IsNull(Combo39)

MsgBox ("Please fill in the form fully!")

' code will only get to here if the required fields are all ok
Case Else
' don't need a variable here - you can directly test the answer
If vbYes = MsgBox("Do you wish to send this issue?", vbYesNo) Then
MsgBox ("Your problem has been recorded! Please note that your Id number is " & _
Me.Text35)
DoCmd.GoToRecord , , acNewRec
Problem.Text = "Please enter your problem"
Else
MsgBox ("Not sent!")
End If
End Select


Exit_Command12_Click:
Exit Sub
Err_Command12_Click:
MsgBox Err.Description
Resume Exit_Command12_Click
End Sub


I would recommend that you NEVER use If on a single line - it's far too easy to get confused over the logic. You need a clear path for the different choices available. If you spell it out as I've done in this example, you can easily see what's going to happen in different situations.

You can also see that I've used Select Case rather than If. This looks at whether any of those controls have a null value, and shows the same error message. None of the remaining code will be activated unless the required fields are all filled.

[Ryanr]

Ah thank you so much! I can now see what I have done wrong :)

'Case' is new to me, and it definately looks like it will be useful so thank you guys!

[Imdabaum]

On Error GoTo err_error1
If Dealt_ = False Then Text5 = "Your problem is still being dealt with"
If Dealt_ = True Then Text5 = "Your problem has been dealt with"

err_error1:
MsgBox ("The ID number cannot be found. Please make sure that it is valid")
DoCmd.Close
Exit Sub
End Sub


I didn't notice anyone point this out, but this code will always error out, regardless of whether your values are null. As the next line to process after If Dealt_ = True Then Text5=blahblahblah is the error handling code.

I noticed you corrected it in later posts, and were asking what was different. If the code actually changed corresponding to your posts. That's what changed. If the post just corrected the code that existed in your mdb. Disregard my post.


Sub Blah()
Do code

succeed:
do code for clean run
Exit Sub ' or function
Err_Handling:
do code to handle the error.
End Sub

[Ryanr]

Ah yes thank you for pointing that out :) That is where I went wrong, and I like to think I can now handle any errors effectively :)