Case Statements

[Guest]

I’m having difficulty with a case statement. I am validating that specific
fields in a form are filled out. Below is the one case statement that
doesn’t work When the code gets to this point, it apparently bypasses the
remainder of the case statements and saves the form. When I disable it, all
the other case statements work.

This piece of code was originally an IF, Then statement (shown at the
bottom) and worked. I want to incorporate that function with all the other
validation in the case statements. This bit looks at a number of check
boxes, as long as at least one is checked, then OK. Is there a way to do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[George Nicholson]

Hard to be sure what is wrong with your Case statement since you don't
include the Select Case statement.

But maybe something like the following will help:
Select Case True
Case (Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D],
0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) = 0
' No checkboxes are checked (all are Null or False).
MsgBox "At least One Commodity Type must be selected",
"Required Field:"
Case SomeOtherTestForTrue
'etc...
Case Else
' All validation tests have been passed. (Save the record?)
End Select

If you are using "Select Case False" then change "=0" to "<>0"

HTH,

 

[Douglas J. Steele]

Or

Select Case Case (Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) +
Nz(Me.[M D],
0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
Case 0
' No checkboxes are checked (all are Null or False).
MsgBox "At least One Commodity Type must be selected",
"Required Field:"
Case Else
' All validation tests have been passed. (Save the record?)
End Select


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hard to be sure what is wrong with your Case statement since you don't
include the Select Case statement.

But maybe something like the following will help:
Select Case True
Case (Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D],
0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) = 0
' No checkboxes are checked (all are Null or False).
MsgBox "At least One Commodity Type must be selected",
"Required Field:"
Case SomeOtherTestForTrue
'etc...
Case Else
' All validation tests have been passed. (Save the record?)
End Select

If you are using "Select Case False" then change "=0" to "<>0"

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I'm having difficulty with a case statement. I am validating that
specific
fields in a form are filled out. Below is the one case statement that
doesn't work When the code gets to this point, it apparently bypasses
the
remainder of the case statements and saves the form. When I disable it,
all
the other case statements work.

This piece of code was originally an IF, Then statement (shown at the
bottom) and worked. I want to incorporate that function with all the
other
validation in the case statements. This bit looks at a number of check
boxes, as long as at least one is checked, then OK. Is there a way to do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[George Nicholson]

Hi Doug!

Yours would also work, but I got the impression from the OP that he had
additional Cases:

...it apparently bypasses the remainder of the case statements ...
...I want to incorporate that function with all the other validation in
the case statements...

I was making a guess that he was using (or wanted to be using) Select Case
True. Hard to say without seeing more of the related code.

--
George Nicholson

Remove 'Junk' from return address.

Or

Select Case Case (Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) +
Nz(Me.[M D],
0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
Case 0
' No checkboxes are checked (all are Null or False).
MsgBox "At least One Commodity Type must be selected",
"Required Field:"
Case Else
' All validation tests have been passed. (Save the record?)
End Select


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hard to be sure what is wrong with your Case statement since you don't
include the Select Case statement.

But maybe something like the following will help:
Select Case True
Case (Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D],
0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) = 0
' No checkboxes are checked (all are Null or False).
MsgBox "At least One Commodity Type must be selected",
"Required Field:"
Case SomeOtherTestForTrue
'etc...
Case Else
' All validation tests have been passed. (Save the
record?)
End Select

If you are using "Select Case False" then change "=0" to "<>0"

HTH,
--
George Nicholson

Remove 'Junk' from return address.

I'm having difficulty with a case statement. I am validating that
specific
fields in a form are filled out. Below is the one case statement that
doesn't work When the code gets to this point, it apparently bypasses
the
remainder of the case statements and saves the form. When I disable it,
all
the other case statements work.

This piece of code was originally an IF, Then statement (shown at the
bottom) and worked. I want to incorporate that function with all the
other
validation in the case statements. This bit looks at a number of check
boxes, as long as at least one is checked, then OK. Is there a way to
do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[Guest]

I'm sorry I was unclear, I have 11 or so Select Case statements driven by
clicking a command button "save record". The code starts with
Select Case True
Case IsNull(control1)
Msgbox "bla bla bla"

Case IsNull(control2)
Msgbox "bla bla bla"

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"

Case IsNull (control4)
MsgBox "bla bla bla"

Case Else
DoCmd.Save
MsgBox "record saved"
Etc

