Update Field Based on Another Field

[Adrianna]

I know...I know...this topic is highly exhausted, but I just can't figure out what the heck I'm doing wrong. So...please bear with me.

I have two fields that I want to automatically full based upon information in other fields. I'm not looking up values...just assigning them based upon other fields. So, the first one....if an [Accreditation Date] is Provided, then I want the [Accredited] checkbox to be checked (-1), so I used:

If IsNull (Me.Accreditation_Date) Then
Me.Accredited = 0
Else
Me.Accredited = -1
End If

Should be pretty straight forward, but nothing happens...no checkmark.

Then I have a similar problem with the Status Field, which should show one of three conditions:

If IsNull(Me.Daft_Date) Then
Me.Accred_Status = "None"
Else
If IsNull(Me.Approved_Date) Then
Me.Accred_Status = "Intermediate"
Else
Me. Accred_Status = "Completed"
End If
End If

This does nothing.....at all I can't figure out what the heck I'm doing wrong. I'm feeling like I just sat down to ACCESS for the first time....not a good feeling today. Please help!!

I've tried Before Update and even After Update, but nothing

 

[rhounsome]

OK this isn't VB Scripting as such but still does the same thing. If you can adpat it it might help.

Status: IIf([Company]![Acceptance Date]=#01/01/90#,"In Progress",IIf(Now()>[Company]![Review Date],"Out of Programme",IIf(Now()>[Company]![Review Date]-31,"Review","Active")))

This query allows 4 possibly results from the input of information.

HTH

 

[Graham T]

In reply to checkbox query only...

Adrianna

This should work for the checkbox query, not sure on the second query as you are testing different conditions and applying a value to only one option. Hopefully one of the experts will point you in the right direction with that one !!

' Placed in the After Update of the Accreditation_Date field

Private Sub Accreditation_Date_AfterUpdate()
    If IsNull(Me.Accreditation_Date) Or Me.Accreditation_Date = "" Then
        Me.Accredited = 0
    Else
        Me.Accredited = -1
    End If
End Sub

HTH

Graham

 

[Adrianna]

Graham,
Thanks for the input about the checkbox. I actually decided to delete the checkbox all together. Who needs to see a check box if there is already a date. I'm hoping that it will be obvious enough that having a date means that it has been completed.
As for the second part.....still no solutions This should be simple....but no cigar

 

[Adrianna]

Like always..You're Brilliant Pat! That fixed it like a charm! I knew that it had to be something simple that I was over looking. I think my mind is just on holiday with the rest of my office

Thanks All!

 

[Adrianna]

Same thing on a Grander scale

Pat....or anyone else that can help!

Okay....I'd tested everything out on the question above and all seems to be working well....but if I have to place the code into each variable as well as the form, then this next problem is going to be cumbersome and seem very redundant:

The overall purpose of this database is to track the status of projects, so I need to have the [Project Stage] field update when all of the fields for the stage have been completed. In essense, there are about 32 variables that affect the stage and I think it would be a little silly to place in the same code in 32 places. Since the code will have to watch for the condition of all of these fileds being filled in.....the code itself will be long too.

Any ideas? Is there a way around the long code, or the multiple places that I must repeat it?

Thanks

 

[Rich]

Place the code in the form_current, in the after update event of the fields that affect the result put Form_Current

 

[Adrianna]

Rich,
That was the question....I was wondering if there was a way around placing the same code in 33 locations. Seems very redundant to me. Can't I place it in the OnCurrent and the OnChange events of the form without having to place the code in every single field that affects that overall status?

 

[Rich]

Your not putting the code into 33 places, just put the code in the Form_Current event, then type literally Form_Current into the after update events you require, the code will then be Called when required.
HTH
Form_Current
MyCode etc
End Sub


MyTextBox_AfterUpdate
Form_Current
End Sub

 

[Adrianna]

Rich,
THank you so much. I figured there had to be a way to call a "method" or "function" from another event. That's exactly what I was looking for. Thank you so much!!!