Changing Data in a feild automatically

[bone head]

Hi

Probably a simple solution to this however it is doing my head in at the moment.

I have a field ( Approv) which is currently set using the unconditional formatting. However I notice that you can only have 3 conditions set.

I needed to add another condition which you could not do anyway with the conditional formatting as far as I can see, So I inserted the following code on exit of a field “ Credit amount”

Private Sub Credit_amount_Exit(Cancel As Integer)
If [Credit amount] < 1 Then [Credit Approv] = "No Credit"
End Sub

However this does not seem to change the Credit Approv field, I am getting no error messages.

The conditional formatting also allocates different colours for the 3 options already set, ideally I would like to make the “No Credit” option turn the background Yellow, guess it would be simple to add to the code above, if the code above worked.

Any ideas what I am doing wrong, and suggestions on the colour issue.

Many thanks

Bone Head

 

[Mile-O]

In the Control Source of [Credit Approv], try:

=IIf([Credit amount] < 1, "No Credit", "-")

 

[bone head]

Ok that works fine but I now lose my conditional formation options

At various other points of the data base ie on other forms this field is changed to show progress

So it goes from Red back ground with nothing in the field to
Orange when the credit has been authorised by the manager with the text Approved in the field
To Green when the Credit has been paid and the text Authorised in the field
To Blue background and text closed in the field when all actions have been performed.

It is these two I need to marry up somehow

 

[Mile-O]

You must be able to further customise the Conditional Formatting in VBA but, as I don't have Access 2000 or above I don't have the Conditional Formatting option to experiment with.

Sorry.

 

[bone head]

I dont think so I have had a look around. it seems you are limited to 3 options 4 if you count the no conditions met.

You also seem only to be able to set conditions on info in the feild in question, and you can not seem to make reference to another feild as i need to.

it seems to me that the code i had or similar should work, as conditional formating i thought left the formating as is unless the specified criteria had been met. this would just mean me having to change the background colour manually.

it sounds so simple

 

[bone head]

OK had a cup of coffee and a think, what i was trying to do would not have worked anyway. as i am changing the feild in several places so the likely hood was it would keep being changed.

Have now opted for a simpler option with a second visual colour feild just showing whether credit is required or not.

Any way thanks Mike-O-phile your code came in very usefull for this, i would not have thought of doing it this way, would probably just have done it through an event. so i learnt something along the way.


BTW what does IIF mean in the code ?::
Cheers

Paul

 

[Mile-O]

IIF() is like the IF function in Excel where you evaluate an expression and act on whether the response was try or false.


i.e.


IIF ( expression , result if true, result if false)

so

IIf(2>1, "Yes!", "No!") would return "Yes!"