Access 2007 Colouring records on form

[branston]

Hi,

I have tried to do this before but had to go a really complicated route round to get what I wanted... but I was hoping access 2007 had a nicer solution to my problem...!

Basically, I have a continuous form with 20 or so fiends in each record. (It looks like a register)
What i need to be able to do is highlight every field (in each record individually) which is different from a certain field in a different table (i.e. I make a copy of the source table weekly & I need to higlight the changes that have happened since that copy was taken)

I have the following very simple bit of code, which highlights fine, but it highlights EVERY record, even if only the 1st one is different:

Dim strCriteria As String

strCriteria = "[ProjectNo] = " & Me.Project & " and [Rev] = " & Me.Rev

If Me.Name= DLookup("[Name]", "TblLastVersion", strCriteria) Then
Else
Me.Name.BackColor = RGB(204, 238, 230)
End If


Any ideas if 2007 solved this problem?!

Thank you

 

[vbaInet]

You need to use Conditional Formatting. Look into that.

 

[branston]

Yeah, i tried but i think it won't work for 2 reasons:
1) I need it on more than 3 cells, and conditional formatting only works for 3 (unless thats changed)
2) I need a dlookup in the criteria for the conditional formatting, I had a go at that but it didnt seem to like it.

Can conditional formatting work on more than 3 in 2007? If so I will give it another go!

Thank you

 

[Chrism2]

Even 2010 is pretty limited as to what you can do with continuous form formatting. It's still based on Conditional Formatting, with very few actual options and I've noticed the processing time can be affected quite badly on some of my longer lists. (Even for simple expressions such as converting a text field to Uppercase).

 

[vbaInet]

Unfortunately it's limited. There's a sample database on one of the forum's Super Moderators (DCrake) website that could give you some ideas.

http://www.xcraftlimited.co.uk/Sample-Databases.html

It's called CondFormat.

 

[branston]

Thanks for your replies.
Chrism2 - its such a shame, hey - the 2007 excell has massivley improved the conditional formatting... seems access was forgotten about though!
vbaInet - thanks for that link - it looks like it could be of some use but I cant find the code they used to do it!! If you open the vba the only bit there is a filter! Ant idea where I can get the vba for it? (I tried to click the 'more info' link but it didnt like it!

Thanks again!

 

[vbaInet]

I've not had a look at his db but he's probably using a label or a textbox lodged behind the actual textbox and setting that label's or textbox's backcolor with the main textbox's backstyle property set to Transparent.

Is there code in the Modules section?

 

[branston]

right, ive found a way around this... slightly better than how i did it last time, but still not great.
For anyone who's interested:
- Table A holding "live" data, Table B holding the "stored" data i.e. from last week
- Create a Query with every field from table A and a corresponding field comparing Table A and Table B (e.g. Comments_: IIf([TblA].[Comments] Like [TblB].[Comments],"","XX") so if the fields are different an "XX" is shown
- Make a form with this new query as the source data
- you can then show every field and its correspionding "XX" field.
If you make the XX field conditional format to be a colour (background and font) if it = XX then the non-XX field to be transparent then you can put one behind the other and ta da - it solves the problem (if you can understand what i'm on about!)

I am sure there are MUCH better ways to do this, but when you spend so long on a problem, any solution seems good enough!

 

[branston]

ah, sorry vbaInet - seems we came to the same conclusion at the same time!!

Thank you!

 

[vbaInet]

Looks like it

Here's a thought, why not use a function to determine which of the conditions should fire. Then call the function in the conditional format. One of the parameters of the function will determine which condition it should use.

 

[branston]

If i ever have to use it again I will have a closer look at that idea - yeah!

Thanks for your help!

 

[vbaInet]

You're welcome!