Need help writing IF code for text box

[rwilliams]

I have a text box called "Approved" on frmPARQ that I am trying to write an event for and am having trouble.

I've created a db to allow users to enter and store information off of paper forms. There are 10 check boxes on frmPARQ that are yes/no questions. There is also a check box called "Signed" that when checked states that the applicant signed the form.

If the applicant answers Yes to any of the 10 questions, they must have physicians consent to participate, otherwise, they are approved. I've set up an option group of 3 choices, option 3 being that the applicant was given full consent to participate.

To be approved, the applicant must sign the form, and have answered no to all 10. Or, have signed the form, and gotten physicians consent.

Here's what I'm wanting to happen, in pseudo code:

If  	[tblPARQ].[signed]=-1
AND	[tblQuestions].[Q1]=0
AND	[tblQuestions].[Q2]=0
AND	[tblQuestions].[Q3]=0
AND	[tblQuestions].[Q4]=0
AND	[tblQuestions].[Q5]=0
AND	[tblQuestions].[Q6]=0
AND	[tblQuestions].[Q7]=0
AND	[tblQuestions].[Q8]=0
AND	[tblQuestions].[Q9]=0
AND	[tblQuestions].[Q10]=0
Then	[tblPARQ].[Approved]="Yes"
Else If	[tblPARQ].[signed]=-1
AND	[tblPhysicianConsent].[ParticipationOption]=3
Then	[tblPARQ].[Approved]="Yes"
Else	[tblPARQ].[Approved]="No"
End If

I don't know how to write VB. I also need the "yes" or "no" value stored in tblPARQ. Help?

 

[MarkK]

Sum the tblQuestions.Questions, and if they sum to zero they are all false. Write a function that does that and include that in your boolean evaluation.

Function SumQuestions() as boolean
' pseudo code
  for i = 1 to 10
    sum = sum + tblQuestions.["Q" & i]
  next i
  SumQuestions = sum
end function

Your boolean expression can look like...

Approved = _
  (Signed AND SumQuestions) _
  OR (Signed AND Option = 3)

 

[rwilliams]

Thank you for the reply lagbolt, seems like a smart idea, however, I don't know, and can't seem to find, the correct syntax to write what you've told me to write. And I'm not sure where to place the code once it's written.

I hate to be a bother, but if you could, or anyone, write out the code i need? Or maybe point me to a good website so I can try to piece together some VB knowledge for this.

Thanks in advance.

I've attached the db - maybe it will be helpful.

-Rachel

 

[MarkK]

Here's one way. I've done the approval calculations in the record source of frmPARQ. I'd recommend that you calculate the approval status like this. What you want to absolutely avoid is the case where the your saved approval status, and the data that yields the approval are in conflict.
Using the calculation ensures that the Approval field is ALWAYS based in the data.

 

[rwilliams]

Absolutely wonderful Lagbolt! Thank you!

However, silly me, when looking through it, noticed that I'd left out a possibility. The DoctorConsent can be yes if it is 1 or 3.

So I'm trying to make this minor change in the code you put in the Record Source.

SELECT tblPARQ.*, Not CBool(nz([q1]+[q2]+[q3]+[q4]+[q5]+[q6]+[q7]+[q8]+[q9]+[q10],True)) AS AllQuestionsNegative, nz([ParticipationChoice],-1)=3 AS DoctorConsent, [Signed] And ([AllQuestionsNegative] Or [DoctorConsent]) AS IsApproved FROM (tblPARQ LEFT JOIN tblPhysicianConsent ON tblPARQ.Social=tblPhysicianConsent.Social) LEFT JOIN tblQuestions ON tblPARQ.Social=tblQuestions.Social;

I tried changing

nz([ParticipationChoice],-1)=3 AS DoctorConsent

in various ways and can't get it to work right.

Sorry I should have noticed that before I posted my question.
I've really appreciated all of your help with this.

 

[rwilliams]

Oh one other thing I noticed, I'd like for new records to default "Yes" for All Questions No.

I've attached an updated version of the database. It's mostly formatting changes, but you'll see the text box I'm talking about in the "Status" area on frmPARQ.

I've played and played with the record source query, looked up those functions, and I'm just not able to change it to work correctly.

 

[MarkK]

Try this...

Nz(ParticipationChoice,-1)=3 OR Nz(ParticipationChoice,-1)=1 AS DoctorConsent