The IsNull ones are simple and all work, it is the case where I assign '0'
to null values that doesn't work. Depending on what changes I make to that
case code trying to get it to work, it either;
1. apparently skips over the case (the remaining cases still work as they
should), so the code syntax is likely correct, but I've got it backwards
somewhere as it doesn't perform the requested task,
or
2. the application does not appear to do anything, literally it is as if I
did not push the command putton. The subsequent case statement should fire
because its control is still blank but it doesn't, and the record saves. It
doesn't freeze, I can click and keep going, but the case statement clearly
doesn't do what I need it to do.

I tried your suggestion George, inserting the = 0 at the end, but that got
me to scenario 2. I tried Douglas' suggestion inserting the Case 0, that
got me to scenario 1, the next case fired, completely skipping this one.
I've tried Case Is =0, <>0, >0, and others, none seem to work. I know I'm
close, but I just don't know Select Cases well enough to diagnose the
problem.

Thanks for your help.

I’m having difficulty with a case statement. I am validating that specific
fields in a form are filled out. Below is the one case statement that
doesn’t work When the code gets to this point, it apparently bypasses the
remainder of the case statements and saves the form. When I disable it, all
the other case statements work.

This piece of code was originally an IF, Then statement (shown at the
bottom) and worked. I want to incorporate that function with all the other
validation in the case statements. This bit looks at a number of check
boxes, as long as at least one is checked, then OK. Is there a way to do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[Douglas J Steele]

I'm not sure a Case statement is necessarily the best approach, as a Case
statement will stop at the first case that matches, and you could still have
other errors.

What I typically do is go through all the errors:

strMessage = vbNullString

If IsNull(control1) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If IsNull(control2) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D], 0) +
Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) Then
strMessage = strMessage & "At least One Commodity Type must be selected"
& vbCrLf
End If


If IsNull(control4) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage, vbOkOnly + vbError, "Errors"
Cancel = True
Else
MsgBox "Record Saved"
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry I was unclear, I have 11 or so Select Case statements driven by
clicking a command button "save record". The code starts with
Select Case True
Case IsNull(control1)
Msgbox "bla bla bla"

Case IsNull(control2)
Msgbox "bla bla bla"

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"

Case IsNull (control4)
MsgBox "bla bla bla"

Case Else
DoCmd.Save
MsgBox "record saved"
Etc

The IsNull ones are simple and all work, it is the case where I assign '0'
to null values that doesn't work. Depending on what changes I make to that
case code trying to get it to work, it either;
1. apparently skips over the case (the remaining cases still work as they
should), so the code syntax is likely correct, but I've got it backwards
somewhere as it doesn't perform the requested task,
or
2. the application does not appear to do anything, literally it is as if I
did not push the command putton. The subsequent case statement should fire
because its control is still blank but it doesn't, and the record saves. It
doesn't freeze, I can click and keep going, but the case statement clearly
doesn't do what I need it to do.

I tried your suggestion George, inserting the = 0 at the end, but that got
me to scenario 2. I tried Douglas' suggestion inserting the Case 0, that
got me to scenario 1, the next case fired, completely skipping this one.
I've tried Case Is =0, <>0, >0, and others, none seem to work. I know I'm
close, but I just don't know Select Cases well enough to diagnose the
problem.

Thanks for your help.

I'm having difficulty with a case statement. I am validating that specific
fields in a form are filled out. Below is the one case statement that
doesn't work When the code gets to this point, it apparently bypasses the
remainder of the case statements and saves the form. When I disable it, all
the other case statements work.

This piece of code was originally an IF, Then statement (shown at the
bottom) and worked. I want to incorporate that function with all the other
validation in the case statements. This bit looks at a number of check
boxes, as long as at least one is checked, then OK. Is there a way to do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[Guest]

Thanks Douglas
I removed the problem case, returned it to an IF, Then statement, and placed
it before the select case statements. I was hoping to have all my validation
integrated to make code edits easier and have a seamless presentation. In
effect they are still integrated except the If, Then now fires first which
may or may not be out of order (meaning the tab order). It is no longer a
functional problem, just a presentation issue that we were able to mitigate.
I told the users the field is very important, so it gets its own warning,
which is mostly true.

When I have the time, I'll dig into it and see if I can fix it. I'm going
to try your Strings rather than my message boxes too. I'm still learning
VBA, while I'm no longer "dangerous", I am not yet walking either.

