If IsNull...And...= Then Help!

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I am trying to us If IsNull to create requirements on a form that need
to be fulfilled, but
I don't think I have the "language" quite right. Can anyone help me
correct this?

Here is what I have so far:

Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
End If
If Len([CountermeasureDate] & "") < (RequestDate) Then
MsgBox "The Countermeasure Date must be greater than the
Request Date."
Exit Sub
End If
If IsNull(BodyNumber) And (Status) = "Closed-OK" Then
MsgBox "You must input a Body Number before you can close this
Countermeasure."
Exit Sub
End If
If IsNull(Countermeasure) And (Status) = "Closed-OK" Then
MsgBox "You must indicate Countermeasure Details before you
can close this Countermeasure."
Exit Sub
End If
SubmitCM
End Sub

Thank you!
 
Opal said:
I am trying to us If IsNull to create requirements on a form that need
to be fulfilled, but
I don't think I have the "language" quite right. Can anyone help me
correct this?

Here is what I have so far:

Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
End If
If Len([CountermeasureDate] & "") < (RequestDate) Then
MsgBox "The Countermeasure Date must be greater than the
Request Date."
Exit Sub
End If
If IsNull(BodyNumber) And (Status) = "Closed-OK" Then
MsgBox "You must input a Body Number before you can close this
Countermeasure."
Exit Sub
End If
If IsNull(Countermeasure) And (Status) = "Closed-OK" Then
MsgBox "You must indicate Countermeasure Details before you
can close this Countermeasure."
Exit Sub
End If
SubmitCM
End Sub
Click to expand...


It's difficult to guess what code you might need without
knoing what you want the code to do.

Hazarding a couple of guesses, I think the first If were
nore like:
If IsNull(CountermeasureDate) Then

The second If is clearly wrong because comparing length of
any string to a date just doesn't make sense. Based on the
message, it seems like it should be:
If CountermeasureDate < RequestDate Then
 
try

Private Sub SubmitFitConcern_Click()

