How can I trigger a change in value in a field when the user types in another field

[Vetgeorge]

The field that I want the change to occur in has the choices of "Active" and "Closed". All cases default to Active as a new record, but what I want to happen is to change it to "Closed" when the user types an entry in another field explaining the resolution to close the record.

As it works now, the user must manually change the field to "Closed", but sometimes people forget.....
I want to make it it automatic, when the user types the resolution (because they are closing the case), I want the status to change to "Closed"

Or find some other way to close the status of the case.


Thanks

[alansidman]

Is it possible that the field used to determine "resolution" will contain data that does not resolve/close the incident or will data in that field always indicate a closed resolution?

[Vetgeorge]

Is it possible that the field used to determine "resolution" will contain data that does not resolve/close the incident or will data in that field always indicate a closed resolution?

The field for resolution entry is strictly for entering the closure details of that case. Any other entries prior to closure take place in another field.

I could also use a date field I have that the users have to click in to choose the "Closed date"

[orange]

Have you considered a Button --eg Click to Close This Case

in the code for the click event

if other required fields are populated Then
update the record involved with a ClosedDate
Else
Put a message that says You have not completed the required fields
End If

OR

Hide the button mentioned above until all required fields are valued
then, make the button visible
then Update the ClosedDate

Just a few thoughts to consider

[Vetgeorge]

Thanks for that suggestion, I tried a simple macro that pops up a message reminding people to marked the status as closed as soon as they click on closed date field. Hopefully it works to remind them. But I would like to find a way to make it just happen when they make an entry in the resolution field.

[alansidman]

you could also put an afterupdate event on the resolution field something like this:

If Isnull(ResolutionField) then
me.closedField = Null
Else
me.closedfield.value = "closed"
End if

FYI--this has not been tested as I didn't recreate your issue.

Alan

[orange]

Alan,

IIRC if the Me.Closed control is expecting a string then you must use "" and not NULL.

[alansidman]

Orange--

Excellent Point.

Alan

[Vetgeorge]

Hi, I'm posting a resolution to this case, after I got help from someone I work with. We decided to tie the automatic change in Status value to the ClosedDate field as opposed to the ClosedDescription field as I originally used at the beginning of the thread.

Now, when the user inputs a date in the ClosedDate field, the Status field value changes from the default "Active" to "Closed".
Code

Private Sub ClosedDate_Exit(Cancel As Integer)
If IsDate(Me.ClosedDate.Value) Then
Me.Status.Value = 2
Else
Me.Status.Value = 1
End If
End Sub

(The reason Status.Value says 1 or 2 is because the Primary Key for Active is 1, and PK for Closed is 2 in my database)

[orange]

I don't think this has anything to do with PK. It is about the value you assign to Status to indicate Active or Closed.

You may wish to review the meaning of Primary Key.
see http://databases.about.com/cs/admini...primarykey.htm

[Vetgeorge]

I don't think this has anything to do with PK. It is about the value you assign to Status to indicate Active or Closed.

You may wish to review the meaning of Primary Key.
see http://databases.about.com/cs/admini...primarykey.htm

I could be confused about it, but in this database, the Status is a lookup table, and it happens to have those Primary keys for those particular values

[orange]

Could be?? What is it that is actually being closed? A project record? An Invoice?
Please tell us about the whole application.
In your Status look up table, the 1 and 2 are the PKs no doubt. But you have other records in which you populate the StatusCode or something similar with values from your lookup table to show that it is Active or Closed. It isn't clear what that something else is - to me at least.