How to validate three condition, before providing Error Msg (1 Viewer)

kimpida · Feb 11, 2014

:banghead:I am trying to write following code, I want Msg to pop, when all three condition are true, but it not working, any help will be appriciated

'''Non Budgeted Projects need Explanation and Variance class"

If Me.ID.Value >= 90000 And and Me.Variance_Class = "" and Me.Comments_Explanation_Delta_____100K = "" Then

MsgBox "This project is Unbudgeted. Please Add 'Variance Class' and provide Explanation why this project is Unbudgeted project has been added.", vbExclamation, "Rules Checker..."
CheckRules = False
GoTo Exit_CheckRules

pbaldy · Feb 12, 2014

I moved your thread from the introductions forum. In case your fields are Null rather than "", try this test for each:

Nz(Me.Variance_Class, "") = ""

kimpida · Feb 12, 2014

thanks it is working, but now I have an other question.

If (Me.Variance_From_Budget < 99999 And Me.Variance_From_Budget <= -99999) And (Me.Variance_Class = "" Or IsNull(Me.Variance_Class)) Then
MsgBox "This project is onbudget project, please add Variance Class 'Onbudget' .", vbExclamation, "Rules Checker ...."
CheckRules = False
GoTo Exit_CheckRules
End If

My variance from Budget is -200,000, I am still getting the msg, since it is less 100,000.
what I need is a msg, when variance from Budget is 100,000 or -100,000.

Does vba has between operator, like Sql has.

:banghead:

pbaldy · Feb 12, 2014

You're getting the message because it's less than -99999.

Frothingslosh · Feb 12, 2014

In addition,

Code:

If (Me.Variance_From_Budget < 99999 And Me.Variance_From_Budget <= -99999)

is suspect, because if Me.Variance_From_Budget <= -99999, then it will ALWAYS be < 99999. I think you meant for the second one to be '>='. That should work.

As long as you're not submitting a value of -200,000.

pbaldy · Feb 12, 2014

I guess my hint wasn't good enough?

kimpida · Feb 12, 2014

it is working - thanks

kimpida · Feb 14, 2014

Thanks for all your help

One more problem

How to find, the null value in the code?

I am getting following error in my code:

"Invalid use of Null"

pr2-eugin · Feb 14, 2014

Could you show the code (that you are using) which is causing the error?

kimpida · Feb 14, 2014

Code:

'''Non Budgeted Projects need and Variance class
If Me.ID.Value >= 90000 And (IsNull(Me.Variance_Class) Or Me.Variance_Class = "") Then
MsgBox "This project is Unbudgeted. Please Add 'Variance Class' and provide Explanation why this project is Unbudgeted project has been added.", vbExclamation, "Rules Checker..."
'Me.Annual_Summarized_Budget.Locked = True
CheckRules = False
GoTo Exit_CheckRules
End If
'''Non Budgeted Projects need Explanation
If Me.Variance_Class = "Unbudgeted" And (IsNull(Me.Comments_Explanation_Delta_____100K) Or Me.Comments_Explanation_Delta_____100K = "") Then
MsgBox " This is a non budgeted project, please provide an explanation, why this project has been added.", vbExclamation, "Rules Checker..."
'Me.Annual_Summarized_Budget.Locked = True
CheckRules = False
GoTo Exit_CheckRules
End If
 
'''Non Budgeted Projects need Variance class to be Unbudgeted
If Me.ID.Value >= 90000 And Me.Variance_Class <> "Unbudgeted" Then MsgBox "This is a non budgeted project Please change variance class to unbudgeted.", vbExclamation, "Rules Checker..."
'Me.Annual_Summarized_Budget.Locked = True
CheckRules = False
GoTo Exit_CheckRules
End If

this my code and I don't know, where Null is coming from. I am new to Access vba and inhertied this database with any documnetation.

thanks
:banghead:

pr2-eugin · Feb 14, 2014

So check this thread : Please use Code Tags when posting VBA Code edit your previous post. Then I will have a look at it.

http://www.access-programmers.co.uk/forums/showthread.php?t=240420

pr2-eugin · Feb 14, 2014

kimpida said:
this my code and I don't know, where Null is coming from. I am new to Access vba and inhertied this database with any documnetation.

