Complex If Statement

D

DS

Can anyone let me know if this if Statement is correct. It works but
looks a little scary !
Thanks
DS

Private Sub ImageSave_Click()
Me.CommandHide.SetFocus
If IsNull(Me.TxtName) _
Or Me.TxtName = "" _
Or IsNull(Me.TxtType) _
Or Me.TxtType = "" _
Or IsNull(Me.TxtWorth) _
Or Me.TxtWorth = "" _
Or IsNull(Me.TxtPercent) _
Or IsNull(Me.TxtExp) _
Or Me.TxtExp = "" _
Then
DoCmd.OpenForm "FXMsgWarning"
Forms!FXMsgWarning!TxtMsg = "EMPTY FIELD"
ElseIf Me.TxtAction = 0 Then
If Me.TxtWorth = "Dollar" Then
Me.CommandHide.SetFocus
Me.TxtStatus = "SAVED"
Me.TxtID = Nz(DMax("[PaymentNameID]", "PayName"), 0) + 1
Dim DSQL As String
DoCmd.SetWarnings False
DSQL = "INSERT INTO PayName " & _

"(PaymentNameID,PaymentName,PaymentType,ExpirationDate,Active, " & _
"CouponType,CouponWorth,CouponAmount) " & _
"Values(" & Forms!CouponNames!TxtID & ", " & _
"'" & Forms!CouponNames!TxtName & "', " & _
"" & 3 & ", " & _
"" & Format(Forms!CouponNames![TxtExp],
"\#mm\/dd\/yyyy\#") & ", " & _
"" & Forms!CouponNames![ChkActive] & ", " & _
"" & Forms!CouponNames!TxtType & ", " & _
"'" & "Dollar" & "', " & _
"'" & Forms!CouponNames!TxtAmount & "')"
DoCmd.RunSQL (DSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
Me.TxtStatus = ""
Me.TxtID = 0
Me.TxtName = ""
Me.TxtType = ""
Me.ChkActive = -1
Me.LblActive.Visible = True
Me.TxtWorth = ""
Me.TxtExp = Date
Me.TxtAmount.Visible = False
Me.Label37.Visible = False
Me.TxtPercent.Visible = False
Me.Label38.Visible = False
ElseIf Me.TxtWorth = "Percent" Then
Me.CommandHide.SetFocus
Me.TxtStatus = "SAVED"
Me.TxtID = Nz(DMax("[PaymentNameID]", "PayName"), 0) + 1
Dim PSQL As String
DoCmd.SetWarnings False
PSQL = "INSERT INTO PayName " & _

"(PaymentNameID,PaymentName,PaymentType,ExpirationDate,Active, " & _
"CouponType,CouponWorth,CouponPercent) " & _
"Values(" & Forms!CouponNames!TxtID & ", " & _
"'" & Forms!CouponNames!TxtName & "', " & _
"" & 3 & ", " & _
"" & Format(Forms!CouponNames![TxtExp],
"\#mm\/dd\/yyyy\#") & ", " & _
"" & Forms!CouponNames![ChkActive] & ", " & _
"" & Forms!CouponNames!TxtType & ", " & _
"'" & "Percent" & "', " & _
"'" & Forms!CouponNames!TxtPercent & "')"
DoCmd.RunSQL (PSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
Me.TxtStatus = ""
Me.TxtID = ""
Me.TxtName = ""
Me.TxtType = ""
Me.ChkActive = -1
Me.LblActive.Visible = True
Me.TxtWorth = ""
Me.TxtAmount.Visible = False
Me.Label37.Visible = False
Me.TxtPercent.Visible = False
Me.Label38.Visible = False
End If
ElseIf Me.TxtAction = 1 Then
If Me.TxtWorth = "Dollar" Then
Me.CommandHide.SetFocus
Dim DollUpSQL As String
DoCmd.SetWarnings False
DollUpSQL = "UPDATE PayName SET PayName.PaymentName =
Forms!CouponNames!TxtName, " & _
"PayName.Active = Forms!CouponNames!ChkActive, " & _
"PayName.CouponType = Forms!CouponNames!TxtType, " & _
"PayName.CouponWorth = Forms!CouponNames!TxtWorth, " & _
"PayName.CouponAmount = Forms!CouponNames!TxtAmount, " & _
"PayName.ExpirationDate = Forms!CouponNames!TxtExp " & _
"WHERE PayName.PaymentNameID = Forms!CouponNames!TxtID;"
DoCmd.RunSQL (DollUpSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
DoCmd.OpenForm "FXMsgInfo"
Forms!FXMsgInfo!TxtMsg = "UPDATE SUCCESSFUL"
ElseIf Me.TxtWorth = "Percent" Then
Me.CommandHide.SetFocus
Dim PerUpSQL As String
DoCmd.SetWarnings False
PerUpSQL = "UPDATE PayName SET PayName.PaymentName =
Forms!CouponNames!TxtName, " & _
"PayName.Active = Forms!CouponNames!ChkActive, " & _
"PayName.CouponType = Forms!CouponNames!TxtType, " & _
"PayName.CouponWorth = Forms!CouponNames!TxtWorth, " & _
"PayName.CouponPercent = Forms!CouponNames!TxtPercent,
" & _
"PayName.ExpirationDate = Forms!CouponNames!TxtExp " & _
"WHERE PayName.PaymentNameID = Forms!CouponNames!TxtID;"
DoCmd.RunSQL (PerUpSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
DoCmd.OpenForm "FXMsgInfo"
Forms!FXMsgInfo!TxtMsg = "UPDATE SUCCESSFUL"
End If
End If
End Sub
 
K

Ken Snell \(MVP\)

Before anyone can say if it's "correct", we would have to know what that
means (we don't know your data nor what you want to have happen). However,
if it works, then it may indeed be correct.

--

Ken Snell
<MS ACCESS MVP>

DS said:
Can anyone let me know if this if Statement is correct. It works but
looks a little scary !
Thanks
DS

Private Sub ImageSave_Click()
Me.CommandHide.SetFocus
If IsNull(Me.TxtName) _
Or Me.TxtName = "" _
Or IsNull(Me.TxtType) _
Or Me.TxtType = "" _
Or IsNull(Me.TxtWorth) _
Or Me.TxtWorth = "" _
Or IsNull(Me.TxtPercent) _
Or IsNull(Me.TxtExp) _
Or Me.TxtExp = "" _
Then
DoCmd.OpenForm "FXMsgWarning"
Forms!FXMsgWarning!TxtMsg = "EMPTY FIELD"
ElseIf Me.TxtAction = 0 Then
If Me.TxtWorth = "Dollar" Then
Me.CommandHide.SetFocus
Me.TxtStatus = "SAVED"
Me.TxtID = Nz(DMax("[PaymentNameID]", "PayName"), 0) + 1
Dim DSQL As String
DoCmd.SetWarnings False
DSQL = "INSERT INTO PayName " & _

"(PaymentNameID,PaymentName,PaymentType,ExpirationDate,Active, " & _
"CouponType,CouponWorth,CouponAmount) " & _
"Values(" & Forms!CouponNames!TxtID & ", " & _
"'" & Forms!CouponNames!TxtName & "', " & _
"" & 3 & ", " & _
"" & Format(Forms!CouponNames![TxtExp],
"\#mm\/dd\/yyyy\#") & ", " & _
"" & Forms!CouponNames![ChkActive] & ", " & _
"" & Forms!CouponNames!TxtType & ", " & _
"'" & "Dollar" & "', " & _
"'" & Forms!CouponNames!TxtAmount & "')"
DoCmd.RunSQL (DSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
Me.TxtStatus = ""
Me.TxtID = 0
Me.TxtName = ""
Me.TxtType = ""
Me.ChkActive = -1
Me.LblActive.Visible = True
Me.TxtWorth = ""
Me.TxtExp = Date
Me.TxtAmount.Visible = False
Me.Label37.Visible = False
Me.TxtPercent.Visible = False
Me.Label38.Visible = False
ElseIf Me.TxtWorth = "Percent" Then
Me.CommandHide.SetFocus
Me.TxtStatus = "SAVED"
Me.TxtID = Nz(DMax("[PaymentNameID]", "PayName"), 0) + 1
Dim PSQL As String
DoCmd.SetWarnings False
PSQL = "INSERT INTO PayName " & _

"(PaymentNameID,PaymentName,PaymentType,ExpirationDate,Active, " & _
"CouponType,CouponWorth,CouponPercent) " & _
"Values(" & Forms!CouponNames!TxtID & ", " & _
"'" & Forms!CouponNames!TxtName & "', " & _
"" & 3 & ", " & _
"" & Format(Forms!CouponNames![TxtExp],
"\#mm\/dd\/yyyy\#") & ", " & _
"" & Forms!CouponNames![ChkActive] & ", " & _
"" & Forms!CouponNames!TxtType & ", " & _
"'" & "Percent" & "', " & _
"'" & Forms!CouponNames!TxtPercent & "')"
DoCmd.RunSQL (PSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
Me.TxtStatus = ""
Me.TxtID = ""
Me.TxtName = ""
Me.TxtType = ""
Me.ChkActive = -1
Me.LblActive.Visible = True
Me.TxtWorth = ""
Me.TxtAmount.Visible = False
Me.Label37.Visible = False
Me.TxtPercent.Visible = False
Me.Label38.Visible = False
End If
ElseIf Me.TxtAction = 1 Then
If Me.TxtWorth = "Dollar" Then
Me.CommandHide.SetFocus
Dim DollUpSQL As String
DoCmd.SetWarnings False
DollUpSQL = "UPDATE PayName SET PayName.PaymentName =
Forms!CouponNames!TxtName, " & _
"PayName.Active = Forms!CouponNames!ChkActive, " & _
"PayName.CouponType = Forms!CouponNames!TxtType, " & _
"PayName.CouponWorth = Forms!CouponNames!TxtWorth, " & _
"PayName.CouponAmount = Forms!CouponNames!TxtAmount, " & _
"PayName.ExpirationDate = Forms!CouponNames!TxtExp " & _
"WHERE PayName.PaymentNameID = Forms!CouponNames!TxtID;"
DoCmd.RunSQL (DollUpSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
DoCmd.OpenForm "FXMsgInfo"
Forms!FXMsgInfo!TxtMsg = "UPDATE SUCCESSFUL"
ElseIf Me.TxtWorth = "Percent" Then
Me.CommandHide.SetFocus
Dim PerUpSQL As String
DoCmd.SetWarnings False
PerUpSQL = "UPDATE PayName SET PayName.PaymentName =
Forms!CouponNames!TxtName, " & _
"PayName.Active = Forms!CouponNames!ChkActive, " & _
"PayName.CouponType = Forms!CouponNames!TxtType, " & _
"PayName.CouponWorth = Forms!CouponNames!TxtWorth, " & _
"PayName.CouponPercent = Forms!CouponNames!TxtPercent, " &
_
"PayName.ExpirationDate = Forms!CouponNames!TxtExp " & _
"WHERE PayName.PaymentNameID = Forms!CouponNames!TxtID;"
DoCmd.RunSQL (PerUpSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
DoCmd.OpenForm "FXMsgInfo"
Forms!FXMsgInfo!TxtMsg = "UPDATE SUCCESSFUL"
End If
End If
End Sub
Click to expand...
 
D

Douglas J. Steele

I agree with Ken.

However, assuming that it's giving you the correct results, you might
consider changing your condition to

If Len(Me.TxtName & vbNullString) = 0 _
Or Len(Me.TxtType & vbNullString) = 0 _
Or Len(Me.TxtWorth & vbNullString) = 0 _
Or IsNull(Me.TxtPercent) _
Or Len(Me.TxtExp & vbNullString) = 0 _

This should actually be more efficient.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ken Snell (MVP) said:
Before anyone can say if it's "correct", we would have to know what that
means (we don't know your data nor what you want to have happen). However,
if it works, then it may indeed be correct.

--

Ken Snell
<MS ACCESS MVP>

DS said:
Can anyone let me know if this if Statement is correct. It works but
looks a little scary !
Thanks
DS

Private Sub ImageSave_Click()
Me.CommandHide.SetFocus
If IsNull(Me.TxtName) _
Or Me.TxtName = "" _
Or IsNull(Me.TxtType) _
Or Me.TxtType = "" _
Or IsNull(Me.TxtWorth) _
Or Me.TxtWorth = "" _
Or IsNull(Me.TxtPercent) _
Or IsNull(Me.TxtExp) _
Or Me.TxtExp = "" _
Then
DoCmd.OpenForm "FXMsgWarning"
Forms!FXMsgWarning!TxtMsg = "EMPTY FIELD"
ElseIf Me.TxtAction = 0 Then
If Me.TxtWorth = "Dollar" Then
Me.CommandHide.SetFocus
Me.TxtStatus = "SAVED"
Me.TxtID = Nz(DMax("[PaymentNameID]", "PayName"), 0) + 1
Dim DSQL As String
DoCmd.SetWarnings False
DSQL = "INSERT INTO PayName " & _

"(PaymentNameID,PaymentName,PaymentType,ExpirationDate,Active, " & _
"CouponType,CouponWorth,CouponAmount) " & _
"Values(" & Forms!CouponNames!TxtID & ", " & _
"'" & Forms!CouponNames!TxtName & "', " & _
"" & 3 & ", " & _
"" & Format(Forms!CouponNames![TxtExp],
"\#mm\/dd\/yyyy\#") & ", " & _
"" & Forms!CouponNames![ChkActive] & ", " & _
"" & Forms!CouponNames!TxtType & ", " & _
"'" & "Dollar" & "', " & _
"'" & Forms!CouponNames!TxtAmount & "')"
DoCmd.RunSQL (DSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
Me.TxtStatus = ""
Me.TxtID = 0
Me.TxtName = ""
Me.TxtType = ""
Me.ChkActive = -1
Me.LblActive.Visible = True
Me.TxtWorth = ""
Me.TxtExp = Date
Me.TxtAmount.Visible = False
Me.Label37.Visible = False
Me.TxtPercent.Visible = False
Me.Label38.Visible = False
ElseIf Me.TxtWorth = "Percent" Then
Me.CommandHide.SetFocus
Me.TxtStatus = "SAVED"
Me.TxtID = Nz(DMax("[PaymentNameID]", "PayName"), 0) + 1
Dim PSQL As String
DoCmd.SetWarnings False
PSQL = "INSERT INTO PayName " & _

"(PaymentNameID,PaymentName,PaymentType,ExpirationDate,Active, " & _
"CouponType,CouponWorth,CouponPercent) " & _
"Values(" & Forms!CouponNames!TxtID & ", " & _
"'" & Forms!CouponNames!TxtName & "', " & _
"" & 3 & ", " & _
"" & Format(Forms!CouponNames![TxtExp],
"\#mm\/dd\/yyyy\#") & ", " & _
"" & Forms!CouponNames![ChkActive] & ", " & _
"" & Forms!CouponNames!TxtType & ", " & _
"'" & "Percent" & "', " & _
"'" & Forms!CouponNames!TxtPercent & "')"
DoCmd.RunSQL (PSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
Me.TxtStatus = ""
Me.TxtID = ""
Me.TxtName = ""
Me.TxtType = ""
Me.ChkActive = -1
Me.LblActive.Visible = True
Me.TxtWorth = ""
Me.TxtAmount.Visible = False
Me.Label37.Visible = False
Me.TxtPercent.Visible = False
Me.Label38.Visible = False
End If
ElseIf Me.TxtAction = 1 Then
If Me.TxtWorth = "Dollar" Then
Me.CommandHide.SetFocus
Dim DollUpSQL As String
DoCmd.SetWarnings False
DollUpSQL = "UPDATE PayName SET PayName.PaymentName =
Forms!CouponNames!TxtName, " & _
"PayName.Active = Forms!CouponNames!ChkActive, " & _
"PayName.CouponType = Forms!CouponNames!TxtType, " & _
"PayName.CouponWorth = Forms!CouponNames!TxtWorth, " & _
"PayName.CouponAmount = Forms!CouponNames!TxtAmount, " &
_
"PayName.ExpirationDate = Forms!CouponNames!TxtExp " & _
"WHERE PayName.PaymentNameID = Forms!CouponNames!TxtID;"
DoCmd.RunSQL (DollUpSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
DoCmd.OpenForm "FXMsgInfo"
Forms!FXMsgInfo!TxtMsg = "UPDATE SUCCESSFUL"
ElseIf Me.TxtWorth = "Percent" Then
Me.CommandHide.SetFocus
Dim PerUpSQL As String
DoCmd.SetWarnings False
PerUpSQL = "UPDATE PayName SET PayName.PaymentName =
Forms!CouponNames!TxtName, " & _
"PayName.Active = Forms!CouponNames!ChkActive, " & _
"PayName.CouponType = Forms!CouponNames!TxtType, " & _
"PayName.CouponWorth = Forms!CouponNames!TxtWorth, " & _
"PayName.CouponPercent = Forms!CouponNames!TxtPercent, "
& _
"PayName.ExpirationDate = Forms!CouponNames!TxtExp " & _
"WHERE PayName.PaymentNameID = Forms!CouponNames!TxtID;"
DoCmd.RunSQL (PerUpSQL)
DoCmd.SetWarnings True
Me.ListCoupons.Requery
DoCmd.OpenForm "FXMsgInfo"
Forms!FXMsgInfo!TxtMsg = "UPDATE SUCCESSFUL"
End If
End If
End Sub
Click to expand...
Click to expand...
 
D

DS

Ken said:
Before anyone can say if it's "correct", we would have to know what that
means (we don't know your data nor what you want to have happen). However,
if it works, then it may indeed be correct.
Click to expand...
Ken, It does work, it basically checks for empty unbound fields in the
first part. If all of the fields are field in then executes the code.

1) Are all of the fields filled in?
If yes proceed to Number 2, if not MsgBox.
2) Is this an INSERT or an UPDATE?
If this is INSERT then go to Number 3
If this is an UPDATE go to Number 4
3) Is this an "INSERT DOLLAR" or an "INSERT PERCENT"
If this is an INSERT DOLLAR, run Insert Dollar Code
Id this is an INSERT PERCENT, run Insert Percent Code
4) Is this an UPDATE DOLLAR" or an UPDATE PERCENT"
If this is an UPDATE DOLLAR, run Update Dollar Code
Id this is an UPDATE PERCENT, run Update Percent Code

This is the criteria in a nutshell. My concern was this part.
ElseIf Me.TxtAction = 0 Then
If Me.TxtWorth = "Dollar" Then
Perhaps a Select Case would be cleaner?

Thanks
DS
 
D

DS

Douglas said:
I agree with Ken.

However, assuming that it's giving you the correct results, you might
consider changing your condition to

If Len(Me.TxtName & vbNullString) = 0 _
Or Len(Me.TxtType & vbNullString) = 0 _
Or Len(Me.TxtWorth & vbNullString) = 0 _
Or IsNull(Me.TxtPercent) _
Or Len(Me.TxtExp & vbNullString) = 0 _

This should actually be more efficient.
Click to expand...
Thanks Douglas I'll give your suggestion a try>
DS
 
K

Ken Snell \(MVP\)

Ah, "cleaner" vs. "correct" < g >.

From other posts I've seen, there appears to be some evidence that a Select
Case runs faster than a series of If..ElseIf..Else..Then blocks, so changing
to a Select Case might be good. The problem is that your tests dont' all use
the same "values" from controls, so the logic you need to use is a bit
different:

Select Case True
Case IsNull(Me.TxtName), Me.TxtName = "", _
IsNull(Me.TxtType), Me.TxtType = "", _
IsNull(Me.TxtWorth), Me.TxtWorth = "", _
IsNull(Me.TxtPercent), IsNull(Me.TxtExp), _
Me.TxtExp = ""
' put your code here

Case Me.TxtAction = 0
' put your code here

(etc. etc. etc. using the tests from your If statements)

End Select
 
D

DS

Ken said:
Ah, "cleaner" vs. "correct" < g >.

From other posts I've seen, there appears to be some evidence that a Select
Case runs faster than a series of If..ElseIf..Else..Then blocks, so changing
to a Select Case might be good. The problem is that your tests dont' all use
the same "values" from controls, so the logic you need to use is a bit
different:

Select Case True
Case IsNull(Me.TxtName), Me.TxtName = "", _
IsNull(Me.TxtType), Me.TxtType = "", _
IsNull(Me.TxtWorth), Me.TxtWorth = "", _
IsNull(Me.TxtPercent), IsNull(Me.TxtExp), _
Me.TxtExp = ""
' put your code here

Case Me.TxtAction = 0
' put your code here

(etc. etc. etc. using the tests from your If statements)

End Select
Click to expand...
Great! Helped a lot! Thanks Ken.
Sometimes when your building these things you end up with a bit of a
Frankenstein. Then you have to goback and as you say "Clean It Up".
Once again,
Thank You
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL Percent Problem 2
Correct use of IF ElseIf 5

Top