Thanks again for your posts, they are very helpful.

I'm not sure a Case statement is necessarily the best approach, as a Case
statement will stop at the first case that matches, and you could still have
other errors.

What I typically do is go through all the errors:

strMessage = vbNullString

If IsNull(control1) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If IsNull(control2) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D], 0) +
Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) Then
strMessage = strMessage & "At least One Commodity Type must be selected"
& vbCrLf
End If


If IsNull(control4) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage, vbOkOnly + vbError, "Errors"
Cancel = True
Else
MsgBox "Record Saved"
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry I was unclear, I have 11 or so Select Case statements driven by
clicking a command button "save record". The code starts with
Select Case True
Case IsNull(control1)
Msgbox "bla bla bla"

Case IsNull(control2)
Msgbox "bla bla bla"

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"

Case IsNull (control4)
MsgBox "bla bla bla"

Case Else
DoCmd.Save
MsgBox "record saved"
Etc

The IsNull ones are simple and all work, it is the case where I assign '0'
to null values that doesn't work. Depending on what changes I make to that
case code trying to get it to work, it either;
1. apparently skips over the case (the remaining cases still work as they
should), so the code syntax is likely correct, but I've got it backwards
somewhere as it doesn't perform the requested task,
or
2. the application does not appear to do anything, literally it is as if I
did not push the command putton. The subsequent case statement should fire
because its control is still blank but it doesn't, and the record saves. It
doesn't freeze, I can click and keep going, but the case statement clearly
doesn't do what I need it to do.

I tried your suggestion George, inserting the = 0 at the end, but that got
me to scenario 2. I tried Douglas' suggestion inserting the Case 0, that
got me to scenario 1, the next case fired, completely skipping this one.
I've tried Case Is =0, <>0, >0, and others, none seem to work. I know I'm
close, but I just don't know Select Cases well enough to diagnose the
problem.

Thanks for your help.

I'm having difficulty with a case statement. I am validating that specific
fields in a form are filled out. Below is the one case statement that
doesn't work When the code gets to this point, it apparently bypasses the
remainder of the case statements and saves the form. When I disable it, all
the other case statements work.

This piece of code was originally an IF, Then statement (shown at the
bottom) and worked. I want to incorporate that function with all the other
validation in the case statements. This bit looks at a number of check
boxes, as long as at least one is checked, then OK. Is there a way to do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[Douglas J Steele]

It sounds as though your edit check has the potential of showing 0, 1 or 2
error messages. However, even if they receive 2 error messages, there might
still be additional errors: they go back, correct the error(s) that popped
up, and they'll receive yet another error message. I would think that would
be annoying.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas
I removed the problem case, returned it to an IF, Then statement, and placed
it before the select case statements. I was hoping to have all my validation
integrated to make code edits easier and have a seamless presentation. In
effect they are still integrated except the If, Then now fires first which
may or may not be out of order (meaning the tab order). It is no longer a
functional problem, just a presentation issue that we were able to mitigate.
I told the users the field is very important, so it gets its own warning,
which is mostly true.

When I have the time, I'll dig into it and see if I can fix it. I'm going
to try your Strings rather than my message boxes too. I'm still learning
VBA, while I'm no longer "dangerous", I am not yet walking either.

Thanks again for your posts, they are very helpful.

I'm not sure a Case statement is necessarily the best approach, as a Case
statement will stop at the first case that matches, and you could still have
other errors.

What I typically do is go through all the errors:

strMessage = vbNullString

If IsNull(control1) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If IsNull(control2) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D], 0) +
Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) Then
strMessage = strMessage & "At least One Commodity Type must be selected"
& vbCrLf
End If


If IsNull(control4) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage, vbOkOnly + vbError, "Errors"
Cancel = True
Else
MsgBox "Record Saved"
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry I was unclear, I have 11 or so Select Case statements driven by
clicking a command button "save record". The code starts with
Select Case True
Case IsNull(control1)
Msgbox "bla bla bla"

Case IsNull(control2)
Msgbox "bla bla bla"

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"

Case IsNull (control4)
MsgBox "bla bla bla"

Case Else
DoCmd.Save
MsgBox "record saved"
Etc