You would exactly know where this Null is coming from. Have you left Variance_Class or Comments_Explanation_Delta_____100K - Null? Does not matter if the ID is >= 90000. If they are Null you will hit a brick wall somewhere, you need to analyze your logic correctly.

kimpida · Feb 14, 2014

Code:

'''Non Budgeted Projects need and Variance class
If Me.ID.Value >= 90000 And (IsNull(Me.Variance_Class) Or Me.Variance_Class = "") Then
MsgBox "This project is Unbudgeted. Please Add 'Variance Class' and provide Explanation why this project is Unbudgeted project has been added.", vbExclamation, "Rules Checker..."
'Me.Annual_Summarized_Budget.Locked = True
CheckRules = False
GoTo Exit_CheckRules
End If
'''Non Budgeted Projects need Explanation
If Me.Variance_Class = "Unbudgeted" And (IsNull(Me.Comments_Explanation_Delta_____100K) Or Me.Comments_Explanation_Delta_____100K = "") Then
MsgBox " This is a non budgeted project, please provide an explanation, why this project has been added.", vbExclamation, "Rules Checker..."
'Me.Annual_Summarized_Budget.Locked = True
CheckRules = False
GoTo Exit_CheckRules
End If

'''Non Budgeted Projects need Variance class to be Unbudgeted
If Me.ID.Value >= 90000 And Me.Variance_Class <> "Unbudgeted" Then 
 
MsgBox "This is a non budgeted project Please change variance class to unbudgeted.", vbExclamation, "Rules Checker..."
'Me.Annual_Summarized_Budget.Locked = True
CheckRules = False
GoTo Exit_CheckRules
End If
[\CODE]

this my code and I don't know, where Null is coming from. I am new to Access vba and inhertied this database with any documnetation.

thanks

Frothingslosh · Feb 14, 2014

This is how your code should be presented here:

Code:

Public Function TempFunction()
 
    '''Non Budgeted Projects need and Variance class
    If Me.ID.Value >= 90000 And (IsNull(Me.Variance_Class) Or Me.Variance_Class = "") Then
        MsgBox "This project is Unbudgeted. Please Add 'Variance Class' and provide Explanation why this project is Unbudgeted project has been added.", vbExclamation, "Rules Checker..."
        'Me.Annual_Summarized_Budget.Locked = True
        CheckRules = False
        GoTo Exit_CheckRules
    End If
 
    '''Non Budgeted Projects need Explanation
    If Me.Variance_Class = "Unbudgeted" And (IsNull(Me.Comments_Explanation_Delta_____100K) Or Me.Comments_Explanation_Delta_____100K = "") Then
        MsgBox " This is a non budgeted project, please provide an explanation, why this project has been added.", vbExclamation, "Rules Checker..."
        'Me.Annual_Summarized_Budget.Locked = True
        CheckRules = False
        GoTo Exit_CheckRules
    End If
 
    '''Non Budgeted Projects need Variance class to be Unbudgeted
    If Me.ID.Value >= 90000 And Me.Variance_Class <> "Unbudgeted" Then
        MsgBox "This is a non budgeted project Please change variance class to unbudgeted.", vbExclamation, "Rules Checker..."
        'Me.Annual_Summarized_Budget.Locked = True
        CheckRules = False
        GoTo Exit_CheckRules
    End If
 
End Function

Also, don't forget the Ten Commandments of Access. In this case:

Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you from the path of righteousness.

kimpida · Feb 14, 2014

Sorry about it. My new at it. Please be paitent with me.

thanks

Frothingslosh · Feb 14, 2014

kimpida said:
Sorry about it. My new at it. Please be paitent with me.

thanks

I knew I should have put a smiley at the end. No worries!

How to validate three condition, before providing Error Msg (1 Viewer)

kimpida

Registered User.

pbaldy

Wino Moderator

kimpida

Registered User.

pbaldy

Wino Moderator

Frothingslosh

Premier Pale Stale Ale

pbaldy

Wino Moderator

kimpida

Registered User.

kimpida

Registered User.

pr2-eugin

Super Moderator

kimpida

Registered User.

pr2-eugin

Super Moderator

pr2-eugin

Super Moderator

kimpida

Registered User.

Frothingslosh

Premier Pale Stale Ale

kimpida

Registered User.

Frothingslosh

Premier Pale Stale Ale

Users who are viewing this thread