Problems with an update query

[Monroeski]

I have a form that has two subforms on it - one of them is continuous and lists a bunch of data about a given service's performance measures, while the other is a single view form where the user selects a measure from a combo box (cbo_Measure). After a measure is selected, the other text boxes in the form are updated with relevant information from the database. I am trying to set it up so that after the user updates the action plan for that measure, the form checks to see if the ActionPlan textbox is empty; if it is empty, I want to update the "ActionPlanCompleted" field to "No," and if there is text in there I want to update it to "Yes." I then requery the other subform to display the change.

Here is the code I have so far -

Private Sub ActionPlan_AfterUpdate()
Dim strSQL As String
Dim strCriteria As String

strCriteria = ""
If Me!ActionPlan.Value = "" Then
strCriteria = "Yes"
Else
strCriteria = "No"
End If

MsgBox (Me!cbo_Measure)
MsgBox (Me![cbo_Measure])
MsgBox (Service_ID)
MsgBox (strCriteria)
strSQL = "UPDATE tbl_Service_PM " & " SET tbl_Service_PM.ActionPlanComplete = " & strCriteria & " WHERE tbl_Service_PM.Service_ID = " & Service_ID & " AND tbl_Service_PM.Measure = " & Me![cbo_Measure]
MsgBox (strSQL)
DoCmd.RunSQL strSQL
Forms![frm_PerformanceMeasure]![sfrm_Service_PM].Requery
End Sub

All of the message boxes are popping up with the correct values in them, except for strCriteria. That shows up as "No" whether the text box is blank or not. I am having three other problems with this -

1. tbl_Service_PM.ActionPlanComplete is not getting updated at all, though I get a message saying that 1 row has been updated.
2. When it runs, it always prompts me for a value of cbo_Measure. For instance, if the value in the combo box is MC3, a prompt pops up for me to give a value for MC3. If I type in anything except for MC3, the message saying how many rows were updated gives me a 0.
3. After the update, the next time I bring the focus to the other subform, I get a Write Conflict error message.

I've been stuck on this for a few hours already this morning, and it's really starting to stress me out.

[FishVal]

Hi, Monroeski.

I think you don't need at all to store strCriteria in the table as soon as value of tbl_Service_PM.ActionPlan is the criteria itself.
Simply retrieve it's value with a query like this

SELECT *, iif(isnull(tbl_Service_PM.ActionPlan), "No", "Yes") AS ActionPlanComplete FROM tbl_Service_PM;
 

[Monroeski]

Would IsNull even pick that up? A zero-length string isn't the same thing as Null, is it?

::edit::
nm, just tried it, and it returns correctly. Still not updating the table correctly, though.

None of the forms involved actually uses tbl_Service_PM as a record source, they both use queries (which do contain fields from tbl_Service_PM). Could that be part of the problem? It's really confusing me why it tells me that a record has been updated when I can go into the table and see with my own eyes that it hasn't.

[FishVal]

Would IsNull even pick that up? A zero-length string isn't the same thing as Null, is it?

::edit::
nm, just tried it, and it returns correctly. Still not updating the table correctly, though.

None of the forms involved actually uses tbl_Service_PM as a record source, they both use queries (which do contain fields form tbl_Service_PM). Could that be part of the problem? It's really confusing me why it tells me that a record has been updated when I can go into the table and see with my own eyes that it hasn't.

????
Is tbl_Service_PM.ActionPlan field value not a strict criteria for ActionPlan being completed?

[Monroeski]

????
Is tbl_Service_PM.ActionPlan field value not a strict criteria for ActionPlan being completed?

Not sure exactly what you're asking. I can say that the only thing that changes ActionPlanCompleted from yes to no and vice-versa is the ActionPlan text box in this form. It is set up to run the above VBA code AfterUpdate.

[FishVal]

Not sure exactly what you're asking. I can say that the only thing that changes ActionPlanCompleted from yes to no and vice-versa is the ActionPlan text box in this form. It is set up to run the above VBA code AfterUpdate.

I mean the following.

What a reason to have [ActionPlanCompleted] field in the table as soon as it only indicates whether [ActionPlan] is null or not? Or [ActionPlanCompleted] value is not so strictly depends on [ActionPlan] value?