The IsNull ones are simple and all work, it is the case where I assign '0'
to null values that doesn't work. Depending on what changes I make to that
case code trying to get it to work, it either;
1. apparently skips over the case (the remaining cases still work as they
should), so the code syntax is likely correct, but I've got it backwards
somewhere as it doesn't perform the requested task,
or
2. the application does not appear to do anything, literally it is as if I
did not push the command putton. The subsequent case statement should fire
because its control is still blank but it doesn't, and the record

saves.
It

doesn't freeze, I can click and keep going, but the case statement clearly
doesn't do what I need it to do.

I tried your suggestion George, inserting the = 0 at the end, but that got
me to scenario 2. I tried Douglas' suggestion inserting the Case 0, that
got me to scenario 1, the next case fired, completely skipping this one.
I've tried Case Is =0, <>0, >0, and others, none seem to work. I

know
I'm

close, but I just don't know Select Cases well enough to diagnose the
problem.

Thanks for your help.

:

I'm having difficulty with a case statement. I am validating that specific
fields in a form are filled out. Below is the one case statement that
doesn't work When the code gets to this point, it apparently

bypasses
the

remainder of the case statements and saves the form. When I disable

it,
all

the other case statements work.

This piece of code was originally an IF, Then statement (shown at the
bottom) and worked. I want to incorporate that function with all

the
other

validation in the case statements. This bit looks at a number of check
boxes, as long as at least one is checked, then OK. Is there a way

to
do

this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[Guest]

You are right, it would be annoying. The form has many controls, only some
are required. The required controls are colored blue until the user enters
the control, then they go white. If the user leaves the control w/o entering
data, the control goes blue again. There are no error messages at this
point, just the color changes. Visually, the user sees what fields they have
to complete before saving. If they're paying attention, they shouldn't see
any error messages. The error messages only go after the user clicks the
"save" command button and only if a required control is blank. When I test
it, only one msgbox fires even if I have several required controls blank.

It sounds as though your edit check has the potential of showing 0, 1 or 2
error messages. However, even if they receive 2 error messages, there might
still be additional errors: they go back, correct the error(s) that popped
up, and they'll receive yet another error message. I would think that would
be annoying.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas
I removed the problem case, returned it to an IF, Then statement, and placed
it before the select case statements. I was hoping to have all my validation
integrated to make code edits easier and have a seamless presentation. In
effect they are still integrated except the If, Then now fires first which
may or may not be out of order (meaning the tab order). It is no longer a
functional problem, just a presentation issue that we were able to mitigate.
I told the users the field is very important, so it gets its own warning,
which is mostly true.

When I have the time, I'll dig into it and see if I can fix it. I'm going
to try your Strings rather than my message boxes too. I'm still learning
VBA, while I'm no longer "dangerous", I am not yet walking either.

Thanks again for your posts, they are very helpful.

I'm not sure a Case statement is necessarily the best approach, as a Case
statement will stop at the first case that matches, and you could still have
other errors.

What I typically do is go through all the errors:

strMessage = vbNullString

If IsNull(control1) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If IsNull(control2) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D], 0) +
Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) Then
strMessage = strMessage & "At least One Commodity Type must be selected"
& vbCrLf
End If


If IsNull(control4) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage, vbOkOnly + vbError, "Errors"
Cancel = True
Else
MsgBox "Record Saved"
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm sorry I was unclear, I have 11 or so Select Case statements driven by
clicking a command button "save record". The code starts with
Select Case True
Case IsNull(control1)
Msgbox "bla bla bla"

Case IsNull(control2)
Msgbox "bla bla bla"

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"

Case IsNull (control4)
MsgBox "bla bla bla"

Case Else
DoCmd.Save
MsgBox "record saved"
Etc

The IsNull ones are simple and all work, it is the case where I assign '0'
to null values that doesn't work. Depending on what changes I make to
that
case code trying to get it to work, it either;
1. apparently skips over the case (the remaining cases still work as they
should), so the code syntax is likely correct, but I've got it backwards
somewhere as it doesn't perform the requested task,
or
2. the application does not appear to do anything, literally it is as if I
did not push the command putton. The subsequent case statement should
fire
because its control is still blank but it doesn't, and the record saves.
It
doesn't freeze, I can click and keep going, but the case statement clearly
doesn't do what I need it to do.

