[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

Run code seperately for each record in continuous form

I have a continuous form whose recordset is a table.  The first field in the table is a date and the rest are 30 minute interval (ex: 0600 - 0630).  Each record in the table will have a specific date and then a number that corresponds to an amount of people we need working for the 30 minute interval, the intervals go from 0600 to 2330.  This data is entered manually as we find that we need more people, we will open the table and add a record with the date people are needed and how many are needed per interval.  When the form comes up it displays each date and then the number of extra people needed for each interval.  On the form I have a box for each interval with a default back color of green, I want to format this box to yellow if the amount of people needed is 1-5 and red if >5 people are needed.  I have put code in the OnLoad event for the form as follows:

If Me![0600 - 0630].Value > 0 Then
    Me!Box243.BackColor = 65535
        If Me![0600 - 0630] > 5 Then
            Me!Box243.BackColor = 255
        End If
End If

If Me![0630 - 0700].Value > 0 Then
    Me!Box244.BackColor = 65535
        If Me![0630 - 0700] > 5 Then
            Me!Box244.BackColor = 255
        End If
End If

The problem is that this code only runs once and colors all the boxes based on the first record in the table, so it colors the boxes on each record of the continuous form the same color.  So for example if the 0600 - 0630 interval of the first record in the table says 5 people are needed and the 0600 - 0630 interval of the second record says 15 people are needed the box will be colored yellow on each record when the form is opened causing the second record to be colored yellow when it should be red.  Any ideas or help is greatly appreciated.

Thank you,
  • 2
1 Solution
Patrick MatthewsCommented:
Hello redrp,

For continuous forms, you need to use Conditional Formatting.


redrpAuthor Commented:
Thanks!  That works perfectly for what I need to do.  Is there a way to do this if I need to format for more than 3 conditions?
Patrick MatthewsCommented:
redrp said:
>>Is there a way to do this if I need to format for more than 3 conditions?

AFAIK, no.  When you set properties for controls on continuous forms, they get applied to all
instances of the controls--that's just the designed behavior of continuous forms.

Access 2007 may offer expanded capabilities (such as more conditions)--I do not have Office
2007, but I do know that in Excel 2007 Microsoft vastly expanded those capabilities.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now