The problem is that ActionPlan text box is only supposed to be "the only thing that changes ActionPlanCompleted from yes to no and vice-versa".

[Monroeski]

I mean the following.

What a reason to have [ActionPlanCompleted] field in the table as soon as it only indicates whether [ActionPlan] is null or not? Or [ActionPlanCompleted] value is not so strictly depends on [ActionPlan] value?

The problem is that ActionPlan text box is only supposed to be "the only thing that changes ActionPlanCompleted from yes to no and vice-versa".

I think I see what you're asking. The ActionPlanCompleted field is already in the table beforehand, and every entry is "No." Basically, we'll be rolling this out to the organization with everything blank, then each service inputs their own information. So, the starting value for ActionPlanCompleted is "No," and it will only change to "Yes" after a representative from the service has gone in and added data. Should they happen to go back in to the ActionPlan and delete the information there, I want ActionPlanCompleted to reflect that, so I allowed for it to change back to "No" if that field is null.

I'm starting to have issues with the whole way this thing is set up, I may try to figure out a different way ot track this rather than keep trying to make this one work.

[FishVal]

I think I see what you're asking. The ActionPlanCompleted field is already in the table beforehand, and every entry is "No." Basically, we'll be rolling this out to the organization with everything blank, then each service inputs their own information. So, the starting value for ActionPlanCompleted is "No," and it will only change to "Yes" after a representative from the service has gone in and added data. Should they happen to go back in to the ActionPlan and delete the information there, I want ActionPlanCompleted to reflect that, so I allowed for it to change back to "No" if that field is null.

I'm starting to have issues with the whole way this thing is set up, I may try to figure out a different way ot track this rather than keep trying to make this one work.

Ok. As you like.
To run the update query you just need to enclose string values assigning to text type fields in single quotation marks .

strSQL = "UPDATE tbl_Service_PM " & " SET tbl_Service_PM.ActionPlanComplete = '" & strCriteria & "' WHERE tbl_Service_PM.Service_ID = " & Service_ID & " AND tbl_Service_PM.Measure = " & Me![cbo_Measure] & ";"
 

or if tbl_Service_PM.Measure is text type field too, then

strSQL = "UPDATE tbl_Service_PM " & " SET tbl_Service_PM.ActionPlanComplete = '" & strCriteria & "' WHERE tbl_Service_PM.Service_ID = " & Service_ID & " AND tbl_Service_PM.Measure = '" & Me![cbo_Measure] & "';"
 

[Monroeski]

Ok. As you like.
To run the update query you just need to enclose string values assigning to text type fields in single quotation marks .

strSQL = "UPDATE tbl_Service_PM " & " SET tbl_Service_PM.ActionPlanComplete = '" & strCriteria & "' WHERE tbl_Service_PM.Service_ID = " & Service_ID & " AND tbl_Service_PM.Measure = " & Me![cbo_Measure] & ";"
 

or if tbl_Service_PM.Measure is text type field too, then

strSQL = "UPDATE tbl_Service_PM " & " SET tbl_Service_PM.ActionPlanComplete = '" & strCriteria & "' WHERE tbl_Service_PM.Service_ID = " & Service_ID & " AND tbl_Service_PM.Measure = '" & Me![cbo_Measure] & "';"
 

I actually got the query working (I thought) right before you posted this message, but when I went in to the actual table, instead of just updating the ActionPlanComplete field, it also changed another field from 61 to 3997701 or something like that and a couple of other fields from strings to random symbols. ????

At that point I just decided to drop it until tomorrow, when a cooler head should prevail. : )

Thanks for all the help.

[FishVal]

I actually got the query working (I thought) right before you posted this message, but when I went in to the actual table, instead of just updating the ActionPlanComplete field, it also changed another field from 61 to 3997701 or something like that and a couple of other fields from strings to random symbols. ????

At that point I just decided to drop it until tomorrow, when a cooler head should prevail. : )

Thanks for all the help.

Put

Debug.Print strSQL

before

DoCmd.RunSQL strSQL

line and see what query actually runs.