I tried your suggestion George, inserting the = 0 at the end, but that got
me to scenario 2. I tried Douglas' suggestion inserting the Case 0, that
got me to scenario 1, the next case fired, completely skipping this one.
I've tried Case Is =0, <>0, >0, and others, none seem to work. I know
I'm
close, but I just don't know Select Cases well enough to diagnose the
problem.

Thanks for your help.

:

I'm having difficulty with a case statement. I am validating that
specific
fields in a form are filled out. Below is the one case statement that
doesn't work When the code gets to this point, it apparently bypasses
the
remainder of the case statements and saves the form. When I disable it,
all
the other case statements work.

This piece of code was originally an IF, Then statement (shown at the
bottom) and worked. I want to incorporate that function with all the
other
validation in the case statements. This bit looks at a number of check
boxes, as long as at least one is checked, then OK. Is there a way to
do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[Douglas J Steele]

But your one msgbox only reports that one control is blank, even if several
controls are actually blank, doesn't it? Or does your "bla bla bla" message
not mention the specific control?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You are right, it would be annoying. The form has many controls, only some
are required. The required controls are colored blue until the user enters
the control, then they go white. If the user leaves the control w/o entering
data, the control goes blue again. There are no error messages at this
point, just the color changes. Visually, the user sees what fields they have
to complete before saving. If they're paying attention, they shouldn't see
any error messages. The error messages only go after the user clicks the
"save" command button and only if a required control is blank. When I test
it, only one msgbox fires even if I have several required controls blank.

It sounds as though your edit check has the potential of showing 0, 1 or 2
error messages. However, even if they receive 2 error messages, there might
still be additional errors: they go back, correct the error(s) that popped
up, and they'll receive yet another error message. I would think that would
be annoying.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Douglas
I removed the problem case, returned it to an IF, Then statement, and placed
it before the select case statements. I was hoping to have all my validation
integrated to make code edits easier and have a seamless presentation. In
effect they are still integrated except the If, Then now fires first which
may or may not be out of order (meaning the tab order). It is no longer a
functional problem, just a presentation issue that we were able to mitigate.
I told the users the field is very important, so it gets its own warning,
which is mostly true.

When I have the time, I'll dig into it and see if I can fix it. I'm going
to try your Strings rather than my message boxes too. I'm still learning
VBA, while I'm no longer "dangerous", I am not yet walking either.

Thanks again for your posts, they are very helpful.


:

I'm not sure a Case statement is necessarily the best approach, as a Case
statement will stop at the first case that matches, and you could

still
have

other errors.

What I typically do is go through all the errors:

strMessage = vbNullString

If IsNull(control1) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If IsNull(control2) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D], 0) +
Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) Then
strMessage = strMessage & "At least One Commodity Type must be selected"
& vbCrLf
End If


If IsNull(control4) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage, vbOkOnly + vbError, "Errors"
Cancel = True
Else
MsgBox "Record Saved"
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm sorry I was unclear, I have 11 or so Select Case statements

driven
by

clicking a command button "save record". The code starts with
Select Case True
Case IsNull(control1)
Msgbox "bla bla bla"

Case IsNull(control2)
Msgbox "bla bla bla"

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O -

O],
0))

MsgBox "At least One Commodity Type must be selected", "Required
Field:"

Case IsNull (control4)
MsgBox "bla bla bla"

Case Else
DoCmd.Save
MsgBox "record saved"
Etc

The IsNull ones are simple and all work, it is the case where I

assign
'0'

to null values that doesn't work. Depending on what changes I make to
that
case code trying to get it to work, it either;
1. apparently skips over the case (the remaining cases still work

as
they

should), so the code syntax is likely correct, but I've got it backwards
somewhere as it doesn't perform the requested task,
or
2. the application does not appear to do anything, literally it is

as
if I

did not push the command putton. The subsequent case statement should
fire
because its control is still blank but it doesn't, and the record saves.
It
doesn't freeze, I can click and keep going, but the case statement clearly
doesn't do what I need it to do.

I tried your suggestion George, inserting the = 0 at the end, but

that
got

me to scenario 2. I tried Douglas' suggestion inserting the Case

0,
that

got me to scenario 1, the next case fired, completely skipping

this
one.

I've tried Case Is =0, <>0, >0, and others, none seem to work. I know
I'm
close, but I just don't know Select Cases well enough to diagnose the
problem.

Thanks for your help.

:

