Will this IF work?

  • Thread starter Thread starter BonnieW via AccessMonster.com
  • Start date Start date
B

BonnieW via AccessMonster.com

This code should work, I think, but there's got to be a better way to do this
than a million IFs. Anyone have any suggestions?

If IsNull(Activity.Value) Then
MsgBox "Missing Required Field - Activity!"
ElseIf IsNull(EffortDate.Value) Then
MsgBox "Missing Required Field - Effort Date!"
ElseIf IsNull(Hours.Value) Then
MsgBox "Missing Required Field - Hours!"
ElseIf IsNull(PeopleType.Value) Then
MsgBox "Missing Required Field - PeopleType!"
Else
DoCmd.Save
If Activity.Value = "Trail Work" Then
DoCmd.OpenForm "frmTrailWorkEntry", , , , acFormAdd
ElseIf Activity.Value = "Invasives Control" Then
DoCmd.OpenForm "frmInvasivesControlCrewNew", , , , acFormAdd
ElseIf Activity.Value = "Revegetation" Then
DoCmd.OpenForm "frmRevegetationNew", , , , acFormAdd
Else
DoCmd.GoToRecord , , acNewRec
'Refresh
Forms!tablepeople.SetFocus
MsgBox "Your record has been saved! Please choose your next
volunteer or enter additional information.", vbOKOnly, "Hooray!"
End If

Thanks in advance for any advice or suggestions!
 
Alright, that code created some problems- not related to the actual code, I
think, so now I have this:
If IsNull(Activity.Value) Then
MsgBox "Missing Required Field - Activity!"
ElseIf IsNull(EffortDate.Value) Then
MsgBox "Missing Required Field - Effort Date!"
ElseIf IsNull(Hours.Value) Then
MsgBox "Missing Required Field - Hours!"
ElseIf IsNull(PeopleType.Value) Then
MsgBox "Missing Required Field - PeopleType!"
'why is the following rem'd out? unrem'd 5/10 bw
Else
DoCmd.Save
If Activity.Value = "Trail Work" Then
DoCmd.GoToRecord , , acNewRec
MsgBox "So, about that trail work...", vbOKOnly, "Hooray!"
DoCmd.OpenForm "frmTrailWorkEntry", , , , acFormAdd
ElseIf Activity.Value = "Invasives Control" Then
DoCmd.GoToRecord , , acNewRec
MsgBox "Invasives control, huh? Let's talk about that some
more.", vbOKOnly, "Hooray!"
DoCmd.OpenForm "frmInvasivesControlCrewNew", , , , acFormAdd
ElseIf Activity.Value = "Revegetation" Then
DoCmd.GoToRecord , , acNewRec
MsgBox "Revegetation? Seriously?", vbOKOnly, "Hooray!"
DoCmd.OpenForm "frmRevegetationNew", , , , acFormAdd
Else
'DoCmd.GoToRecord , , acNext, 1
DoCmd.GoToRecord , , acNewRec
'Refresh
Forms!tablepeople.SetFocus
MsgBox "Your record has been saved! Please choose your next
volunteer.", vbOKOnly, "Hooray!"
End If
End If
 
Use a loop going through all of your controls, here's an idea.

Dim control as Control
for each control in Me.Controls
If isNull(Me.controls(control)) Then
If the control is EffortDate, PeopleType, Hours, etc
' do your msgBox
Else
' do your saving thing
End If
Next

That code is very rough, but using a loop for the control's is a much
cleaner solution.

HTH

Mark
 
This still has "a million ifs" but they produce a nicer result: informing
your user about ALL missing data at one time, rather than telling them about
one field, have them fix that, then tell them about another field, have them
fix that, etc., etc.


Dim strMessage as string
If IsNull(Activity.Value) Then
strMessage = ", Activity"
End If
If IsNull(EffortDate.Value) Then
strMessage = strMessage & ", Effort Date"
End If
If IsNull(Hours.Value) Then
strMessage = strMessage & ", Hours"
End If
If IsNull(PeopleType.Value) Then
' At least one field is missing. Add Prefix phrase to message, skip
leading comma-space.
strMessage = strMessage & ", PeopleType"
End If

If Len(strMessage)>0 Then
' At least one field is missing.
' Add preface to message and skip 2 leading characters (comma-space)
strMessage = "The following required field(s) are missing: " &
Mid(strMessage,3) & "!"
MsgBox strMessage
'(optionally: Exit Sub)
Else
'Do the save
End If

Variation: rather than a single line message, you could incorporate the
vbCrLf constant (CarriageReturn LineFeed) to place each field name on a
separate line rather than use comma separation. (i.e., strMessage =
strMessage & "Activity" & vbCrLf)


HTH,
 
