Change background color upon data entry

[aggieweld]

How do I change the background color of a cell when data is entered into a database record.

We have recently converted a spreadsheet schedule into Access, with queries and forms filtering out information appropriate to the individual users. When this information was in Excel the individual users were used to highlighting cells when they entered information into the individual cells to visually show completion.

Is there a macro, or events procedure that would allow the individual field record to change in background color when data is entered for the first time or from null value to not null value. That would then duplicate the way we were using Excel.

Thanks....

 

[hneal98]

Use the AfterUpdate event of the control. You can put your color changing code there so when the user changes the field and hits tab to leave, it will update the color of the field

 

[DustDevil1980]

First and simplest is using Conditional Formatting. Another way is to set the back color to yellow and then set it to transparent or normal using the got/lost focus events. A third way is to put a box behind your controls, set the back color to yellow, then reference to be transparent or normal on the controls got/lost focus events.

 

[oharab]

Surely the OnEnter and OnExit events are the events that are needed here! On the OnEnter event put something like:
Me.ActiveControl.BackColor = vbRed
and on the OnExit
Me.ActiveControl.BackColor = vbWhite

That should sort it.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[BSman]

If you are using Access 2000 (or later), conditional formatting is the solution. Otherwise, forget it! I'm assuming you are using a multiple row type of form. If you try to change the color of a control in Access 97 on a form, it will change ALL of the occurrences of the control on the multiple row form, which is exactly not what you want.

Conditional formatting in Access 2000 allows you the change some of the formatting of the control (colors, etc.) based on the value of the data. You can set up to three difference conditions. The change is displayed for each row based on the data in the control on that row, so your form can display a different color in different rows for the same control.

 

[oharab]

Ah, you see this is where we differ! I was assuming a single page, data entry form!
aggieweld, put us out of our misery, what type of form are you using and which method works?

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[aggieweld]

Thanks for all the help.

I am using Access 97 and the form I am trying to use is a datasheet view or form with multple records.

I have been able to format using the afterupdate commands, but as has been noted above, the complete field is changed not just the the single record field.

I have not tried Dustdevil's suggestion yet, and will try to upgrade to Access 2000/2002 to get conditional formatting availability.

Thanks again to all, I was able to learn some new things,even if I was not able to get where I wanted to go originally.