Automatic statsus changes based on answered questions

[Guest]

Would like to have a status automatically change based on answers to
questions. Some of the "If, Then" statements are working and the status is
changing as expected. Others are not. Any help that can be provided is
appreciated.

Here is current code:

Private Sub CA_Completion_Acceptable__AfterUpdate()

If Me.[CA Completion Acceptable?] = -1 And Me.[CA Completion Notification
Submitted?] = -1 And Me.[CA Response Acceptable?] = -1 And Me.[CAR Response
Submitted?] = -1 And Me.[CAR Issued?] = -1 Then
Me.CAR_Status = "CA Closure"

End Sub (NOT WORKING)

Private Sub CA_Completion_Notification_Submitted__AfterUpdate()

If Me.[CA Completion Notification Submitted?] = -1 And Me.[CA Response
Acceptable?] = -1 And Me.[CAR Response Submitted?] = -1 And Me.[CAR Issued?]
= -1 Then
Me.CAR_Status = "CA Verification"
End If

End Sub (NOT WORKING)

Private Sub CA_Ready_to_Close__AfterUpdate()

If Me.[CA Ready to Close?] = -1 And Me.[CA Completion Acceptable?] = -1 And
Me.[CA Completion Notification Submitted?] = -1 And Me.[CA Response
Acceptable?] = -1 And Me.[CAR Response Submitted?] = -1 And Me.[CAR Issued?]
= -1 Then
Me.CAR_Status = "Closed"
End If

End Sub (NOT WORKING)

Private Sub CA_Response_Acceptable__AfterUpdate()

If Me.[CA Response Acceptable?] = -1 And Me.[CAR Response Submitted?] = -1
And Me.[CAR Issued?] = -1 Then
Me.CAR_Status = "CA Completion Notification"
End If

End Sub (NOT WORKING--SOMETIMES)

Private Sub CAR_Issued__AfterUpdate()

If Me.[CAR Issued?] = -1 Then
Me.CAR_Status = "CAR Response"
Else
Me.CAR_Status = Null
End If

End Sub (WORKING)

Private Sub CAR_Response_Submitted__AfterUpdate()

If Me.[CAR Response Submitted?] = -1 And Me.[CAR Issued?] = -1 Then
Me.CAR_Status = "CAR Response Evaluation"
End If

End Sub (WORKING)

 

[John W. Vinson]

"On Tue, 20 Feb 2007 07:52:47 -0800, Londa Sue

Would like to have a status automatically change based on answers to
questions. Some of the "If, Then" statements are working and the status is
changing as expected. Others are not. Any help that can be provided is
appreciated.

Here is current code:

Private Sub CA_Completion_Acceptable__AfterUpdate()

If Me.[CA Completion Acceptable?] = -1 And Me.[CA Completion Notification
Submitted?] = -1 And Me.[CA Response Acceptable?] = -1 And Me.[CAR Response
Submitted?] = -1 And Me.[CAR Issued?] = -1 Then
Me.CAR_Status = "CA Closure"

End Sub (NOT WORKING)

"Not working" is not particularly clear. What's happening? Do you get
an error message? Are all of these control names in reference to Form
Checkboxes? Is the entire IF statement all on one line (it should be)?

John W. Vinson [MVP]

 

[Guest]

Hi.

"Not working" means the CAR Status field is not changing when the questions
are answered, which are the conditions that must be met and are met. The
only changes that take place all the time are "CAR Response" and "CAR
Response Evaluation". Right now, they are separate IF statements for each
question that needs to be answered. I tried to do a Before Update on the CAR
Status field that had all the IF statements. No go.

"On Tue, 20 Feb 2007 07:52:47 -0800, Londa Sue

Would like to have a status automatically change based on answers to
questions. Some of the "If, Then" statements are working and the status is
changing as expected. Others are not. Any help that can be provided is
appreciated.

Here is current code:

Private Sub CA_Completion_Acceptable__AfterUpdate()

