Form not updating with query control source (1 Viewer)

[tezread]

I have a form that captures yes/no responses. The forms control source is a table called tblEpisode

I have a query called qryEpisode, here is part of it:

SELECT tblPatient.Patient_Name, IIf([PastMedical_StrokeTIA]="Yes",2,0) AS StrokeTIAChadsVasc, IIf([Hypertension]="Yes",1,0) AS HypertensionChadsVasc, [StrokeTIAChadsVasc]+[HypertensionChadsVasc] AS SCORE
FROM tblPatient LEFT JOIN tblEpisode ON tblPatient.PatientID = tblEpisode.PatientID;

This bit helps me calculate a score based on the responses.

If i try to have qryEpisode as the control source of the form and add the field SCORE to then form it will not let me update the record.

How can I overcome this

 

[vbaInet]

So remove it from your query and put it in the Control Source of your textbox:

[COLOR=Red][B]=[/B][/COLOR]IIf([PastMedical_StrokeTIA]="Yes",2,0) + IIf([Hypertension]="Yes",1,0)

Actually, you can also do:

=(Abs(Nz([PastMedical_StrokeTIA],0) * 2) + (Abs(Nz([Hypertension],0))

 

[tezread]

So remove it from your query and put it in the Control Source of your textbox:

[COLOR=red][B]=[/B][/COLOR]IIf([PastMedical_StrokeTIA]="Yes",2,0) + IIf([Hypertension]="Yes",1,0)

Actually, you can also do:

=(Abs(Nz([PastMedical_StrokeTIA],0) * 2) + (Abs(Nz([Hypertension],0))

Aha - well well well. I didn't realise you could do that!

Should have updated this thread as I have something working now.
I have a sub form in my form which works (query control source), but it doesn't update the score unless you close and then open the form?

 

[vbaInet]

I have a sub form in my form which works (query control source), but it doesn't update the score unless you close and then open the form?

With your method or the method I gave you?

 

[tezread]

With your method or the method I gave you?

With my method.

 

[vbaInet]

Well, I can't comment on your method without seeing it. Try all the solutions offered first without spending too much pondering on why yours is failing.

 

[tezread]

Well, I can't comment on your method without seeing it. Try all the solutions offered first without spending too much pondering on why yours is failing.

Cool.

I have tried

=IIf([PastMedical_StrokeTIA]="Yes",2,0) + IIf([Hypertension]="Yes",1,0)

in the control source but the result is

#Name?

 

[vbaInet]

That's one out of two solutions. What about the other? I know it will result in the same error, but like I previously mentioned, explore all solutions before posting.

Drop those two fields (i.e. Hypertension and the other) on your form and set their Visible property to No.

 

[tezread]

That's one out of two solutions. What about the other? I know it will result in the same error, but like I previously mentioned, explore all solutions before posting.

Drop those two fields (i.e. Hypertension and the other) on your form and set their Visible property to No.

Tried the other solution - as expected the same result.
Sorry but cannot see value in hiding two fields when user needs to see them so they can respond yes/no
Ultimate goal here is to create a score based on series of yes/no answers

 

[vbaInet]

I thought they weren't needed

What are the names of the controls those fields are bound to?

 

[tezread]

Got a solution to this now

I have a subform in it whose control source is querying the current score.
This is calculated thus:

SELECT tblPatient.Patient_Name, tblEpisode.EpisodeID, tblPatient.Unit_Number, tblPatient.PatientID, tblEpisode.Hypertension, tblEpisode.PastMedical_LVSD, tblEpisode.PastMedical_CAD, tblEpisode.PastMedical_PVD, tblEpisode.PastMedical_PAD, tblEpisode.PastMedical_COPD, tblEpisode.PastMedical_StrokeTIA, tblEpisode.Past_medical_history_Other, DateDiff("yyyy",[DOB],[Date_seen]) AS AgeOnDateSeen, IIf([DM]="Yes",1,0) AS DMChadsVasc, IIf([Valve_Dis]="Yes",1,0) AS ValveDisChadsVasc, IIf([PastMedical_LVSD]="Yes",1,0) AS LVSDChadsVasc, IIf([PastMedical_CAD]="Yes",1,0) AS CADChadsVasc, IIf([PastMedical_PVD]="Yes",1,0) AS PVDChadsVasc, IIf([PastMedical_PAD]="Yes",1,0) AS PADChadsVasc, IIf([PastMedical_StrokeTIA]="Yes",2,0) AS StrokeTIAChadsVasc, IIf([Hypertension]="Yes",1,0) AS HypertensionChadsVasc, IIf([MI]="Yes",1,0) AS MIChadsVasc, IIf([AgeOnDateSeen]>=75,2,IIf([AgeOnDateSeen] Between 65 And 74,1,0)) AS AgeChadsVasc, IIf([Gender]="Female",1,0) AS GenderChadsVasc, [AgeChadsVasc]+[GenderChadsVasc]+[DMChadsVasc]+[ValveDisChadsVasc]+[LVSDChadsVasc]+[CADChadsVasc]+[PVDChadsVasc]+[PADChadsVasc]+[StrokeTIAChadsVasc]+[HypertensionChadsVasc]+[MIChadsVasc] AS CHADS2VASC
FROM tblPatient LEFT JOIN tblEpisode ON tblPatient.PatientID = tblEpisode.PatientID;

In each of the yes/no boxes I have put in the 'onchange' event

DoCmd.RefreshRecord

then if you toggle yes/no the score updates immediately in the subform

 

[tezread]

Many thanks for your support vbainet (again!)

 

[vbaInet]

Many thanks for your support vbainet (again!)

You're welcome tezread!

... but

Got a solution to this now

I have a subform in it whose control source is querying the current score.
This is calculated thus:
In each of the yes/no boxes I have put in the 'onchange' event

DoCmd.RefreshRecord

The After Update or On Click events will suffice and all refresh record is doing is saving changes to the current record and refreshing the entire form so you can also use:

DoCmd.RunCommand acCmdSaveRecord

In any case, I would advise that you ensure that the names of the controls are not the same as the fields they are bound to then use the controls names in the calculation. That should update without needing to save.