Results 1 to 5 of 5
  1. #1
    Xixor is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    20

    Limit to number of "And" statements in Access VBA? Help me make this code more efficient?

    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

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Never seen anything like it, so don't know if it's a character limit or some sort of AND limit. I know you could shorten this a wee bit by using a With block for Me, but that may not help and I couldn't find anything about an IF block character limit. Have to wonder if you get it working that in the end, it won't perform as expected.
    In the meantime, I'll look around for more info. Going forward, please use code tags for your code (see # on toolbar).
    EDIT
    I should have read your post more closely. No doubt OR is what you need, not AND - notwithstanding that there's probably a better approach, such as looping through the controls to check backcolor, but I can't recall doing that. It may be only possible to detect that property as designed rather than at run time.
    Nor do I understand how saving the record in either case satisfies the goal, which is what you seem to be doing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could use a loop of controls and their Tag property. Generally:
    Code:
      
    Dim ctl As Control  
    
    For Each ctl In Me.Controls       
      If ctl.Tag = "CheckMe" Then      
        'test for control contents or backcolor here    
      End If  
    Next ctl
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Xixor is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    20
    Thanks, Micron and pbaldy.

    pbaldy can you re-do your example code using a 2-3 of my fields so I can better see how it should be written? That would help me a lot as I am very basic at VBA and am not familiar yet with loops.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Xixor View Post
    pbaldy can you re-do your example code using a 2-3 of my fields so I can better see how it should be written? That would help me a lot as I am very basic at VBA and am not familiar yet with loops.
    The posted code wouldn't change, that's the point of it. You no longer specify each control, you put "CheckMe" in the Tag property of the controls you want to check. The code loops through all controls on the form, and checks any that have CheckMe in the tag property. If you add a new control, the code doesn't change, you just add CheckMe to the Tag property.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-06-2017, 07:10 AM
  2. Can't test code - "Debug" - "Run to Cursor"
    By GraeagleBill in forum Programming
    Replies: 4
    Last Post: 05-15-2016, 05:16 PM
  3. Replies: 3
    Last Post: 01-06-2016, 01:18 PM
  4. Replies: 2
    Last Post: 12-23-2015, 09:32 PM
  5. Replies: 4
    Last Post: 07-12-2014, 02:02 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums