IF Then Else VBA Access form

cardonas

Registered User.
Local time
Today, 07:02
Joined
Dec 30, 2015
Messages
16
I have two fields in Access form one is [Job Status] and [Term date] I am trying to have a word populate in [job status] based off of what the [term date] field here is my logic is this
If [Term date] is " " or IS NULL I want the word "Active" to populate in the [jobstatus] field
Else if not I want the word "Inactive" in the [jobstatus] field to populate. I tryed the code below but I get nothing. NO error and no words popping up in job status field. I am putting the code in the AFTERUPDATE Property field for Jobstatus

Code:
Private Sub Job_Status_AfterUpdate()
If Me.Term_Date = " " Then
  Me.Job_Status = "Active"
ElseIf Me.Term_Date Is Null Then
  Me.Job_Status = "Active"
Else
    Me.Job_Status = "Inactive"
    End If
End Sub


Any help is greatly appreciated.
 
Why are you handling the AfterUpdate event of JobStatus to set the JobStatus? You will immediately overwrite the update that caused the event to fire in the first place, which makes no sense.

Do you mean the handle the AfterUpdate event of the TermDate?
 
I moved the code on over to the [TERMDATE] Field and now I get an error when I debugg on the IS NULL part of the code. Also I am a NEWBIE at this.

PHP:
Private Sub Term_Date_AfterUpdate()
If Me.Term_Date = " " Then
  Me.Job_Status = "Active"
ElseIf Me.Term_Date Is Null Then
  Me.Job_Status = "Active"
Else
    Me.Job_Status = "Inactive"
    End If
End Sub
 
Last edited:
Your code calculates a Status using a TermDate. If this can be done reliably, there is no need to store the status. Consider the two pieces of data, 1) Age, and 2) Birthdate. They are in fact the same data, one being a calculation on the other using the current date. In this case we don't store BOTH the Age and the birthdate, we only store the birthdate and we calculate the age.

Similarly in your case, if Status can be said to "vary directly" with TermDate, then there is no need to store the Status, simply calculate it in a query. The Status field in a query, using the logic you posted, might look like...
Code:
Status: IIF(Nz([TermDate], "") =  "", "Active", "Inactive")

In VBA, use the IsNull() function . . .
Code:
ElseIf IsNull(Me.Term_Date) Then
 
Wozzers!!!! YOU'RE:D THE BEST !!! Thank you so much !! I did not know that and my code work !!!! Thank you again!!
 

Users who are viewing this thread

Back
Top Bottom