On Error GoTo is not working

[JNariss]

Hello,

I have a form with the following code that is not working properly.
This form is created from one simple table called tblRequestPassword
with all fields being required. Fields are RequestID, Name, Reason, and
Date.

I was hoping someone could take a look and perhaps see what errors are
in my code. What I want it to do is to check that all fields have a
value and if so then execute the SendObject command. However, it is not
executing an error if any of the fields are left blank it just runs the
SendObject.


Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim strTo As String
Dim strRequestID As String
Dim strMessage As String
Dim strYourName As String
Dim strReason As String

strTo = "(e-mail address removed);[email protected]"
strRequestID = Me.RequestID
strYourName = Me.YourName
strReason = Me.Reason

strMessage = "A request for a password is needed to make the following
change. Please view the details below and contact this person with
further instructions." & Chr$(13) & Chr$(13) & _
"Request ID: " & strRequestID & Chr$(13) & Chr$(13) & _
"Name: " & strYourName & Chr$(13) & Chr$(13) & _
"Reason: " & strReason & Chr$(13) & Chr$(13) & _
"Please do not reply to this automated email."

DoCmd.SendObject acSendNoObject, , , strTo, , , "Password needed to
Edit a Request", strMessage, No, False
DoCmd.Close acForm, "frmRequestPassword"
MsgBox "Your request for a password has been sent. You will be
contacted with further instructions.", vbOKOnly, "Password Request
Sent"

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description & Chr$(13) & Chr$(13) & _
"Required fields for this form are: Request ID, Date, Name, and
Reason. If all the required fields are not filled in, your form will
not be submitted.", vbOKOnly
Resume Exit_Command9_Click

End Sub



Thank You,
Justine

 

[Guest]

