Hi all.
I have a form with some conditional formatting in VBA that turns the BackColor of certain fields to vbYellow depending on if/when the field is required to be filled in by the user. This all works great at this point.
I am trying to design some code that will check to see if there is any vbYellow on any of the fields in the form so I can see if a record is incomplete. I have an IF statement that basically goes through each field one by one using an "AND" operator to see if the field's BackColor is vbYellow or not. If even one field is vbYellow, it needs to mark the record as incomplete.
The code seems to work, but only to a certain point. After I start adding more than about 7-9 "And" operators, the code seems to get sketchy and sometimes work and sometimes not work.
Here is is:
If Me.student_last_name.BackColor <> vbYellow And _
Me.nih_mechanism.BackColor <> vbYellow And _
Me.prime_sponsor.BackColor <> vbYellow And _
Me.subaccount_department.BackColor <> vbYellow And _
Me.subcontract_entity.BackColor <> vbYellow And _
Me.date_submitted.BackColor <> vbYellow And _
Me.peris_infoed.BackColor <> vbYellow And _
Me.anticipated_project_start.BackColor <> vbYellow And _
Me.anticipated_project_end.BackColor <> vbYellow And_
Me.total_directs_requested.BackColor <> vbYellow And _
Me.total_indirects_requested.BackColor <> vbYellow And _
Me.total_son_costshare.BackColor <> vbYellow And _
Me.pi_effortamount.BackColor <> vbYellow And _
Me.son_coi_1.BackColor <> vbYellow And _
Me.son_coi_2.BackColor <> vbYellow And _
Me.son_coi_3.BackColor <> vbYellow And _
Me.son_coi_4.BackColor <> vbYellow And _
Me.son_coi_5.BackColor <> vbYellow And _
Me.son_coi_1_effort.BackColor <> vbYellow And _
Me.son_coi_2_effort.BackColor <> vbYellow And _
Me.son_coi_3_effort.BackColor <> vbYellow And _
Me.son_coi_4_effort.BackColor <> vbYellow And _
Me.son_coi_5_effort.BackColor <> vbYellow Then
Me.record_complete = "o"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
Else: Me.record_complete = "x"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
End If