If statement not working

[mcgraw]

I am trying to write an If statement into the VB event procedure in Access 2007 for a field called Status on a form called [Issues details].

What I would like, is If the status = "Closed" Then Set the close_dt in table Issues to Now().

Both Status and Close_dt are in teh Issues table, but close_dt.

This is what I have so far,

Private Sub Status_AfterUpdate()

Stop
If Me.Status = "closed" Then
close_dt = Now()
End If

I put the stop in to try and see what it was doing, and it appears to run through the close_dt = New() part, but it doesn't actually update the field?

can someone point me in the right direction? I am still trying to learn coding in general...so talk to me like I'm an idiot!

 

[mcgraw]

I forgot to mention. The other thing I want it to do, is If Status <> Closed set close_dt to Null. So, if a project is reopened, it will delete the closed date. (I need to get part A working first.)

 

[dbDamo]

Try this -

If Me.Status = "Closed" Then
Me.Close_dt = Now()
Else
Me.Close_dt Is Null
End If

 

[mcgraw]

Yup. That did it. I also realized that while close_dt was in the table, it wasn't on the form, and it kept giving me problems until I added it.

Thanks for the help!

 

[dbDamo]

No problem

 

[boblarson]

Yup. That did it. I also realized that while close_dt was in the table, it wasn't on the form, and it kept giving me problems until I added it.

And that is due to you not knowing that you can modify a FIELD even if it is NOT on the form (it has to be in the form's record source though) but you can't use the DOT syntax and instead need to use the BANG syntax:

If Me.Status = "Closed" Then
   Me!close_dt = Now()
Else
   Me!close_dt = Null
End If

 

[mcgraw]

Now I know how to Bang!

If I wanted to change it up, and say If me.status = "Closed" OR "Unfunded" OR "Cancelled"

How would I do that?

Do I do this?

If Me.Status = "Closed" Then
Me.Close_dt = Now()
End If

If Me.Status = "Unfunded" Then
Me.Close_dt = Now()
End If

IF Me.Status = "Cancelled" Then
Me.Close_dt = Now()
Else

Me.Close_dt Is Null
End If

Or is there a way to Say If me.status = "Closed" Or "Unfunded" or "Cancelled"?
(I tried both, and they both threw up on me, so I'm a bit stuck...imagine that!)

 

[boblarson]

It would need to be

If  Me.status = "Closed" OR  Me.status = "Unfunded" OR Me.Status = "Cancelled"

but I would use a select case in this case

Select Case Me.Status
   Case "Closed", "Unfunded", "Cancelled"
        Me.Close_dt = Now()
   Case Else
        Me.Close_dt = Null
End Select

 

[mcgraw]

Awesome! You have saved my butt several times! Thanks!

 

[boblarson]

Glad that we could assist.

 

[gemma-the-husky]

one other thing - personally, i would use numeric values for your statuses, and have a lookup table - at some point you are going to want to

a) add new statuses
b) change descriptions

and it will be far easier if you use numeric codes

 

[mcgraw]

one other thing - personally, i would use numeric values for your statuses, and have a lookup table - at some point you are going to want to

a) add new statuses
b) change descriptions

and it will be far easier if you use numeric codes

Currently the status field is set to Text, as a combo box value list...and then the row source has all the statuses...you are saying there's a more efficient way to do it?