It is not checking for required fields anywhere in your code. Your message
is not in the correct place. No error is generated because of a null field.
Below is a revision of your code that may (if I didn't make an error) correct
the problem:

Private Sub Command9_Click()
Dim strRequestID As String
Dim strTo As String
Dim strHeader As String
Dim strMessage As String
Dim strYourName As String
Dim strReason As String
Dim blnRequiredFieldIsMissing As Boolean

On Error GoTo Err_Command9_Click

If IsNull(Me.RequestID) Then
blnRequiredFieldIsMissing = True
Else
strRequestID = Me.RequestID
End If

If IsNull(Me.YourName) Then
blnRequiredFieldIsMissing = True
Else
strYourName = Me.YourName
End If

If IsNull(Me.Reason) Then
blnRequiredFieldIsMissing = True
Else
strReason = Me.Reason
End If

If blnRequiredFieldIsMissing Then
MsgBox "Required fields for this form are: Request ID, Date, Name,
and " _
& "Reason. If all the required fields are not filled in, your
form will " _
& "not be submitted.", vbOKOnly
Else
strTo = "(e-mail address removed);[email protected]"
strHeader = "Password needed to Edit a Request"
strMessage = "A request for a password is needed to make the
following " _
& "change. Please view the details below and contact this person
with " _
& "further instructions." & Chr$(13) & Chr$(13) _
& "Request ID: " & strRequestID & Chr$(13) & Chr$(13) _
& "Name: " & strYourName & Chr$(13) & Chr$(13) _
& "Reason: " & strReason & Chr$(13) & Chr$(13) _
& "Please do not reply to this automated email."

DoCmd.SendObject acSendNoObject, , , strTo, , , _
strHeader, strMessage, No, False

DoCmd.Close acForm, "frmRequestPassword"

MsgBox "Your request for a password has been sent. You will be " _
& "contacted with further instructions.", vbOKOnly, _
& "Password Request Sent"
End If

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:

MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

Also, notice the difference in readability of the code above. Proper
indentation and white space makes it much easier for us humans to read, but
the computer doesn't care.

 

[OfficeDev18 via AccessMonster.com]

Hello, Justine,

That's because On Error only looks for VBA errors, and the error you describe
is a procedural,data, or logic error. This type of error cannot be trapped
with On Error.

What you need to do is to assign a user-defined message to the user if a
certain condition exists, such as no data, etc. You can do this with the
MsgBox statement.

Hope this helps,

Sam

 

[JNariss]

Wow...........this is great!!! I truly thank you for taking the time to
help me out here. I will copy and paste the code and see what happens
from there. I will also take your advice on indenting and white spaces
within my codes.

-Justine

 

[Guest]

Before running that code, be sure you are wearing safety goggles and hearing
protection

 

[BruceM]

Just curious as to what Chr$(13) & Chr$(13). I would have thought it is two
carriage returns in succession.

 

[Guest]

It is. I wondered why he is doing that, I guess he wants to skip a line so
it displays like:

Some Stuff On this Line

More Stuff After a Blank Line

 

[JNariss]

Bruce,

The Chr code enters a line in between my code. So when the user
receives an email it will look like this:

Request ID: 500

Name: Betty White

Reason: To read this easier


-------------------------------------------------------------------------------------------------->>>>>

Klatuu,

I tried to the code and had to fix up a couple errors on the messages
boxes. However, I am now getting an error which states:

"with object must be user-defined type, Object, or Variant."

This then ofcourse points to the with " _ line in the code. The code I
am using is:

Private Sub Command9_Click()
Dim strRequestID As String
Dim strTo As String
Dim strHeader As String
Dim strMessage As String
Dim strYourName As String
Dim strReason As String
Dim blnRequiredFieldIsMissing As Boolean
On Error GoTo Err_Command9_Click
If IsNull(Me.RequestID) Then
blnRequiredFieldIsMissing = True
Else
strRequestID = Me.RequestID
End If
If IsNull(Me.YourName) Then
blnRequiredFieldIsMissing = True
Else
strYourName = Me.YourName
End If
If IsNull(Me.Reason) Then
blnRequiredFieldIsMissing = True
Else
strReason = Me.Reason
End If
If blnRequiredFieldIsMissing Then
MsgBox "Required fields for this form are: Request ID, Date,
Name, and " _
& "Reason. If all the required fields are not filled in,
your form will " _
& "not be submitted.", vbOKOnly
Else
strTo = "(e-mail address removed);[email protected]"
strHeader = "Password needed to Edit a Request"
strMessage = "A request for a password is needed to make the
following " _
& "change. Please view the details below and contact this
person "
With "" _
& "further instructions." & Chr$(13) & Chr$(13) _
& "Request ID: " & strRequestID & Chr$(13) & Chr$(13) _
& "Name: " & strYourName & Chr$(13) & Chr$(13) _
& "Reason: " & strReason & Chr$(13) & Chr$(13) _
& "Please do not reply to this automated email."
DoCmd.SendObject acSendNoObject, , , strTo, , , _
strHeader, strMessage, No, False
DoCmd.Close acForm, "frmRequestPassword"
MsgBox "Your request for a password has been sent. You will be
" _
& "contacted with further instructions.", vbOKOnly,
"Password Request Sent"
End If
Exit_Command9_Click:
Exit Sub
Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click
End Sub



If anyone knows how to correct this problem, I would appreciate it.

Thanks,
Justine

 

[BruceM]

Does it work? Usually you would use Chr(10) & Chr(13) (carriage return and
line feed, in that order) to skip to the next line. Maybe using Chr(13)
twice eliminates the need for Chr(10). No matter. I just wondered about
the unusual syntax. If it works, it works. vbCrLf accomplishes the same
thing in VBA as does Chr(13) & Chr(10), by the way, although you can't use
it in an expression.

 

[JNariss]

I can't believe I did not spot the problem in the code with the "with".
It just had to be backspaced to the above line. hahaha

Bruce,

The generated email looks like this:

A request for a password is needed to make the following change. Please
view the details below and contact this person with further
instructions.

Request ID: 580

Name:

Reason: This is a test to see if this works

Please do not reply to this automated email.

__________________________________________________________________________________

To be honest with you, I do this b/c some of the people where I work
don't have very good eye-sight and it is much easier for them to view
the details when they are spaced out.

Also, notice on the code how the information next to Name: is blank.
The code let me generate the email even though the name field was
blank. However, when the RequestID field was blank, it would not let
the email go through. So needless to say, I will be checking out the
code again.

 

[BruceM]

Please understand that I am not asking how to add an empty line to a text
string. I understand that you used Chr(13) twice to insert an empty line.
My question was about a technical detail. I have read from quite a few
sources that the combination Chr(13) & Chr(10) is needed to go to a new
line, and that vbCrLf is an easier way of accomplishing that in VBA.
However, I have finally done some testing, and I can't see any difference
between using vbCrLf and using either Chr(13) or Chr(10). I'm not saying
that there is no difference, only that I cannot discover one. Chr(13) &
Chr(10), in that order, is definitely needed in an expression, but VBA seems
to be less demanding in that way. If nobody jumps into this thread with an
explanation I will start a new thread, since I'm pretty curious now.

In what way does the e-mail not go through if RequestID is blank? Is there
an error message?

 

[Douglas J Steele]

Bruce: It makes a difference in certain places.

For instance, if you're populating a field in a table and you want line
breaks, you must use Chr$(13) & Chr$(10) or vbCrLf. If you don't, you'll get
unprintable squares in the text rather than line breaks.

Other applications aren't as demanding as Access in this regard, so you can
get away with just Chr(13). And even within Access, you can use only Chr(13)
in message boxes and it'll work.

Isn't consistency wonderful? <g>

 

[BruceM]

Thanks for the reply, Doug. Fortunately I find it simpler to type vbcrlf
(and let VBA format it as vbCrLf) than to type Chr(13) or Chr$(13), so I'll
just keep doing what I have been, but it was puzzling. But then, I can't
imagine why I expected consistency, considering the source. <g>