VBA If Statement

[Weekleyba]

I have a textbox for setting the Project Priority. ProjectPriority
This is a number 01, 02, 03, 04, ...
I would like to have the ProjectPriority automatically change when the project is awarded.
That is, when the DateAwarded is entered, can I have the ProjectPriority change to "Obligated"?

In addition, I want to have this record for the control, change to "Obligate", so I can query the table (ProjectT) later and filter out all the Obligate to show only the Project Priority projects.

In Access I would put: =IIf(IsNull([DateofAward]),[ProjectPriority],"OBL")
but in VBA I'm not sure what to do.

My thought was to use an Event for the Project Priority textbox.
Not sure which one.

Thanks for any help!

 

[pbaldy]

Perhaps something like the second example, in the after update event of the date awarded control.

http://www.baldyweb.com/ConditionalVisibility.htm

 

[theDBguy]

Hi. Pardon me for jumping in, but I'm just a little confused here. If ProjectPriority is a Number field, how come you want to change it to a Text value?

 

[jdraw]

Further to the other responses, if ProjectPriority is a number, why does it have a leading 0?

 

[Mark_]

Important question, if DateOfAward is cleared out, does something have to happen to your ProjectPriority? Likewise if ProjectPriority is already "OBL" should that prevent DateOfAward from being changed or cleared out?

These answers help determine if you should do this is in the DateOfAward's BeforeUpdate or AfterUpdate event.

 

[Weekleyba]

Thanks. I ended up with this:

Private Sub txtDateofAward_AfterUpdate()
If IsNull(Me.DateofAward) Then
Else
    Forms!ProjectF!ProjectGenInfoF.Form.txtProjectPriority = "OBL"
End If
DoCmd.RefreshRecord
End Sub

This should work...
The ProjectPriority field is a short text field.

 

[Weekleyba]

If the DateOfAward is cleared out, the "OBL" text would remain, which would not be wanted, however, this is not likely to happen. (Except by a user error, accidently inputting a date for award but not actually awarded)

I guess I could put is some code that would clear the ProjectPriority if the DateOfAward was deleted?.......

I'll work on that.

 

[Weekleyba]

This is much better!
Now if someone enters an Award Date by mistake and then later deletes it, the Project Priority will change from OBL to empty.
Thanks for the help!

Private Sub txtDateofAward_AfterUpdate()
If Not IsNull(Me.DateofAward) Then
    Forms!ProjectF!ProjectGenInfoF.Form.txtProjectPriority = "OBL"
    Else
    Forms!ProjectF!ProjectGenInfoF.Form.txtProjectPriority = ""
End If
DoCmd.RefreshRecord
End Sub

 

[pbaldy]

Glad you got it sorted.