I'm having difficulty with a case statement. I am validating that
specific
fields in a form are filled out. Below is the one case

statement
that

doesn't work When the code gets to this point, it apparently bypasses
the
remainder of the case statements and saves the form. When I

disable
it,

all
the other case statements work.

This piece of code was originally an IF, Then statement (shown

at
the

bottom) and worked. I want to incorporate that function with

all
the

other
validation in the case statements. This bit looks at a number

of
check

boxes, as long as at least one is checked, then OK. Is there a

way
to

do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O -

O],
0))

MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[Guest]

I see the confusion... sorry. bla bla bal was just a posting shortcut.
Each case has a specific error message, i.e. "abc is required, please fill
in abc" then it sets focus on that control. The next case is "def is
required, please fill in def" and sets focus on that, and so on.

But your one msgbox only reports that one control is blank, even if several
controls are actually blank, doesn't it? Or does your "bla bla bla" message
not mention the specific control?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You are right, it would be annoying. The form has many controls, only some
are required. The required controls are colored blue until the user enters
the control, then they go white. If the user leaves the control w/o entering
data, the control goes blue again. There are no error messages at this
point, just the color changes. Visually, the user sees what fields they have
to complete before saving. If they're paying attention, they shouldn't see
any error messages. The error messages only go after the user clicks the
"save" command button and only if a required control is blank. When I test
it, only one msgbox fires even if I have several required controls blank.

It sounds as though your edit check has the potential of showing 0, 1 or 2
error messages. However, even if they receive 2 error messages, there might
still be additional errors: they go back, correct the error(s) that popped
up, and they'll receive yet another error message. I would think that would
be annoying.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Douglas
I removed the problem case, returned it to an IF, Then statement, and
placed
it before the select case statements. I was hoping to have all my
validation
integrated to make code edits easier and have a seamless presentation. In
effect they are still integrated except the If, Then now fires first which
may or may not be out of order (meaning the tab order). It is no longer a
functional problem, just a presentation issue that we were able to
mitigate.
I told the users the field is very important, so it gets its own warning,
which is mostly true.

When I have the time, I'll dig into it and see if I can fix it. I'm going
to try your Strings rather than my message boxes too. I'm still learning
VBA, while I'm no longer "dangerous", I am not yet walking either.

Thanks again for your posts, they are very helpful.


:

I'm not sure a Case statement is necessarily the best approach, as a
Case
statement will stop at the first case that matches, and you could still
have
other errors.

What I typically do is go through all the errors:

strMessage = vbNullString

If IsNull(control1) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If IsNull(control2) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D], 0) +
Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) Then
strMessage = strMessage & "At least One Commodity Type must be
selected"
& vbCrLf
End If


If IsNull(control4) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage, vbOkOnly + vbError, "Errors"
Cancel = True
Else
MsgBox "Record Saved"
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm sorry I was unclear, I have 11 or so Select Case statements driven
by
clicking a command button "save record". The code starts with
Select Case True
Case IsNull(control1)
Msgbox "bla bla bla"

Case IsNull(control2)
Msgbox "bla bla bla"

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O],
0))
MsgBox "At least One Commodity Type must be selected",
"Required
Field:"

Case IsNull (control4)
MsgBox "bla bla bla"

Case Else
DoCmd.Save
MsgBox "record saved"
Etc

The IsNull ones are simple and all work, it is the case where I assign
'0'
to null values that doesn't work. Depending on what changes I make to
that
case code trying to get it to work, it either;
1. apparently skips over the case (the remaining cases still work as
they
should), so the code syntax is likely correct, but I've got it
backwards
somewhere as it doesn't perform the requested task,
or
2. the application does not appear to do anything, literally it is as
if I
did not push the command putton. The subsequent case statement should
fire
because its control is still blank but it doesn't, and the record
saves.
It
doesn't freeze, I can click and keep going, but the case statement
clearly
doesn't do what I need it to do.

I tried your suggestion George, inserting the = 0 at the end, but that
got
me to scenario 2. I tried Douglas' suggestion inserting the Case 0,
that
got me to scenario 1, the next case fired, completely skipping this
one.
I've tried Case Is =0, <>0, >0, and others, none seem to work. I
know
I'm
close, but I just don't know Select Cases well enough to diagnose the
problem.

Thanks for your help.

:

