Solved

# Run code seperately for each record in continuous form

Posted on 2007-10-21
203 Views
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,
Robert
0
Question by:redrp

LVL 92

Accepted Solution

Hello redrp,

For continuous forms, you need to use Conditional Formatting.

Regards,

Patrick
0

Author Comment

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?
0

LVL 92

Expert Comment

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.
0

## Featured Post

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…