Continuous Form...

[vjb75]

{RESOLVED} and THANK YOU Continuous Form...

'************UPADTE 20/07/2004***********************

THANK YOU ALL FOR YOUR GUIDANCE, I'VE DECIDED TO OVERLAY A
A TRANSPARANT FIELD, FORMATTED IN RED WITH AN IIF STATEMENT
AS PER ADVICE. MUCH APPRECIATED. CYA

'***********************************************

hi all,

i have a continuous form that displays a data set, what i would like to do is based on the contents of a specific field change the back color of another field on the same form. The code sample works to change the field color to blue but as soon as the condition changes on the form the false part of the statement isnt met. (changing the field to vbred) Any help would be greatly appreciated. Code sample is below:

thanks

Private Sub Form_Current()

If Me.txtDelCompIndicator = "J" Then
Me.txtExpiryDate.BackColor = vbBlue
ElseIf Me.txtDelCompIndicator <> "J" And bcheckDate(Me.txtExpiryDate) = True Then
Me.txtExpiryDate.BackColor = vbRed
End If

End Sub

 

[WayneRyan]

vjb,

It's not exactly what you asked for, but on continuous forms you have to
use Conditional formatting.

Just select a field when in Design View, Format --> Conditional

Wayne

 

[ChrisO]

G’day Vjb75 and welcome to the site.

There are very few way to do this. We could write code all daylong and never get it to work correctly, for example…

Option Explicit
[color=green]'  Option Compare Text  '  What is inplace???[/color]


Private Sub Form_Current()

    If Me.txtDelCompIndicator = "J" Then
        Me.txtExpiryDate.BackColor = vbBlue
    ElseIf Me.txtDelCompIndicator <> "J" And bCheckDate(Me.txtExpiryDate) = True Then
        Me.txtExpiryDate.BackColor = vbRed
    End If

End Sub


Private Sub Form_Current1()

    If Me.txtDelCompIndicator = "J" Then
        [color=green]'   Me.txtDelCompIndicator is equal to "J"[/color]
        Me.txtExpiryDate.BackColor = vbBlue
    Else
        [color=green]'   Me.txtDelCompIndicator is not equal to "J"
        '   No point in rechecking that condition.
        '   Simply check if bCheckDate() is True.[/color]
        If bCheckDate(Me.txtExpiryDate) = True Then
            Me.txtExpiryDate.BackColor = vbRed
        Else
            [color=green]'   But what Color is Me.txtExpiryDate.BackColor
            '   if <> "J" and bCheckDate() is False?[/color]
        End If
    End If

End Sub


Private Sub Form_Current2()

    [color=green]'    No protection against Nulls and/or upper/lower case comparison...[/color]
    Me.txtExpiryDate.BackColor = IIf(Me.txtDelCompIndicator <> "J" And _
                                     bCheckDate(Me.txtExpiryDate) = True, _
                                     vbRed, vbBlue)

End Sub


Private Sub Form_Current3()

    [color=green]'    Add Null protection, pick any silly default date...[/color]
    Me.txtExpiryDate.BackColor = IIf(Nz(Me.txtDelCompIndicator, "") <> "J" And _
                                     bCheckDate(Nz(Me.txtExpiryDate, CDate("1/1/1900"))) = True, _
                                     vbRed, vbBlue)

End Sub


Private Sub Form_Current4()

    [color=green]'    Add upper/lower case protection...[/color]
    Me.txtExpiryDate.BackColor = IIf(UCase(Nz(Me.txtDelCompIndicator, "")) <> "J" And _
                                     bCheckDate(Nz(Me.txtExpiryDate, CDate("1/1/1900"))) = True, _
                                     vbRed, vbBlue)

End Sub

Sad thing is, none of that will work on a Continuous Form.
Nor will Conditional formatting in A97 or anything that needs to maintain backward compatibility with A97.

If you would like to post a small example, A97 please, I guess Wayne would do the conditional formatting and I will do it in A97.

Edit…
Oh, the spelling mistakes.

Regards,
Chris.

 

[Mike375]

What about a compromise and change the colour of the writing. That is easy to do.

You create a couple of fields in a query and overlay those fields on your form and give the fields on top transparent backgrounds.

Let's say you want a >=0 to be "green" and a <0 to be "red". Your query has the fields that show a blank or the value.

You set the forecolour in form design of the overlaying fields

You make your IIF fields such that when the value is >=0 then field1 shows the value and field2 is blank. But when the value is <0 then Field2 shows the value and Field1 is blank and because the background has been set as transparent then etc.

