Change Field Color in VB code - Tabular Form

[IceFreeze]

I have a form that has a column of data (txtLastName) - with many repeated values.
I would like to change the back-color of the field each time the data value in the field changes.

For example:
Anderson
Anderson
Anderson
Abby
Abby
Smith
Smith

I would like the first occurance of Abby and Smith to have a different background color.

In code - I am able to loop through the reords (using DoCmd.GoToRecord) and identify where the changes occur, but when I try to set the background color (Me.Form.txtLastName.BackColor = vbYellow) - it sets it for every cell.

I have also tried to create another text-box where I could update the value to 1 or 0 based on if the name changed for that row (I would then create a conditional color for the name cell - based on the value of 1 or 0 in the text box). But each time I set the value of one row's text-box - all of the rows would change. I belive this is becuase it is not a bound control.... but I don't want to bind it to anything becuase I just want to set the value in code.

Any help or direction is appreciated!!

Thanks,
Jennifer

 

[RoyVidar]

I'm sorry, but forget the code approach - see in a continuous form, what you see isn't multiple controls, but multiple instances of the same control. Change one, changes them all

What you need, is either a calculated control, or a field in the recordsource (query?) which can be used by the Conditional Formatting feature you can find on the Format menu.

 

[IceFreeze]

hmm - Thanks so much for the fast reply - so it looks like I will need to go with the conditional formating..

Can a calculated control investigate values of different rows? If so - could you provide any example for syntex?

The form is currently using a query from a table as the record source. I really don't want to add a field to the table just for the coloring purpose. Are there other options?

I feel like if conditional formating is possible, that there must be something telling the form how to format each individual occurance of the cell - I wish I could get my hand on it in code!

Thanks again for the help,
Jennifer

 

[Samrasr]

Help, [Please Help with a date change

In my database i have the following

Doc in date 06/05/07, Target days 10, Target Date ????

I have three labels as you can see in the attach photo.

Doc in Date is automatically displays todays date as i put a Date() code
Target Dates is set to default 10
Target Date should automatically display 16/05/2007 after adding Doc in Date + Target Date.


How can this be done? Please help

 

[RoyVidar]

Hm, I don't really like using calculated controls for conditional formatting, as there are some bugs with it (http://allenbrowne.com/bug-05.html), I think I'd tried the query. The following will be a bit demanding on recourses, but if it isn't to many records, it'll probably work. You'll need some field to sort on, here I'm assuming ID (field1 ... fieldN represents your current column list)

SELECT 
t.ID, t.field1, t.field2, ..., t.fieldN, 
    (SELECT count(id) 
     FROM yourTable s
     WHERE s.ID <= t.ID AND 
     s.LastName = t.LastName) As mySep
FROM yourTable t
ORDER BY t.ID

If this works, you could probably use the expression [mySep]=1 as arguement for the conditional formatting (selecting "Expression Is" in the first dropdown, I think). There might be more elegant, better, faster approaches, but this is the one I came up with

 

[IceFreeze]

Oh - I didn't even think of using sql to generate a value that I could use for the conditional statement. I will try - thanks so much!

 

[IceFreeze]

I just used the below sql to get a count of records with the same value, but a higher customer id number - and changed the color of the ones that returned zero (showing they were the first records of the new value)

SELECT MAIN.N_CUST_ID, MAIN.T_TEAM_NAME, (select count(SUB.T_TEAM_NAME)
from SSMCUST AS SUB
where MAIN.T_TEAM_NAME = SUB.T_TEAM_NAME
and SUB.N_CUST_ID > MAIN.N_CUST_ID
) AS I_TM_COUNT, MAIN.T_TEAM_LEVEL
FROM SSMCUST AS MAIN
ORDER BY MAIN.T_TEAM_NAME, MAIN.N_CUST_ID DESC;

works perfectly! Thanks again!

 

[Samrasr]

How do i post a new THREAD?

Im confused on how i can post my own thread

Please help

 

[RoyVidar]

When you view the forum in which you want to post, there is a large friendly button at the top left named "New thread", just below where it says "User CP".