Re: Case Statements

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.

"Douglas J Steele" wrote:

> 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
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Tedd N" <TeddN@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:FB244CC2-0EDF-4219-B0C3-C31DA1F9E38E@xxxxxxxxxxxxxxxx
> > 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.
> >
> >
> > "Douglas J Steele" wrote:
> >
> > > 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
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > > "Tedd N" <TeddN@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:0A035251-AF24-46D1-91B2-AE7645803383@xxxxxxxxxxxxxxxx
> > > > 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.
> > > >
> > > > "Tedd N" wrote:
> > > >
> > > > > 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
> > > > >
> > >
> > >
> > >
>
>
>
.