Multiple field result coding

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Access 2007

Three combo boxes with drop downs that have 2 choices
Yes
No

If all 3 are no I want to take the user to a different location on the form.
I am having trouble with an "AND" If function

This is my current code

Private Sub Q5c_AfterUpdate()
If Q5a.Value = "NO" And
If Q5b.Value = "NO" And
If Q5c.Value = "NO" And
Forms!frmStudy_page3.pgeIneligible.SetFocus
Reason.Value = "Can Not get Pregnant"
End If

Q5a and Q5b and Q5c are all fields on this form


What am I doing wrong in the If FUnction?

Thanks
dave
 
Also tried

Private Sub Q5c_AfterUpdate()
If Q5a.Value = "NO" Then
If Q5b.Value = "NO" Then
If Q5c.Value = "NO" Then
Forms!frmStudy_SPIR_COPD.pgeIneligible.SetFocus
Reason.Value = "Can Not get Pregnant"
End If
End Sub
 
you're not using the IIf() function, you're using the VBA If...Then...Else
statement. similar uses, but different syntax. try

If Me!Q5a = "NO" And Me!Q5b = "NO" And _
Me!Q5c = "NO" Then
Me!pgeIneligible.SetFocus
Me!Reason = "Can Not get Pregnant"
End If

based on your statement "If all 3 are no I want to take the user to a
different location on the form.", i'm assuming that control pgeIneligible is
on the same form as controls Q5a, Q5b and Q5c, as well as the Reason
control - and that all the controls are on the form that the code is running
in.

hth
 
just as a "by the way" addition to my post elsewhere in this thread, what
you were attempting is three nested If statements - which can be done, you
just have to get the syntax right, as

If Me!Q5a = "NO" Then
If Me!Q5b = "NO" Then
If Me!Q5c = "NO" Then
Me!pgeIneligible.SetFocus
Me!Reason = "Can Not get Pregnant"
End If
End If
End If

note that each If...Then construct must be "closed" with an End If, to
complete the statement, otherwise the code will not compile. for a simple
purpose such as the above, the single If statement i posted elsewhere in the
thread will work just as well, and is shorter and easier to write.

hth
 
Thanks for the help.

That worked

dave

tina said:
just as a "by the way" addition to my post elsewhere in this thread, what
you were attempting is three nested If statements - which can be done, you
just have to get the syntax right, as

If Me!Q5a = "NO" Then
If Me!Q5b = "NO" Then
If Me!Q5c = "NO" Then
Me!pgeIneligible.SetFocus
Me!Reason = "Can Not get Pregnant"
End If
End If
End If

note that each If...Then construct must be "closed" with an End If, to
complete the statement, otherwise the code will not compile. for a simple
purpose such as the above, the single If statement i posted elsewhere in
the
thread will work just as well, and is shorter and easier to write.

hth
Click to expand...
 
Back
Top