Hello,

I am working with two forms, one called Studies and one called Results. I have a checkbox called Effective on the Results form that I want to automatically check if certain conditions are met in other controls on the Studies and Results forms. Here is my code:

Private Sub EffectSize_AfterUpdate()
If Me.StudyType = "Intervention evaluation" And Form_Studies.Duration = "High" Or Form_Studies.Duration = "Medium" And Me.EffectSize = "Net positive" Then
Me.Effective = "yes"
End If
End Sub

I don't get an error message when I use the form, but the Effective control doesn't check either. I tried using -1 instead of "yes" to no avail.

Any help is appreciated!

Try:



Are you sure, though, that your condition is correct? Remember that Ands get evaluated before Ors. That means you've got:



BTW, you could rewrite your code as

Doug,

Thank you for your response. I tried using True and re-writing the code as you suggested, but still cannot get the checkbox checked.

The conditions I need met are as follows:

Me.StudyType = Intervention evaluation
Me.EffectSize = Net positive
Form_Studies.Duration = either High or Medium

Am I accomplishing that with my use of Ands & Or?

Sorry: missed the fact that you're using Form_Studies.Duration. If that's supposed to be the value of a control on some other form, it should be Forms!Form_Studies.Duration. If it's supposed to be the value of a control on a subform of the current form, it should be Me!Form_Studies.Form!Duration. Not that that assumes the subform control name on the current form is Form_Studies. Depending on how you added the subform, the subform control name may be different than the name of the form being used as a subform.

If that still doesn't work, make certain that the fields in question are not Lookup fields. Open the table in Design view, and select each of the fields one at a time. Look at the Lookup tab in the bottom left-hand corner to make sure it doesn't say combo box. If it does, you've got a Lookup field, which means that what's stored in the field is NOT what's being displayed!

Sorry for not being more clear! Yes, Duration is the value of a control on another form (Studies). I changed the code to Forms!Form_Studies.Duration as you suggested, but then got an error message saying it couldn't find the form. When I changed it back to Forms_Studies.Duration I no longer got the error message, but still no checkbox.

So it sounds like the problem could be that I am indeed using Lookup fields. Studies.Duration, Me.StudyType and Me.EffectSize are all Lookup fields and I need them to remain as such. Is there someway I can account for that in the code?

You'd need to know the values of the text for which you're searching. Your statement might therefore look something like

If Me.StudyType = 3 And Form_Studies.Duration = 1 Or Form_Studies.Duration = 2 And Me.EffectSize = 4 Then

ok I will work on figuring out the values. Thanks very much for your help!