If Me.[CA Completion Acceptable?] = -1 And Me.[CA Completion Notification
Submitted?] = -1 And Me.[CA Response Acceptable?] = -1 And Me.[CAR Response
Submitted?] = -1 And Me.[CAR Issued?] = -1 Then
Me.CAR_Status = "CA Closure"

End Sub (NOT WORKING)

"Not working" is not particularly clear. What's happening? Do you get
an error message? Are all of these control names in reference to Form
Checkboxes? Is the entire IF statement all on one line (it should be)?

John W. Vinson [MVP]

 

[John W. Vinson]

"Not working" means the CAR Status field is not changing when the questions
are answered, which are the conditions that must be met and are met.

You're not asking it to do so under that condition. The code will
change CAR Status when all the conditions are True - not when all the
conditions are answered.

I'm not really visualizing what is going on here! Are these Yes/No
fields in a Table, or just checkboxes on a Form?

John W. Vinson [MVP]

 

[Guest]

Yes, these are "yes/no" questions.

You're not asking it to do so under that condition. The code will
change CAR Status when all the conditions are True - not when all the
conditions are answered.

I'm not really visualizing what is going on here! Are these Yes/No
fields in a Table, or just checkboxes on a Form?

John W. Vinson [MVP]

 

[John W. Vinson]

Yes, these are "yes/no" questions.

I knew that, Londa Sue. That's not what I asked.

Please reread the question.

John W. Vinson [MVP]

 

[Guest]

They are on a form with six tabs for each state. Each tab has a question to
trigger the input of information on the next tab.

(The "LondaSue" threw me for a moment.)

 

[John W. Vinson]

They are on a form with six tabs for each state. Each tab has a question to
trigger the input of information on the next tab.

Again... I'm sorry, but we're just not communicating.

Clearly Me.[CA Response Acceptable?] refers to a form control.

Is this form control a Checkbox Control? or a Combo Box control? or a
Textbox control?

Is [CA Response Acceptable?] the name of a field in your Table? If
not, what is the Control Source property of the control named Me.[CA
Response Acceptable?]?