I'm having difficulty with a case statement. I am validating that
specific
fields in a form are filled out. Below is the one case statement
that
doesn't work When the code gets to this point, it apparently
bypasses
the
remainder of the case statements and saves the form. When I disable
it,
all
the other case statements work.

This piece of code was originally an IF, Then statement (shown at
the
bottom) and worked. I want to incorporate that function with all
the
other
validation in the case statements. This bit looks at a number of
check
boxes, as long as at least one is checked, then OK. Is there a way
to
do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O],
0))
MsgBox "At least One Commodity Type must be selected",
"Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[Douglas J Steele]

That's what I thought.

So if the user has forgotten to fill out abc AND forgotten to fill out def,
you're only going to show him/her a message box with only

abc is required, please fill in abc

correct?

What I was suggesting would show one message box with:

abc is required, please fill in abc
def is required, please fill in def



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I see the confusion... sorry. bla bla bal was just a posting shortcut.
Each case has a specific error message, i.e. "abc is required, please fill
in abc" then it sets focus on that control. The next case is "def is
required, please fill in def" and sets focus on that, and so on.

But your one msgbox only reports that one control is blank, even if several
controls are actually blank, doesn't it? Or does your "bla bla bla" message
not mention the specific control?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You are right, it would be annoying. The form has many controls, only some
are required. The required controls are colored blue until the user enters
the control, then they go white. If the user leaves the control w/o entering
data, the control goes blue again. There are no error messages at this
point, just the color changes. Visually, the user sees what fields

they
have

to complete before saving. If they're paying attention, they

shouldn't
see

any error messages. The error messages only go after the user clicks the
"save" command button and only if a required control is blank. When I test
it, only one msgbox fires even if I have several required controls blank.

:

It sounds as though your edit check has the potential of showing 0,

1 or
2

error messages. However, even if they receive 2 error messages,

there
might

still be additional errors: they go back, correct the error(s) that popped
up, and they'll receive yet another error message. I would think

that
would

be annoying.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Douglas
I removed the problem case, returned it to an IF, Then statement, and
placed
it before the select case statements. I was hoping to have all my
validation
integrated to make code edits easier and have a seamless

presentation.
In

effect they are still integrated except the If, Then now fires

first
which

may or may not be out of order (meaning the tab order). It is no longer a
functional problem, just a presentation issue that we were able to
mitigate.
I told the users the field is very important, so it gets its own warning,
which is mostly true.

When I have the time, I'll dig into it and see if I can fix it.

I'm
going

to try your Strings rather than my message boxes too. I'm still learning
VBA, while I'm no longer "dangerous", I am not yet walking either.

Thanks again for your posts, they are very helpful.


:

I'm not sure a Case statement is necessarily the best approach, as a
Case
statement will stop at the first case that matches, and you

could
still

have
other errors.

What I typically do is go through all the errors:

strMessage = vbNullString

If IsNull(control1) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If IsNull(control2) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M

D],
0) +

Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) Then
strMessage = strMessage & "At least One Commodity Type must be
selected"
& vbCrLf
End If


If IsNull(control4) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage, vbOkOnly + vbError, "Errors"
Cancel = True
Else
MsgBox "Record Saved"
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm sorry I was unclear, I have 11 or so Select Case

statements
driven

by
clicking a command button "save record". The code starts with
Select Case True
Case IsNull(control1)
Msgbox "bla bla bla"

Case IsNull(control2)
Msgbox "bla bla bla"

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) +

Nz(Me.[O -
O],

0))
MsgBox "At least One Commodity Type must be selected",
"Required
Field:"

Case IsNull (control4)
MsgBox "bla bla bla"

Case Else
DoCmd.Save
MsgBox "record saved"
Etc

The IsNull ones are simple and all work, it is the case where

I
assign

'0'
to null values that doesn't work. Depending on what changes I make to
that
case code trying to get it to work, it either;
1. apparently skips over the case (the remaining cases still

work
as

they
should), so the code syntax is likely correct, but I've got it
backwards
somewhere as it doesn't perform the requested task,
or
2. the application does not appear to do anything, literally

it is
as

if I
did not push the command putton. The subsequent case

statement
should

fire
because its control is still blank but it doesn't, and the record
saves.
It
doesn't freeze, I can click and keep going, but the case statement
clearly
doesn't do what I need it to do.

I tried your suggestion George, inserting the = 0 at the end,

but
that

