I need to update one field based on another.

[JamieF]

I need to update one field based on the entry in one or more other fields. The scenario is that we have an incident "state" field, which will be a value between 1 and 4. The value for "state" corresponds to a series of date fields on the form. Right now, "state" is a manual field, but we're finding that user's forget to update it when filling in one of the date fields.
The correspondence is:
State one - "reported date" filled in (and no others)
state two - "verified date" filled in (and no others)
state three - "work started" or "work completed" or "2nd verification" or
"delivered" or "accpeted" filled in (one or all can be filled in to qualify).
state four - this is an on-hold state and can only be assigned manually
What I want to do is add some code to the On Update property for each of the date fields that will check to see if the date is filled in (and others are not, as
appropriate) and update the "state" field to the correct value. What kind of code/macro do I use? Is a code example available?

 

[Lightning]

Try using a "Select Case" statement in your form's VBA module. Use the After Update event of your Date fields to set the values as required.

See the On-Line Help topic "Using Select Case Statements" for info on these. This also includes a code sample.


HTH

Lightning

 

[Terpsfan]

This may get you started:
Form_BeforeUpdate()
If ([ReportedDate] = Not Null) And ([VerifiedDate] = Null]) And ([WorkStarted] = Null) And ([WorkCompleted] = Null)...Then
[State] = 1
End If

 

[JamieF]

I tried Omega36's suggestion with the IF/Then statement on the Form_BeforeUpdate(). I've got the code entered and no errors are reported, but the [state] field doesn't get updated. I'm not sure how to tell if the code is wrong, or not running at all. I haven't implemented any ELIF statements - I wanted to get this working first. Any ideas? Here's the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ([VDate] = Not Null) And ([WSDate] = Null) And ([FDate] = Null) And ([FOKDate] = Null) And ([DDate] = Null) And ([COKDate] = Null) Then
[State] = 2
End If
End Sub

 

[Rohdem]

I have several suggestions that may or may not help...

1. Try using "" instead of NULL
2. Instead of using the before update event of the forum, try using the after update event of the date boxes. This way the state will change immediately instead of only when you save the record.
3. To see if your code is running, insert the line
msgbox("HI&quot
If you see a messagebox that says HI, then you know your code is running

Mike Rohde
rohdem@marshallengines.com

 

[JamieF]

Getting closer-thank you!. I have been able to get the State field update to work from both the AfterUpdate on the VDate(Verified Date) field and the Before Update for the Form, but only if I only put one selection criteria - example:
If (Not [VDate])Then
[State] = 2
MsgBox ("Hi&quot
End If
** I type in ([VDate] NOT NULL) and it was converted to (Not [Vdate]) - regardless, it works

As soon as I add an "and ([..." for any of the other date fields, the code doesn't work and I get error messages about missing object.

I have tried enclosing the different criteria in another set of parenthesis, but that didn't help - ((xxx)and(xxx)). I'll keep searching, but I'm not finding any way yet to have multiple criteria for the IF command line.