VBA If Statement (1 Viewer)

Weekleyba

Registered User.
Local time
Yesterday, 21:02
Joined
Oct 10, 2013
Messages
586
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!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:02
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Yesterday, 22:02
Joined
Jan 23, 2006
Messages
15,364
Further to the other responses, if ProjectPriority is a number, why does it have a leading 0?
 

Mark_

Longboard on the internet
Local time
Yesterday, 19:02
Joined
Sep 12, 2017
Messages
2,111
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

Registered User.
Local time
Yesterday, 21:02
Joined
Oct 10, 2013
Messages
586
Thanks. I ended up with this:

Code:
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

Registered User.
Local time
Yesterday, 21:02
Joined
Oct 10, 2013
Messages
586
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

Registered User.
Local time
Yesterday, 21:02
Joined
Oct 10, 2013
Messages
586
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!

Code:
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

Wino Moderator
Staff member
Local time
Yesterday, 19:02
Joined
Aug 30, 2003
Messages
36,118
Glad you got it sorted.
 

Users who are viewing this thread

Top Bottom