got
me to scenario 2. I tried Douglas' suggestion inserting the

Case
0,

that
got me to scenario 1, the next case fired, completely skipping this
one.
I've tried Case Is =0, <>0, >0, and others, none seem to work. I
know
I'm
close, but I just don't know Select Cases well enough to

diagnose
the

problem.

Thanks for your help.

:

I'm having difficulty with a case statement. I am

validating
that

specific
fields in a form are filled out. Below is the one case statement
that
doesn't work When the code gets to this point, it apparently
bypasses
the
remainder of the case statements and saves the form. When I disable
it,
all
the other case statements work.

This piece of code was originally an IF, Then statement

(shown
at

the
bottom) and worked. I want to incorporate that function

with
all

the
other
validation in the case statements. This bit looks at a

number
of

check
boxes, as long as at least one is checked, then OK. Is

there a
way

to
do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) +

Nz(Me.[O -
O],

0))
MsgBox "At least One Commodity Type must be selected",
"Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False

 

[Guest]

Hi,

Can you explain what is happening in this part of the code? I want to
reference it in my VBA book. I'm a beginner.

Code:

If Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D], 0) +
Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) Then
strMessage = strMessage & "At least One Commodity Type must be selected"
& vbCrLf
End If

Thanks

--
JLV

I'm not sure a Case statement is necessarily the best approach, as a Case
statement will stop at the first case that matches, and you could still have
other errors.

What I typically do is go through all the errors:

strMessage = vbNullString

If IsNull(control1) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If IsNull(control2) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) + Nz(Me.N, 0) + Nz(Me.[M D], 0) +
Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0)) Then
strMessage = strMessage & "At least One Commodity Type must be selected"
& vbCrLf
End If


If IsNull(control4) Then
strMessage = strMessage & "bla bla bla" & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage, vbOkOnly + vbError, "Errors"
Cancel = True
Else
MsgBox "Record Saved"
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm sorry I was unclear, I have 11 or so Select Case statements driven by
clicking a command button "save record". The code starts with
Select Case True
Case IsNull(control1)
Msgbox "bla bla bla"

Case IsNull(control2)
Msgbox "bla bla bla"

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"

Case IsNull (control4)
MsgBox "bla bla bla"

Case Else
DoCmd.Save
MsgBox "record saved"
Etc

The IsNull ones are simple and all work, it is the case where I assign '0'
to null values that doesn't work. Depending on what changes I make to that
case code trying to get it to work, it either;
1. apparently skips over the case (the remaining cases still work as they
should), so the code syntax is likely correct, but I've got it backwards
somewhere as it doesn't perform the requested task,
or
2. the application does not appear to do anything, literally it is as if I
did not push the command putton. The subsequent case statement should fire
because its control is still blank but it doesn't, and the record saves. It
doesn't freeze, I can click and keep going, but the case statement clearly
doesn't do what I need it to do.

I tried your suggestion George, inserting the = 0 at the end, but that got
me to scenario 2. I tried Douglas' suggestion inserting the Case 0, that
got me to scenario 1, the next case fired, completely skipping this one.
I've tried Case Is =0, <>0, >0, and others, none seem to work. I know I'm
close, but I just don't know Select Cases well enough to diagnose the
problem.

Thanks for your help.

I'm having difficulty with a case statement. I am validating that specific
fields in a form are filled out. Below is the one case statement that
doesn't work When the code gets to this point, it apparently bypasses the
remainder of the case statements and saves the form. When I disable it, all
the other case statements work.

This piece of code was originally an IF, Then statement (shown at the
bottom) and worked. I want to incorporate that function with all the other
validation in the case statements. This bit looks at a number of check
boxes, as long as at least one is checked, then OK. Is there a way to do
this test with a case statement?

Case Abs(Nz(Me.S, 0) + Nz(Me.[D / M], 0) +
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + Nz(Me.[O - O], 0))
MsgBox "At least One Commodity Type must be selected", "Required
Field:"


Original IF, Then code
If Abs(Nz(Me.S, 0) + Nz(Me.[D M], 0) + _
Nz(Me.N, 0) + Nz(Me.[M D], 0) + Nz(Me.[IT P S], 0) + _
Nz(Me.[O - O], 0)) = 0 Then
MsgBox "At least One Commodity Type must be selected", vbOKOnly,
"Required Field:"
Cancel = False