Long If Statement

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

is there an easier way to write the following If Statement? Its working but
my first intention was to do an Case statement but could not figure it out.

If Me.txt745 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor 745 does not have sufficient funds")
ElseIf Me.txtBYL1999_1 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor BYL1999_1 does not have sufficient funds")
ElseIf Me.txtCNL2001_1 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor CNL2001_1 does not have sufficient funds")
ElseIf Me.txtCNL2001_2 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor CNL2001_2 does not have sufficient funds")
ElseIf Me.txtCNL2002_1 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor CNL2002_1 does not have sufficient funds")
ElseIf Me.txtCNL2002_2 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor CNL2002_2 does not have sufficient funds")
ElseIf Me.txtCNL2003_1 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor CNL2003_1 does not have sufficient funds")
ElseIf Me.txtCNL2003_2 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor CNL2003_2 does not have sufficient funds")
ElseIf Me.txtLBHI < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor LBHI does not have sufficient funds")
ElseIf Me.txtLBSBF < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor LBSBF does not have sufficient funds")
ElseIf Me.txtOCWEN < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor OCWEN does not have sufficient funds")
ElseIf Me.txtSBF2005_1 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor SBF2005_1 does not have sufficient funds")
ElseIf Me.txtSBF2005_2 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor SBF2005_2 does not have sufficient funds")
ElseIf Me.txtSBF2006_1 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor SBF2006_1 does not have sufficient funds")
ElseIf Me.txtSBF2006_2 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor SBF2006_2 does not have sufficient funds")
ElseIf Me.txtSBF2006_3 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor SBF2006_3 does not have sufficient funds")
ElseIf Me.txtSBF2007_1 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor SBF2007_1 does not have sufficient funds")
ElseIf Me.txtSBF2007_2 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor SBF2007_2 does not have sufficient funds")
ElseIf Me.txtSBF2007_3 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor SBF2007_3 does not have sufficient funds")
ElseIf Me.txtWF2008_1 < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor WF2008_1 does not have sufficient funds")
ElseIf Me.txtHoldOver < 0 Then
DoCmd.CancelEvent
MsgBox ("HoldOver bucket does not have sufficient funds")
ElseIf Me.txtUnidentified < 0 Then
DoCmd.CancelEvent
MsgBox ("Unidentified bucket does not have sufficient funds")
ElseIf Me.txtOperating < 0 Then
DoCmd.CancelEvent
MsgBox ("Operating bucket does not have sufficient funds")
ElseIf Me.txtREIT < 0 Then
DoCmd.CancelEvent
MsgBox ("Investor REIT does not have sufficient funds")
ElseIf Me.txtMATCH = "No" Then
DoCmd.CancelEvent
MsgBox ("One or more records do not match. Please review your data or
contact your system admistrator")
ElseIf Me.txtBalPriorToSubmit = "N" Then
DoCmd.CancelEvent
MsgBox "Total check amount and Payment Amount must balance before
submitting the daily activity." _
& "Please review your data or contact your system admistrator.",
vbCritical, "Error"
Else: DoCmd.OpenForm stDocName
End If
 
Joe said:
is there an easier way to write the following If Statement? Its working
but
my first intention was to do an Case statement but could not figure it
out.
Click to expand...

Much part of your problem here is that your data looks more like a
spreadsheet then a database. As a general rule if you had normalized data
this this would be a lot less code and in fact the verification could be
done in the before update event for ALL of those values. You would wind up
with about 3-4 lines of code.

Anway, ignoring the above issue. To deal with your current difficult
structures, I would adopt some code like:

Dim strFields As String
Dim vList As Variant
Dim v As Variant

strFields = "txt745,txtBYL1999_1,txtCNL2001_1,txtCNL2002_1 etc..."
vList = Split(strFields, ",")

bolBad = False
For Each v In vList
If Me(v) < 0 Then
MsgBox "Investor " & Mid(v, 4) & " does not have sufficient funds"
DoCmd.CancelEvent
Exit Sub
End If
Next

DoCmd.OpenForm stDocName
 
str = ""
select case TRUE
Case Me.txt745 < 0: str = "Investor 745 does not have ... "
Case Me.txtBYL1999_1 < 0: str="Investor BYL1999_1 ... "
Case Me.txtCNL2001_2 < 0: str ="Investor CNL2001_1 ..."
.....
Case Me.txtMATCH = "No": str = "One or more ..."
End Case

If str<>""
DoCmd.CancelEvent
msgbox str

ElseIf Me.txtBalPriorToSubmit = "N"
DoCmd.CancelEvent
MsgBox "Total check amount and Payment Amount must balance before
submitting the daily activity." _
& "Please review your data or contact your system admistrator.",
vbCritical, "Error"
Else
DoCmd.OpenForm stDocName
EndIf

NOTE: You could put the last two cases into the
case statement as well, but I've separated them out
to make it clear that they are different.

I've truncated the strings just to make this message shorter.

I've put the case expression on the same line as the string assignment by
using the statement separation character, the colon.

(david)
 
Back
Top