BonnieW said:
Alright, that code created some problems- not related to the actual code, I
think, so now I have this:
If IsNull(Activity.Value) Then
MsgBox "Missing Required Field - Activity!"
ElseIf IsNull(EffortDate.Value) Then
MsgBox "Missing Required Field - Effort Date!"
ElseIf IsNull(Hours.Value) Then
MsgBox "Missing Required Field - Hours!"
ElseIf IsNull(PeopleType.Value) Then
MsgBox "Missing Required Field - PeopleType!"
'why is the following rem'd out? unrem'd 5/10 bw
Else
DoCmd.Save
If Activity.Value = "Trail Work" Then
DoCmd.GoToRecord , , acNewRec
MsgBox "So, about that trail work...", vbOKOnly, "Hooray!"
DoCmd.OpenForm "frmTrailWorkEntry", , , , acFormAdd
ElseIf Activity.Value = "Invasives Control" Then
DoCmd.GoToRecord , , acNewRec
MsgBox "Invasives control, huh? Let's talk about that some
more.", vbOKOnly, "Hooray!"
DoCmd.OpenForm "frmInvasivesControlCrewNew", , , , acFormAdd
ElseIf Activity.Value = "Revegetation" Then
DoCmd.GoToRecord , , acNewRec
MsgBox "Revegetation? Seriously?", vbOKOnly, "Hooray!"
DoCmd.OpenForm "frmRevegetationNew", , , , acFormAdd
Else
'DoCmd.GoToRecord , , acNext, 1
DoCmd.GoToRecord , , acNewRec
'Refresh
Forms!tablepeople.SetFocus
MsgBox "Your record has been saved! Please choose your next
volunteer.", vbOKOnly, "Hooray!"
End If
End If
Click to expand...


First, your use of DoCmd.Save is totally inappropriate.
That saves the form's design, not a record. To save any
edits to the current record, use:
Me.Dirty = False

You can use a lot less code to check if the required values
have been entered. Set each of those text box's Tag
property to something like CHECK, the use a loop like:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "CHECK" Then
If IsNull(ctl.Value) Then
MsgBox "Missing Required Field - " & ctl.Name
End If
End If
Next ctl

I suggest that you merge that idea along with George's idea
about the message listing all the empty fields instead of
one at a time.

I don't see a pattern to the second set of If statements
beyond the possibility that they are all checking for
specific values in the Activity text box. In that case, the
code would be a little simpler using Select Case

Select Case Activity.Value
Case "Trail Work"
. . .
Case "Invasives Control"
. . .
Case "Revegetation"
. . .
Case Else
. . .
End Select
 
Thanks! The DoCmd save was a remnant of a previous iteration- once I found
out its true use, I got rid of it everywhere else, but managed to forget it
there.

I'll try the cases and the looping; thanks!

Marshall said:
Alright, that code created some problems- not related to the actual code, I
think, so now I have this:
Click to expand...
[quoted text clipped - 31 lines]
End If
End If
Click to expand...

First, your use of DoCmd.Save is totally inappropriate.
That saves the form's design, not a record. To save any
edits to the current record, use:
Me.Dirty = False

You can use a lot less code to check if the required values
have been entered. Set each of those text box's Tag
property to something like CHECK, the use a loop like:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "CHECK" Then
If IsNull(ctl.Value) Then
MsgBox "Missing Required Field - " & ctl.Name
End If
End If
Next ctl

I suggest that you merge that idea along with George's idea
about the message listing all the empty fields instead of
one at a time.

I don't see a pattern to the second set of If statements
beyond the possibility that they are all checking for
specific values in the Activity text box. In that case, the
code would be a little simpler using Select Case

Select Case Activity.Value
Case "Trail Work"
. . .
Case "Invasives Control"
. . .
Case "Revegetation"
. . .
Case Else
. . .
End Select
Click to expand...
 
Thanks! I'll give it a go. :)

George said:
This still has "a million ifs" but they produce a nicer result: informing
your user about ALL missing data at one time, rather than telling them about
one field, have them fix that, then tell them about another field, have them
fix that, etc., etc.

Dim strMessage as string
If IsNull(Activity.Value) Then
strMessage = ", Activity"
End If
If IsNull(EffortDate.Value) Then
strMessage = strMessage & ", Effort Date"
End If
If IsNull(Hours.Value) Then
strMessage = strMessage & ", Hours"
End If
If IsNull(PeopleType.Value) Then
' At least one field is missing. Add Prefix phrase to message, skip
leading comma-space.
strMessage = strMessage & ", PeopleType"
End If

If Len(strMessage)>0 Then
' At least one field is missing.
' Add preface to message and skip 2 leading characters (comma-space)
strMessage = "The following required field(s) are missing: " &
Mid(strMessage,3) & "!"
MsgBox strMessage
'(optionally: Exit Sub)
Else
'Do the save
End If

Variation: rather than a single line message, you could incorporate the
vbCrLf constant (CarriageReturn LineFeed) to place each field name on a
separate line rather than use comma separation. (i.e., strMessage =
strMessage & "Activity" & vbCrLf)

HTH,
This code should work, I think, but there's got to be a better way to do
this
Click to expand...
[quoted text clipped - 26 lines]
Thanks in advance for any advice or suggestions!
Click to expand...
Click to expand...
 
Back
Top