What is the Recordsource property of this form? Please copy and paste
the SQL of the query or (if it's a table) the fieldnames and data
types of the table.

You're being helpful with the *USER* view of the form. I need the
"under the hood" *DEVELOPER* view of the form to be able to figure out
why the code isn't working.


John W. Vinson [MVP]

 

[Guest]

John,

My apologies. These are combo boxes (yes/no) built from the general table
design (value list). These are fields in my table. The form is built from a
query; here is the SQL:

SELECT tbl_CARinfo.ID, tbl_CARinfo.[IPR ID], tbl_CARinfo.[CAR ID],
tbl_CARinfo.[CAR Assignment Date], tbl_CARinfo.[CAR Assignee],
tbl_CARinfo.[CAR Title], tbl_CARinfo.[CAR Description], tbl_CARinfo.[CA
Response], tbl_CARinfo.[CA Respondee], tbl_CARinfo.[CA Response Date],
tbl_CARinfo.[CA Response Acceptable?], tbl_CARinfo.[CA Reply Description],
tbl_CARinfo.[CA Reply Verificaton Date], tbl_CARinfo.[CA Completion Notice],
tbl_CARinfo.[CA Completion Description], tbl_CARinfo.[CA Completion Date],
tbl_CARinfo.[CA Completion Acceptable?], tbl_CARinfo.[CA Completion
Verification Description], tbl_CARinfo.[CA Completion Verification Date],
tbl_CARinfo.[CA Ready to Close?], tbl_CARinfo.[CA Closure Description],
tbl_CARinfo.[CA Closure Date], tbl_CARinfo.[CAR Due Date],
tbl_CARinfo.Nonconformance, tbl_CARinfo.[Root Cause],
tbl_CARinfo.Observations, tbl_CARinfo.[Issued By], tbl_CARinfo.[Assignee
Manager], tbl_CARinfo.[CAR Status], tbl_CARinfo.[CAR Issued?],
tbl_CARinfo.[CAR Response Submitted?], tbl_CARinfo.[CA Completion
Notification Submitted?], tbl_CARinfo.[Functional Track],
tbl_CARinfo.[Project Name], tbl_CARinfo.[CA Due Date]
FROM tbl_CARinfo;

They are on a form with six tabs for each state. Each tab has a question to
trigger the input of information on the next tab.

Again... I'm sorry, but we're just not communicating.

Clearly Me.[CA Response Acceptable?] refers to a form control.

Is this form control a Checkbox Control? or a Combo Box control? or a
Textbox control?

Is [CA Response Acceptable?] the name of a field in your Table? If
not, what is the Control Source property of the control named Me.[CA
Response Acceptable?]?

What is the Recordsource property of this form? Please copy and paste
the SQL of the query or (if it's a table) the fieldnames and data
types of the table.

You're being helpful with the *USER* view of the form. I need the
"under the hood" *DEVELOPER* view of the form to be able to figure out
why the code isn't working.


John W. Vinson [MVP]

 

[John W. Vinson]

Would like to have a status automatically change based on answers to
questions. Some of the "If, Then" statements are working and the status is
changing as expected. Others are not. Any help that can be provided is
appreciated.

Here is current code:

Private Sub CA_Completion_Acceptable__AfterUpdate()

If Me.[CA Completion Acceptable?] = -1 And Me.[CA Completion Notification
Submitted?] = -1 And Me.[CA Response Acceptable?] = -1 And Me.[CAR Response
Submitted?] = -1 And Me.[CAR Issued?] = -1 Then
Me.CAR_Status = "CA Closure"

End Sub (NOT WORKING)

Ok... this will fire only when the [CA Completion Acceptable?] combo
box is updated, and will only set the Status to CA Closure if all the
other combo boxes have been updated FIRST. If the combos are selected
in any other order, it won't set it.

Since the action depends on multiple form controls, which can be set
in any order, you need to either put this code in the AfterUpdate
event of *every one* of the combo boxes; or in an event that applies
to the form as a whole. I'd suggest putting it in the Form's
BeforeUpdate event.

Strictly speaking, you're violating normalization rules, since the
value of the status depends on multiple other fields in the table;
ideally it would be a calculated field. I can see a case for
denormalizing in this case though, it would be a fairly complex and
fragile expression to calculate it each time.

The same logic applies to the other functions: they'll only work if
the user chooses to select the one with the Sub LAST.


John W. Vinson [MVP]

 

[Guest]

John,

Thank you. I will try the BeforeUpdate event for the form. I've stalled on
the others (I had each combo box with an AfterUpdate event to update the
status, but it kept stalling, hence my question, and your answer to my
dilemma!)

Would like to have a status automatically change based on answers to
questions. Some of the "If, Then" statements are working and the status is
changing as expected. Others are not. Any help that can be provided is
appreciated.

Here is current code:

Private Sub CA_Completion_Acceptable__AfterUpdate()

If Me.[CA Completion Acceptable?] = -1 And Me.[CA Completion Notification
Submitted?] = -1 And Me.[CA Response Acceptable?] = -1 And Me.[CAR Response
Submitted?] = -1 And Me.[CAR Issued?] = -1 Then
Me.CAR_Status = "CA Closure"

End Sub (NOT WORKING)

Ok... this will fire only when the [CA Completion Acceptable?] combo
box is updated, and will only set the Status to CA Closure if all the
other combo boxes have been updated FIRST. If the combos are selected
in any other order, it won't set it.

Since the action depends on multiple form controls, which can be set
in any order, you need to either put this code in the AfterUpdate
event of *every one* of the combo boxes; or in an event that applies
to the form as a whole. I'd suggest putting it in the Form's
BeforeUpdate event.

Strictly speaking, you're violating normalization rules, since the
value of the status depends on multiple other fields in the table;
ideally it would be a calculated field. I can see a case for
denormalizing in this case though, it would be a fairly complex and
fragile expression to calculate it each time.

The same logic applies to the other functions: they'll only work if
the user chooses to select the one with the Sub LAST.


John W. Vinson [MVP]