If Len(Me!CountermeasureDate & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
ElseIf Me!CountermeasureDate < Me!RequestDate Then
MsgBox "The Countermeasure Date must be greater " _
& "than the Request Date."
Exit Sub
ElseIf Me!Status = "Closed-OK" Then
If IsNull(Me!BodyNumber) Then
MsgBox "You must input a Body Number " _
& "before you can close this Countermeasure."
Exit Sub
ElseIf IsNull(Me!Countermeasure) Then
MsgBox "You must indicate Countermeasure Details " _
& "before you can close this Countermeasure."
Exit Sub
End If
End If

SubmitCM

End Sub

hth
 
try

Private Sub SubmitFitConcern_Click()

If Len(Me!CountermeasureDate & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
ElseIf Me!CountermeasureDate < Me!RequestDate Then
MsgBox "The Countermeasure Date must be greater " _
& "than the Request Date."
Exit Sub
ElseIf Me!Status = "Closed-OK" Then
If IsNull(Me!BodyNumber) Then
MsgBox "You must input a Body Number " _
& "before you can close this Countermeasure."
Exit Sub
ElseIf IsNull(Me!Countermeasure) Then
MsgBox "You must indicate Countermeasure Details " _
& "before you can close this Countermeasure."
Exit Sub
End If
End If

SubmitCM

End Sub

hth




I am trying to us If IsNull to create requirements on a form that need
to be fulfilled, but
I don't think I have the "language" quite right. Can anyone help me
correct this?
Click to expand...
Here is what I have so far:
Click to expand...
Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
End If
If Len([CountermeasureDate] & "") < (RequestDate) Then
MsgBox "The Countermeasure Date must be greater than the
Request Date."
Exit Sub
End If
If IsNull(BodyNumber) And (Status) = "Closed-OK" Then
MsgBox "You must input a Body Number before you can close this
Countermeasure."
Exit Sub
End If
If IsNull(Countermeasure) And (Status) = "Closed-OK" Then
MsgBox "You must indicate Countermeasure Details before you
can close this Countermeasure."
Exit Sub
End If
SubmitCM
End Sub
Click to expand...
Thank you!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you tina,

....but I still have a problem.....when I inserted the code into the
VBA editor
for the form and tested it, it did not recognize that the
Countermeasure
or BodyNumber boxes were empty or "null"....
 
first, are you sure the code is even getting that far? double check the
Status string in the code to make sure it matches *exactly* what will be
entered in the Status control on the form. if the Status control is a
combobox control, the stored values may be numbers, not text, so double
check that as well.

if the Status value checks out okay, suggest you replace the two lines of
code

If IsNull(Me!BodyNumber) Then

and

ElseIf IsNull(Me!Countermeasure) Then

with

If Len(Me!BodyNumber & "") = 0 Then

and

ElseIf Len(Me!Countermeasure & "") = 0 Then

though it's generally better to set the AllowZeroLengthString property to
No, in every field in your table that is a Text datatype.

hth


Opal said:
try

Private Sub SubmitFitConcern_Click()

If Len(Me!CountermeasureDate & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
ElseIf Me!CountermeasureDate < Me!RequestDate Then
MsgBox "The Countermeasure Date must be greater " _
& "than the Request Date."
Exit Sub
ElseIf Me!Status = "Closed-OK" Then
If IsNull(Me!BodyNumber) Then
MsgBox "You must input a Body Number " _
& "before you can close this Countermeasure."
Exit Sub
ElseIf IsNull(Me!Countermeasure) Then
MsgBox "You must indicate Countermeasure Details " _
& "before you can close this Countermeasure."
Exit Sub
End If
End If

SubmitCM

End Sub

hth




I am trying to us If IsNull to create requirements on a form that need
to be fulfilled, but
I don't think I have the "language" quite right. Can anyone help me
correct this?
Click to expand...
Here is what I have so far:
Click to expand...
Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
End If
If Len([CountermeasureDate] & "") < (RequestDate) Then
MsgBox "The Countermeasure Date must be greater than the
Request Date."
Exit Sub
End If
If IsNull(BodyNumber) And (Status) = "Closed-OK" Then
MsgBox "You must input a Body Number before you can close this
Countermeasure."
Exit Sub
End If
If IsNull(Countermeasure) And (Status) = "Closed-OK" Then
MsgBox "You must indicate Countermeasure Details before you
can close this Countermeasure."
Exit Sub
End If
SubmitCM
End Sub
Click to expand...
Thank you!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you tina,

...but I still have a problem.....when I inserted the code into the
VBA editor
for the form and tested it, it did not recognize that the
Countermeasure
or BodyNumber boxes were empty or "null"....
Click to expand...
 
first, are you sure the code is even getting that far? double check the
Status string in the code to make sure it matches *exactly* what will be
entered in the Status control on the form. if the Status control is a
combobox control, the stored values may be numbers, not text, so double
check that as well.

if the Status value checks out okay, suggest you replace the two lines of
code

If IsNull(Me!BodyNumber) Then

and

ElseIf IsNull(Me!Countermeasure) Then

with

If Len(Me!BodyNumber & "") = 0 Then

and

ElseIf Len(Me!Countermeasure & "") = 0 Then

though it's generally better to set the AllowZeroLengthString property to
No, in every field in your table that is a Text datatype.

hth




try
Private Sub SubmitFitConcern_Click()
If Len(Me!CountermeasureDate & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
ElseIf Me!CountermeasureDate < Me!RequestDate Then
MsgBox "The Countermeasure Date must be greater " _
& "than the Request Date."
Exit Sub
ElseIf Me!Status = "Closed-OK" Then
If IsNull(Me!BodyNumber) Then
MsgBox "You must input a Body Number " _
& "before you can close this Countermeasure."
Exit Sub
ElseIf IsNull(Me!Countermeasure) Then
MsgBox "You must indicate Countermeasure Details " _
& "before you can close this Countermeasure."
Exit Sub
End If
End If
SubmitCM
End Sub
hth

I am trying to us If IsNull to create requirements on a form that need
to be fulfilled, but
I don't think I have the "language" quite right. Can anyone help me
correct this?
Here is what I have so far:
Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
End If
If Len([CountermeasureDate] & "") < (RequestDate) Then
MsgBox "The Countermeasure Date must be greater than the
Request Date."
Exit Sub
End If
If IsNull(BodyNumber) And (Status) = "Closed-OK" Then
MsgBox "You must input a Body Number before you can close this
Countermeasure."
Exit Sub
End If
If IsNull(Countermeasure) And (Status) = "Closed-OK" Then
MsgBox "You must indicate Countermeasure Details before you
can close this Countermeasure."
Exit Sub
End If
SubmitCM
End Sub
Thank you!- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Thank you tina,
Click to expand...
...but I still have a problem.....when I inserted the code into the
VBA editor
for the form and tested it, it did not recognize that the
Countermeasure
or BodyNumber boxes were empty or "null"....- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you, again...the Status box is a combo box. The user has a
choice
of three values - "Open", "Closed-OK" and "Closed-NG" (NG=No good).
Does this make a difference?
 
first, are you sure the code is even getting that far? double check the
Status string in the code to make sure it matches *exactly* what will be
entered in the Status control on the form. if the Status control is a
combobox control, the stored values may be numbers, not text, so double
check that as well.
Click to expand...
if the Status value checks out okay, suggest you replace the two lines of
code
Click to expand...
If IsNull(Me!BodyNumber) Then

ElseIf IsNull(Me!Countermeasure) Then

If Len(Me!BodyNumber & "") = 0 Then

ElseIf Len(Me!Countermeasure & "") = 0 Then
Click to expand...
though it's generally better to set the AllowZeroLengthString property to
No, in every field in your table that is a Text datatype.
hth
Click to expand...

news:[email protected]...
Click to expand...
try
Private Sub SubmitFitConcern_Click()
If Len(Me!CountermeasureDate & "") = 0 Then
MsgBox "You must select a CountermeasureDate."
Exit Sub
ElseIf Me!CountermeasureDate < Me!RequestDate Then
MsgBox "The CountermeasureDatemust begreater" _
& "thanthe RequestDate."
Exit Sub
ElseIf Me!Status = "Closed-OK" Then
If IsNull(Me!BodyNumber) Then
MsgBox "You must input a Body Number " _
& "before you can close this Countermeasure."
Exit Sub
ElseIf IsNull(Me!Countermeasure) Then
MsgBox "You must indicate Countermeasure Details " _
& "before you can close this Countermeasure."
Exit Sub
End If
End If
SubmitCM
End Sub
hth

I am trying to us If IsNull to create requirements on a form that need
to be fulfilled, but
I don't think I have the "language" quite right. Can anyone help me
correct this?
Here is what I have so far:
Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a CountermeasureDate."
Exit Sub
End If
If Len([CountermeasureDate] & "") < (RequestDate) Then
MsgBox "The CountermeasureDatemust begreaterthanthe
RequestDate."
Exit Sub
End If
If IsNull(BodyNumber) And (Status) = "Closed-OK" Then
MsgBox "You must input a Body Number before you can close this
Countermeasure."
Exit Sub
End If
If IsNull(Countermeasure) And (Status) = "Closed-OK" Then
MsgBox "You must indicate Countermeasure Details before you
can close this Countermeasure."
Exit Sub
End If
SubmitCM
End Sub
Thank you!- Hide quoted text -
- Show quoted text -
Thank you tina,
...but I still have a problem.....when I inserted the code into the
VBA editor
for the form and tested it, it did not recognize that the
Countermeasure
or BodyNumber boxes were empty or "null"....- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Thank you, again...the Status box is a combo box. The user has a
choice
of three values - "Open", "Closed-OK" and "Closed-NG" (NG=No good).
Does this make a difference?- Hide quoted text -

- Show quoted text -
Click to expand...

Also, with respect to the zero length strings....for the
countermeasure field,
it could be left blank if the status remains open. The users may not
have
a countermeasure in place yet. I just want to ensure that they don't
attempt to close the file without a countermeasure or body number.
 
first, are you sure the code is even getting that far? double check the
Status string in the code to make sure it matches *exactly* what will be
entered in the Status control on the form. if the Status control is a
combobox control, the stored values may be numbers, not text, so double
check that as well.
Click to expand...
if the Status value checks out okay, suggest you replace the two lines of
code
Click to expand...
If IsNull(Me!BodyNumber) Then

ElseIf IsNull(Me!Countermeasure) Then

If Len(Me!BodyNumber & "") = 0 Then

ElseIf Len(Me!Countermeasure & "") = 0 Then
Click to expand...
though it's generally better to set the AllowZeroLengthString property to
No, in every field in your table that is a Text datatype.
hth
Click to expand...

news:[email protected]...
Click to expand...
try
Private Sub SubmitFitConcern_Click()
If Len(Me!CountermeasureDate & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
ElseIf Me!CountermeasureDate < Me!RequestDate Then
MsgBox "The Countermeasure Date must be greater " _
& "than the Request Date."
Exit Sub
ElseIf Me!Status = "Closed-OK" Then
If IsNull(Me!BodyNumber) Then
MsgBox "You must input a Body Number " _
& "before you can close this Countermeasure."
Exit Sub
ElseIf IsNull(Me!Countermeasure) Then
MsgBox "You must indicate Countermeasure Details " _
& "before you can close this Countermeasure."
Exit Sub
End If
End If
SubmitCM
End Sub
hth

I am trying to us If IsNull to create requirements on a form that need
to be fulfilled, but
I don't think I have the "language" quite right. Can anyone help me
correct this?
Here is what I have so far:
Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
End If
If Len([CountermeasureDate] & "") < (RequestDate) Then
MsgBox "The Countermeasure Date must be greater than the
Request Date."
Exit Sub
End If
If IsNull(BodyNumber) And (Status) = "Closed-OK" Then
MsgBox "You must input a Body Number before you can close this
Countermeasure."
Exit Sub
End If
If IsNull(Countermeasure) And (Status) = "Closed-OK" Then
MsgBox "You must indicate Countermeasure Details before you
can close this Countermeasure."
Exit Sub
End If
SubmitCM
End Sub
Thank you!- Hide quoted text -
- Show quoted text -
Thank you tina,
...but I still have a problem.....when I inserted the code into the
VBA editor
for the form and tested it, it did not recognize that the
Countermeasure
or BodyNumber boxes were empty or "null"....- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Thank you, again...the Status box is a combo box. The user has a
choice
of three values - "Open", "Closed-OK" and "Closed-NG" (NG=No good).
Does this make a difference?- Hide quoted text -

- Show quoted text -
Click to expand...

Going through the debugger ....

Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
End If
If [CountermeasureDate] < (RequestDate) Then
MsgBox "The Countermeasure Date must be greater than the
Request Date."
Exit Sub
End If
If [Status] = "Closed-OK" Then

gets to here and then jumps down to:

End If
SubmitCM
End Sub

completely missing:

If IsNull(BodyNumber) Then
MsgBox "You must input a Body Number before you can close
this Countermeasure."
Exit Sub
ElseIf IsNull(Countermeasure) Then
MsgBox "You must indicate Countermeasure Details before
you can close this Countermeasure."
Exit Sub
End If

......so, something is missing here.....but I'm not sure what.....
 
first, are you sure the code is even getting that far? double check the
Status string in the code to make sure it matches *exactly* what will be
entered in the Status control on the form. if the Status control is a
combobox control, the stored values may be numbers, not text, so double
check that as well.
if the Status value checks out okay, suggest you replace the two lines of
code
If IsNull(Me!BodyNumber) Then
and
ElseIf IsNull(Me!Countermeasure) Then
with
If Len(Me!BodyNumber & "") = 0 Then
and
ElseIf Len(Me!Countermeasure & "") = 0 Then
though it's generally better to set the AllowZeroLengthString property to
No, in every field in your table that is a Text datatype.
hth

try
Private Sub SubmitFitConcern_Click()
If Len(Me!CountermeasureDate & "") = 0 Then
MsgBox "You must select a CountermeasureDate."
Exit Sub
ElseIf Me!CountermeasureDate < Me!RequestDate Then
MsgBox "The CountermeasureDatemust begreater" _
& "thanthe RequestDate."
Exit Sub
ElseIf Me!Status = "Closed-OK" Then
If IsNull(Me!BodyNumber) Then
MsgBox "You must input a Body Number " _
& "before you can close this Countermeasure."
Exit Sub
ElseIf IsNull(Me!Countermeasure) Then
MsgBox "You must indicate Countermeasure Details " _
& "before you can close this Countermeasure."
Exit Sub
End If
End If
SubmitCM
End Sub
hth

I am trying to us If IsNull to create requirements on a form that need
to be fulfilled, but
I don't think I have the "language" quite right. Can anyone help me
correct this?
Here is what I have so far:
Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a CountermeasureDate."
Exit Sub
End If
If Len([CountermeasureDate] & "") < (RequestDate) Then
MsgBox "The CountermeasureDatemust begreaterthanthe
RequestDate."
Exit Sub
End If
If IsNull(BodyNumber) And (Status) = "Closed-OK" Then
MsgBox "You must input a Body Number before you can close this
Countermeasure."
Exit Sub
End If
If IsNull(Countermeasure) And (Status) = "Closed-OK" Then
MsgBox "You must indicate Countermeasure Details before you
can close this Countermeasure."
Exit Sub
End If
SubmitCM
End Sub
Thank you!- Hide quoted text -
- Show quoted text -
Thank you tina,
...but I still have a problem.....when I inserted the code into the
VBA editor
for the form and tested it, it did not recognize that the
Countermeasure
or BodyNumber boxes were empty or "null"....- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
Thank you, again...the Status box is a combo box. The user has a
choice
of three values - "Open", "Closed-OK" and "Closed-NG" (NG=No good).
Does this make a difference?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

Going through the debugger ....

Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a CountermeasureDate."
Exit Sub
End If
If [CountermeasureDate] < (RequestDate) Then
MsgBox "The CountermeasureDatemust begreaterthanthe
RequestDate."
Exit Sub
End If
If [Status] = "Closed-OK" Then

gets to here and then jumps down to:

End If
SubmitCM
End Sub

completely missing:

If IsNull(BodyNumber) Then
MsgBox "You must input a Body Number before you can close
this Countermeasure."
Exit Sub
ElseIf IsNull(Countermeasure) Then
MsgBox "You must indicate Countermeasure Details before
you can close this Countermeasure."
Exit Sub
End If

.....so, something is missing here.....but I'm not sure what.....- Hide quoted text -

- Show quoted text -
Click to expand...

I keep trying the de-bugger.....

I changed the lower half of the code to:

If Len([BodyNumber] & "") = 0 Then
MsgBox "You must input a Body Number before you can close
this Countermeasure."
Exit Sub
ElseIf Len([Countermeasure] & "") = 0 Then
MsgBox "You must indicate Countermeasure Details before
you can close this Countermeasure."
Exit Sub
End If
End If
SubmitCM
End Sub


but I still can't get past:

ElseIf [Status] = "Closed - OK" Then

"Else without If" error.....
 
but I still can't get past:
ElseIf [Status] = "Closed - OK" Then

"Else without If" error.....
Click to expand...

this is the first time you've mentioned an error. and the code you posted
last is not the code i posted in my first response. suggest you copy/paste
the *entire* code i gave you - completely replacing the code you originally
posted - and run it, then post back with results.

hth
 
but I still can't get past:
Click to expand...
ElseIf [Status] = "Closed - OK" Then
Click to expand...
"Else without If" error.....
Click to expand...

this is the first time you've mentioned an error. and the code you posted
last is not the code i posted in my first response. suggest you copy/paste
the *entire* code i gave you - completely replacing the code you originally
posted - and run it, then post back with results.

hth
Click to expand...

This is the code right now:

Private Sub SubmitFitConcern_Click()
If Len([CountermeasureDate] & "") = 0 Then
MsgBox "You must select a Countermeasure Date."
Exit Sub
End If
If [CountermeasureDate] < (RequestDate) Then
MsgBox "The Countermeasure Date must be greater than the
Request Date."
Exit Sub
End If
ElseIf [Status] = "Closed - OK" Then
If Len([BodyNumber] & "") = 0 Then
MsgBox "You must input a Body Number before you can close
this Countermeasure."
Exit Sub
ElseIf Len([Countermeasure] & "") = 0 Then
MsgBox "You must indicate Countermeasure Details before
you can close this Countermeasure."
Exit Sub
End If
End If
SubmitCM
End Sub

When I tried to use the code you sent, it did not recognize that the
countermeasure date
had to be greater than the request date. I made changes to that line
of code so that it
would recoginze that it had to be greater than....It was after I
changed that line of code,
that I received the compile error, "Else without If". The rest of the
code is as per your
suggestion(s).
 
Hi Opal,

What was the value of "Status" when you ran the code? If the value is not
"Closed-OK" and if the "End If" is not nested correctly, it might cause this
line to jump away to another line.

Do a debug.print & Status to check the value or use a msgbox just before the
"If Then" line.

MsgBox "The value of Status is " & Status

End If
If [Status] = "Closed-OK" Then

gets to here and then jumps down to:

End If
SubmitCM
End Sub
.....so, something is missing here.....but I'm not sure what.....
Click to expand...
 
Hi Opal,

As suspected, there is a nested problem.
ElseIf [Status] = "Closed - OK" Then
Click to expand...

Change it to If [Status] = "Closed - OK" Then
If Len([BodyNumber] & "") = 0 Then
MsgBox "You must input a Body Number before you can close
this Countermeasure."
Exit Sub
ElseIf Len([Countermeasure] & "") = 0 Then
MsgBox "You must indicate Countermeasure Details before
you can close this Countermeasure."
Exit Sub
End If
End If
SubmitCM
End Sub
Click to expand...
 
Hi Opal,

As suspected, there is a nested problem.
ElseIf [Status] = "Closed - OK" Then
Click to expand...

Change it to If [Status] = "Closed - OK" Then
If Len([BodyNumber] & "") = 0 Then
MsgBox "You must input a Body Number before you can close
this Countermeasure."
Exit Sub
ElseIf Len([Countermeasure] & "") = 0 Then
MsgBox "You must indicate Countermeasure Details before
you can close this Countermeasure."
Exit Sub
End If
End If
SubmitCM
End Sub
Click to expand...
Click to expand...

Hi Access,

I tried that change last night. When I remove the "else" in front of:

ElseIf [Status] = "Closed - OK" Then

and run through the code. It highlights that line, (and yes, the
status
box does read "Closed - OK") and then jumps down to "End If" and
"SubmitCM" and then jumps to my SubmitCM Module. It completely
bypasses:

If Len([BodyNumber] & "") = 0 Then
MsgBox "You must input a Body Number before you can close
this Countermeasure."
Exit Sub
ElseIf Len([Countermeasure] & "") = 0 Then
MsgBox "You must indicate Countermeasure Details before
you can close this Countermeasure."
Exit Sub
End If
 
Hi Opal,
Click to expand...
As suspected, there is a nested problem.
Click to expand...
Opal said:
ElseIf [Status] = "Closed - OK" Then
Click to expand...
Click to expand...
Change it to If [Status] = "Closed - OK" Then
If Len([BodyNumber] & "") = 0 Then
MsgBox "You must input a Body Number before you can close
this Countermeasure."
Exit Sub
ElseIf Len([Countermeasure] & "") = 0 Then
MsgBox "You must indicate Countermeasure Details before
you can close this Countermeasure."
Exit Sub
End If
End If
SubmitCM
End Sub
Click to expand...
Click to expand...
Message posted viahttp://www.accessmonster.com
Click to expand...

Hi Access,

I tried that change last night. When I remove the "else" in front of:

ElseIf [Status] = "Closed - OK" Then

and run through the code. It highlights that line, (and yes, the
status
box does read "Closed - OK") and then jumps down to "End If" and
"SubmitCM" and then jumps to my SubmitCM Module. It completely
bypasses:

If Len([BodyNumber] & "") = 0 Then
MsgBox "You must input a Body Number before you can close
this Countermeasure."
Exit Sub
ElseIf Len([Countermeasure] & "") = 0 Then
MsgBox "You must indicate Countermeasure Details before
you can close this Countermeasure."
Exit Sub
End If- Hide quoted text -

- Show quoted text -
Click to expand...

Arghhh....Found it!

missing a space. The box had "Closed -OK" and the code was
looking for "Closed - OK"

Gotta stop working on this stuff when I have a cold and my head
is congested.

Thank you, Tina and Access for all your help!
 
Back
Top