prevent changes to control on combobox afterupdate

[Jeffrey Tan]

Hi.

I have a combobox on a form called Status, with a field list with the values "OPEN", "CLOSED", "CANCELED".

How do I code in VBA that if the status of a record is already set to either CLOSED or CANCELED, but is later changed back to OPEN, messagebox the user that status has changed from CANCELED/CLOSED to OPEN?

do i need something like a nested if in the after update event?

Thanks!

[NeoPa]

It should be as simple as using the Status_AfterUpd ate() procedure as you said. Check the .Value and the .OldValue match what you need to trigger your MsgBox() call.

[Jeffrey Tan]

@NeoPa..oh wow.. i never knew there was a .OldValue property.

so it would be something like

If Status.Value <> Status.OldValue Then
my code would write to a child subform and put in the users' windows ID, date and time old ticket was changed from CANCELED/CLOSED to open.

Thanks again!

[NeoPa]

No. Not really.

If you are in the AfterUpdate event procedure you can be pretty sure that .Value is not equal to .OldValue. You need to check .OldValue against "CLOSED" and "CANCELED", as well as checking .Value against "OPEN". Only then will you know whether or not to execute any other code you want to (which would obviously include whatever is covered by your pseudocode from post #3).

[Jeffrey Tan]

@NeoPa:

how about this?

If Combo46.OldValue = "CLOSED" Or Combo46.OldValue = "CANCELED" And Combo46.Value = "OPEN" Then
'code to execte

im thinking about whether or not this should be in the Change event.

[NeoPa]

That would probably work, but unless you know the priorities of Or and And I would certainly include parentheses around the Or part (that you need to be executed first).

I would suggest :

With Combo46
    If (.OldValue = "CLOSED" Or .OldValue = "CANCELED") And .Value = "OPEN" Then
        '...
    End If
End With

[Jeffrey Tan]

@NeoPa:

i just thought of something else...

in my table, let's say the status of record #1 is CLOSED. in the form that links to the table, when i switch to OPEN (before saving), is that value still CLOSED, or when i hit the save button, value is now "OPEN" and oldvalue is "CLOSED".

selecting an item from the combobox doesnt change the value yet unless the record is saved?

[NeoPa]

Jeffrey,

The .OldValue property reflects the unedited value of the field in a bound control. It doesn't change when the .Value changes, but only when the record is saved.

This is probably not a good way to discover this information as it is easily and much more quickly available using Context-Sensitive Help. I can only assume you were not previously aware of this, so I hope this helps you enormously with your progress in learning Access.

[Jeffrey Tan]

@NeoPa:

Yeah I was missing the ( ). Huge difference. Code works perfectly.

here's what i have :)

    With Combo46
        If (.OldValue = "CLOSED" Or .OldValue = "CANCELED") And .Value = "OPEN" Then
            date_closed = ""
            Combo56 = ""
            With Me.Child39
            DoCmd.GoToControl "Child39"
            DoCmd.GoToRecord , , acNewRec
            .Form![date] = Now()
            .Form![user] = user_name
            .Form![notes] = "Closed Ticket re-opened"
            End With
        End If
    End With

[NeoPa]

Good to hear it Jeffrey :-)

I would make a couple of quick comments on your code :

1. Empty controls reflect Null - not empty strings. Line #3 should be :
   

   date_closed = Null

2. Now() doesn't represent a Date value. It represents a Date/Time value. Unless the field/control named [Date] is misnamed then it should be set to Date() instead.