Highlighting records in a form

[Danielf]

Changing color of records in a form

Hi,

Is is possible to change the color of some records in a form? I would like to change - for example- the color of every 10th record in my form.

Daniel

 

[Guus2005]

Yes, i believe that is possible in Acc2007

 

[Rabbie]

This link has instructions for something similar in A2003. I expect you can modify them to work on every 10th row if thats what you want

 

[Danielf]

This link has instructions for something similar in A2003. I expect you can modify them to work on every 10th row if thats what you want

Hi Rabbie;

Thanks for the link, I will give a look.

Daniel

 

[Guus2005]

This link has instructions for something similar in A2003. I expect you can modify them to work on every 10th row if thats what you want

Yes, that works in reports, not in forms however.

 

[Mike375]

If you don't get a "nice" way of doing it there is a crude way.

You make a macro that is SetValue of code that is =

If a macro you do it as a Runmacro action. You have the macro SetValue a field with a No (or whatever) every 9 records and a Yes on the 10th and repeat it. You could either have the macro have separate actions lines to match each record or the second maco to a runmaco on another macro with a repeat of 10. So each "pair" of action lines will be a SetValue followed by a GoTo Next Record action. The is macro is placed in another macro as a RunMacro action.

That will then allow conditional formatting to work.

You need a couple of conditions. One is to stop the runmacro action and the other is to stop the macro that has the 10 sets of SetValue and GoToNext Record. There are a few ways to do this. If don't have a "stop" at the end of the records then a Halt sign will come up with Can't Go To Record". I have not thought much about it with this exercise but it might be a little tricky since each of the "runs" has several GoToNext Record actions. Edit: I just realised stopping it will be easy.

Obviously you need a sort done first. That can be done at the start of the macro.

If you wish to alter the sort then you would need an update query to make all the records null in the field that gets the Yes or No so it could be redone.

The speed at which the macro will run depends on such things as if you have things being done with OnCurrent and compicated they are.

If the form is based on a table or simply query and nothing with OnCurrent then will it take about 60 seconds to do 5000 to 6000 records, faster with Echo No but I prefer to watch them run down the screen

 

[Mike375]

I just made that macro and it all worked. It took about 25 seconds to sort and run down 5400 records. However, when I added the conditional formatting it took about 30 seconds.

 

[ajetrumpet]

Hey Daniel,

I don't think this is easily done...but if you're using a single form you can get away with it, because one record appears on the screen at once. If you are using single form view, you can do the following to highlight the background of every 10th record:

On Current

if me.currentRecord mod (10) = 0 then
  me.detail.backcolor = [color=red]whatever color you want here[/color]
else
  me.detail.backcolor = rgb (255, 255, 255)
end if

Unfortunately, this won't work on continuous forms, because you are looking at more than one record on only one screen...

 

[Mike375]

Adam,

Looks you are stuck with this

 

[Danielf]

Adam,

Looks you are stuck with this

Thanks Mike for your Macro. I have imported it in my database but I don't understand how it works.

Daniel

 

[Mike375]

Thanks Mike for your Macro. I have imported it in my database but I don't understand how it works.

Daniel

Firstly, you need to change the field names to your fields.

Just look at the two macros in design view and the actions. Macro609 is the one attached to the green label Click Here. The first thing it does is GoToControl for the [NameNumber]. The next action is to sort that field in ascending order. The nest thing macro609 does is to run macro608 and run it until it gets to the end of the records. Whether there are 10 records or 10000it will run to the end and stop. It knows to stop when the field [namenumber] is null. As its last action it returns to the first record. In your case you would replace [namenumber] with a field that it is never null such as your ID field or LastName.

Macro608 has 10 SetValue action lines and a GoToNext Record between each SetValue action. Thus it inserts No in to the field [abc], moves to the next record and inserts a No in [abc] for that record and does it 9 times in all. The 10th and last action line inserts "Yes" in field [abc]. The process then repeats itself. Each action line will only run if the field [namenumber] Is Not Null. You would replace [abc] with a field that you set aside to receive the No or Yes in each record.

Conditional formatting works on any record with a Yes in field [abc]. You would need to replace [abc] with the field or fields that you wish to apply conditional formatting to. As you can see I have it applied conditional formatting to both [abc] and [namenumber]. If the the number of SetValue actions that insert No were changed to 5 or 55 then then the colour highlighting would occur more or less frequently than every 10 records. For example, if you altered the action lines so that there was only one action for a SetValue of No then you conditional formatting would highlight every second record.

In both code and macro the each action line is completed before moving to the next action line.

 

[ajetrumpet]

that's wonderful MIke, but I can tell you that new people to Access have no idea what the he** is going on with that. I only know because I know the program.

If I were you Daniel, I wouldn't even try to figure that stuff out.

 

[Mike375]

If you have it so that it just repeats NextRecord nine times and then inserts a "Yes" every 10th record then it will need an Update query to Null if it is run again but in a different sort order.

 

[Guus2005]

that's wonderful MIke, but I can tell you that new people to Access have no idea what the he** is going on with that. I only know because I know the program.

If I were you Daniel, I wouldn't even try to figure that stuff out.

I tried to figure it out and correct me if i'm wrong, the conditional formatting does the trick.
Your macro's just put Yes in every tenth record. The conditional formatting recognizes it and changes the color.

Firstly, i am not fond of working with macro's so why couldn't it be done using code?
Secondly, where can i find to set conditional formatting for a continuous form?

 

[Mike375]

It can be done in code. SetValue macro action is like = in code. I just prefer macros when a macro will do the job.

To do conditional formatting on the tabular form just right click on the field and you will see conditional formatting in the menu that opens. Then away you go.

If you open the DB I had attached and open the form in design view and right click on the either of the two fields and then select conditional formatting you will see how it was done.

You can make up to 3 sets of conditions. Actually that amounts to 4 conditions if you allow for the formatting that was set in the form. What happens is that if none of the conditions are met then the preset formatting applies.

Thus I could I have made the macro insert a 1 after going down 10 records then insert a 2 after going down another 10 records and a 3 after going down another 10 records. Then the conditional formatting could have been done based on an field value of 1 and 2 and 3. That way the field would be highlighted every 10 records but record 10 would be different to record 20 which would be different to record 30 and then of course it would repeat itself. The records between each group of 10 records would meet none of the conditions and thus display the format that was preset. Hence 4 sets of formatting conditions are available.

 

[Danielf]

Hi Mike,

Thanks a lot, it works .I have looked at the conditional formatting and have understood it

Have a nice day

Daniel

 

[Guus2005]

It can be done in code. SetValue macro action is like = in code. I just prefer macros when a macro will do the job.

To do conditional formatting on the tabular form just right click on the field and you will see conditional formatting in the menu that opens. Then away you go.

Never used it, but i think i will!
Thanks.

Let's get your red dot to green again!

 

[Mike375]

Hi Mike,

Thanks a lot, it works

Daniel

Of course it works

 

[Mike375]

Never used it, but i think i will!
Thanks.

Let's get your red dot to green again!

I think I am just born to be in the red zone