Mike

 

[WayneRyan]

MIke,

In A95 & A97, without conditional formatting, won't ALL of the records
appear to be formatted the same?

Wayne

 

[ChrisO]

Mike.

If you can do that in <= A97 then I will eat my hat.
(Not that I have a hat, I’ve already eaten it…{on more than one occasion. })

Regards,
Chris.

 

[WayneRyan]

Chris,

I thought YOU were coming up with a sample. I haven't delved into that
area in quite a while, but there are some "labor intensive" ways to simulate
it.

Off to research the archives,
Wayne

 

[Mike375]

I have A95, you blokes want to lose some money.

But do note, I said the "writing" or forecolour, not background.

Mike

 

[ChrisO]

Wayne…
Don’t bother, I know how to do it…

Mike…
Never bet against an Insurance salesman…
But you are taking the ‘high ground’ here.
Please don’t let that put you off, I’m all ears and no money.

How about a small demo in A97, that should solve it!

Regards,
Chris.

 

[WayneRyan]

Chris,

Mikey's right!

Not even that hard.

Wow! That's tiny.

Wayne

 

[Mike375]

Wayne…
Don’t bother, I know how to do it…

Mike…
Never bet against an Insurance salesman…
But you are taking the ‘high ground’ here.
Please don’t let that put you off, I’m all ears and no money.

How about a small demo in A97, that should solve it!

Regards,
Chris.

Chris,

Import the table and query into an A97 blank .mdb.

Go to the end of the query and see the Appt+ Appt- Contacts+ Contacts- Time+ Time-

I extracted this just for you

Mike

 

[gseach]

Conditional formatting for continuous forms.

http://www.lebans.com/conditionalformatting.htm

 

[Rich]

Chris,

Import the table and query into an A97 blank .mdb.

Go to the end of the query and see the Appt+ Appt- Contacts+ Contacts- Time+ Time-

I extracted this just for you

Mike

Where are the Macros?

 

[Mike375]

Where are the Macros?

They are not there but the results are there. The first few fields on the table is from a SetValue.

Attached are the little devils and you would not be able to do what they do with an Update Query.

Mike

 

[ChrisO]

Mike and Wayne.

Well I’m sitting here having my second cup of coffee and eating leftover hat from last night.

A very nice twist on an old problem…looks like we now have foreground and background ‘conditional formatting’ in < A2K.

Regards,
Chris.

 

[WayneRyan]

ChrisO,

Not in the least, I knew that there were ways to "fake it out", and Yes,
Mikey was RIGHT (see that Rich!), but it was easier than I thought. I won't
be using it, but it was an interesting problem ...

Mike's response intrigued me, and it was quite easy. Don't eat hat!

Maybe Mike just happened to see the 1 post on the NET that dealt
with this (Mike's used to being bashed), but your thoughts are always
welcome.

Rich, sorry, but this time, we have to give a big thanks to Mike!

Thanks Mike,
Wayne

 

[Mike375]

I learnt how do that a few years ago. The interesting things is that the person that showed me how to do it knew (and still knows) nothing about Access.

Initially I was trying to make the date turn red when it had been clicked on a tabular type calendar I use. On another tabular form I had some columns that were for + and - results. This person pointed out what was staring me in the face but I could not see it, that is, whenever a + field had an entry the - field was always blank and vica versa and he said "it is a pity you can't put one on top of the other".

This person is my business partner and over the years we have had a few instances where he has solved the problem.

A conclusion I came to quite a few years ago is that Access is somewhat like writing a letter. It is often not how many words you know but how you string them together.

Mike

 

[KenHigg]

Go Mikey!!!!

 

[ChrisO]

Well here is a small example of ‘conditional formatting’ in A97.
(Sorry Mike, I doubt if could be loaded with A95 and I have no way to back-convert it.)

The selected record, either with mouse or keyboard.
Five or six levels, depending on which check box is checked and how you want to count them.
(They change colour with left to right priority.)
And now, as per instruction, the foreground colour of the First Name control.
(Triggered by the ‘Written Termination’ check box.)

Note that the foreground formatting is done by directly referencing the Written Termination field in the underlying Table.
To put that another way; no mater how complex that condition may be derived, it should still work.

Nice to have another tool in the old toolbox.

Regards,
Chris.

 

[Rich]

I expect Mikey read one of my previous posts on overlaying text boxes, or even found one of the samples I've posted to achieve a muliticoloured continuous form using a single box