Highlight fields

[whiffy]

I have 2 tables (that admittedly go somewhat against the normalisation rules) called tbl_customer and tbl_customer_changed.
tbl_customer holds details of all our customers, tbl_customer_changed holds temporary information on address changes that gets deleted once it has beeen approved. This allows the great unwashed in the organisation to make changes without messing anything up and it works very well.

However I am trying to highlight on a form, by changing the textbox's background colour, where the record displayed (from tbl_customer_changed) differs from the record held in tbl_customer so that the person approving the changes can see them at a glance.

I am using this code:

matchorg = Forms![frm_customer_changed]![OrganisationID]
Set db = CurrentDb()
Set rec = db.OpenRecordset("SELECT * FROM tbl_customer WHERE ((tbl_customer.OrganisationID)= " & matchorg & "&quot

If Me!txtOrganisation <> rec(&quot;Organisation&quot Then Me!txtOrganisation.BackColor = 10088675

This works ok for most fields except it won't highlight any fields where the record in tbl_customer is Null and has been changed in tbl_customer_changed.

I have tried adding this line:

If (IsNull(Me!txtemail) = False And IsNull(rec(&quot;E-Mail Address&quot) = True) Then Me!txtemail.BackColor = 10088675

But it still doesn't work.

Can anyone suggest anything?

 

[KenReay]

Hi

Try

If Me!txtOrganisation <> NZ(rec(&quot;Organisation&quot,&quot;&quot Then Me!txtOrganisation.BackColor = 10088675

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[whiffy]

Hi,

Thanks for your suggestion, unfortunatley it's still not highlighting the field.

Strangely, If I change the action from:
Me!emailfield.BackColor = 10088675
to:
MsgBox (&quot;!!!!!&quot
The message box pops up as expected, whereas the backcolor one doesn't.

Now I'm really confused..........

 

[danvlas]

Just a thought...have you ever compiled the code?
Open any module, Debug->Compile and save all modules...

HTH

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[whiffy]

Hi,

Your suggestion is much appreciated but for whatever reason it's still not changing the background.
I must admit I hadn't done that for ages.

 

[KenReay]

Hi

Well if you have a clean compile and msgbox appeasr as youi say, then the IF..must be OK are you sure that the number you have chosen is not the existing back colour?, and where do you set it back to 'normal' do you need

If Me!txtOrganisation <> NZ(rec(&quot;Organisation&quot,&quot;&quot Then
Me!txtOrganisation.BackColor = 10088675
Else
Me!txtOrganisation.BackColor = numberforsomeothercolor
end if

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[whiffy]

Hi,

The default BackColor is -2147483643 but just to be on the safe side I added the 'Else' statement as you suggested, but still no joy.

I have also tried altering the fontweight property which didn't work but strangely the specialeffect property does.

The backcolour change works perfectly on any field where the value is not comparing against null in tbl_customers.
But the NZ() function should take care of that. woe is me...

 

[sha76]

This sounds a bit back-to-front I know, but I once had something similar & it worked if I had my default BackColor set to red(or whatever) and used = instead of <> in my code!

Sharon

 

[whiffy]

Wow, even with the default backcolor set to 10088675 via the properties window and no test applied to the field it still appears white!

Bizzare.......

 

[ChrisCalvert]

I suggest that you try this, just to see what shows up.
---------------------------------------------------
Dim intHighlightColor as integer
intHighlightColor = 10088675

If Me!txtOrganisation <> NZ(rec(&quot;Organisation&quot,&quot;&quot Then
msgbox Me!txtOrganisation.BackColor
Me!txtOrganisation.BackColor = intHighlightColor
msgbox Me!txtOrganisation.BackColor
---------------------------------------------------
Let's just see if the event is changeing the property, but somehow the color is not reflecting that.

-chris

 

[danvlas]

How many colors have you defined in your screen settings?
Just a thought...

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[whiffy]

Hi,

Danvlas-
There's more than enough colours, but I like your thinking!

ChrisCalvert-

Right, well the message box came up with -2147483643 then it came up with 10088675 indicating that the colour has indeed been changed, then the form opens and the colour hasn't changed.

However, I tried this several times and on one occasion the form appeared before the msgbox and the colour did indeed change until I clicked on OK on the 2nd msgbox.

I've got this in the onLoad event, perhaps it should be somewhere else?

 

[KenReay]

Hi

I would have thought the oncurrent event more appropriate Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK