Hi there, navigating tru records to spot which records are
flagged
as either "Yes" or "No"

PROBLEM
not sure why it's not working
PaidFlag is a field of type Yes/No within QTime
which is a select query.

appreciate any assistance...
--------------------------------------------------------------

Private Sub Form_Open(Cancel As Integer)

Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("QTime")

rst.MoveFirst

While Not rst.EOF

    If PaidFlag.Value = "Yes" Then
        TotalPay.ForeColor = 0
    Else
        TotalPay.ForeColor = 255
    End If
        rst.MoveNext
Wend

DoCmd.GoToRecord , , acLast
Command33.SetFocus
rst.Close
Set dbs = Nothing

End Sub

--
<remove the"x" to reply>

John

Wellington, NZ

Since you have set the value of PaidFlag to Yes/No this is a Boolean
datatype and you need to handle it as this data type.
Try to replace PaidFlag.Value = "Yes" with PaidFlag.Value = True

--
regards
Hans-Chr. Francke

You don't actually say what isn't working, but I'm guessing that you are
trying to set the forecolor property of a control on a continuous form, so
that it shows as a different colour depending on the state of PaidFlag.

If this is what you are trying to do then, it can't be done.

The properties of a control are the same for all records, you can't have a
control with different properties on different records, unless you change
the property as you move from record to record.

--
Mark Fisher
Replace nospam with mark-fisher from address if replying by Email

Yip that what exactly what I was trying to do...pity

 >--

John,
This is a workaround to the continuos form problem that has worked well for
me, it takes a bit of setup, but it works well once you get it going.

Assume your continuous form is based on a table that includes the fields
[PaidFlag] (yes/no field) and [Amount] (the value you want to change the
forecolor of)

On your form/subform place the following controls:

ctlPaid
ControlSource = [PaidFlag] (which should be true/false)

create the next three controls in the following order, it will make things
easier:
for each control, set the background color to transparent

ctlAmountRed
ControlSource: =IIf([ctlPaid]=False,[ctlAmountEdit],Null)

Enabled: Yes
Locked: Yes
*** Now do Format -> Send to Back

ctAmountBlack
ControlSource: =IIf([ctlPaid]=True,[ctlAmountEdit],Null)

Enabled: No
Locked: Yes
*** Now do Format -> Send to Back

ctlAmountEdit
ControlSource: [Amount]
Enabled: Yes
Locked: No
*** Now do Format -> Send to Back

Line up the three controls so that they are right over each other.

Next add the following code to ctlAmountRed_GotFocus()

Dim lngBlack As Long, lngRed As Long

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)

If Me!ctlPaid = True Then
    Me!ctlAmountEdit.ForeColor = lngRed
Else
    Me!ctlAmountEdit.ForeColor = lngBlack
End If
Me!ctlAmountEdit.SetFocus

Thats it, your [Account] should now be red if Paid is true, and black if
Paid is false.

How it works....
ctlAmountRed and ctlAmountBlack manipulate the format property, if true,
ctlAmountRed = [Amount], else it is null and displays nothing.
ctlAmountBlack is just the opposite. Since the format property can change
the
format for each record in a continuous form, we manipulate it to change the
forecolor. By setting the controls to transparent, we overlap their values.
So if ctlPaid is true then ctlAmountRed is true, which means it equals the
value in [Amount] this overlaps ctlAmountBlack which is null, and
ctlAmountEdit which is black, but hidden behind ctlAmountRed. When you click
on ctlAmountRed, the code for gotfocus executes. This code checks the value
of ctlPaid and sets the ctlAmountEdit forecolor to red or black. This
actually
changes for all controls on the form, but since this control only shows
while it is being editted (otherwise its in back of the other controls), it
doesn't matter.

Give it a try and let me know what you think of this workaround

James H. Brooks
Brooks Consulting

here's a way to get that done:

make a separate table
2 fields
    1st one   name:  Priority
              type: (in this case YES/NO)

    2nd one   name:  Spot
              type:  OLE Object

create 2 records in this table one for Priority YES and one for Priority NO
insert a .bmp object of the color you want for each of these values in the
spot field
(create these in MS Paint)

create a query with your original table with a relationship between your
YES/NO field and the priority field.

base your form on the new query instead of your table.

insert a control on your form with the SPOT field as its control source.

you can adapt this so you can use more than 2 colors by having the priority
field a number instead of YES/NO and having a number field in the original
table
instead of YES/NO.

good luck

John,
there is a simple workaround that you can use by formating the fields on the
form.
Set the format property of the control to:
"0[Black];0[Blue];"Zero"[Red];"Null"[Green]"
the first part is possitive values
next is negative values
third is zero values
and fourth is Null values.
This format changes the forecolor property of controls depending on their
value and it works with subforms.

--
regards
Hans-Chr. Francke

Hans-Chr.,

I think you missed the objective -

Formatting ctrl forecolor of [Amount] depending on value of a
True/False flag [PaidFlag] ctl

Your method is certainly good for accountants.

I'm a consultant trying to ensure what I'm owed in fees is paid
to me.
When I get paid I run an update query to change a date range
from [PaidFlag] = No to Yes for those dates who are No and in
the range.

Thanks anyway

I have had some interesting and varies repoenses to this
question, the one I have tries so far is the posting from "James
H Brooks".

regards,

John

Mark,
I wrote the code in question for John.
I'll grant yes to false is a good idea. As for the the statement "Assuming
TotalPay is a control on the form, wont the colour of totalpay be dependant
upon the last record in rst, irrespective of the rest of the records? " You
are correct this color will be the same for all records. But the reason this
is ok, is that setting this color is for editting the field only. The way
the rest of the code is setup, TotalPay only displays for the current record
when it is being editted. So its color only matters at that time, and the
code is there to set it to the color that is displayed according to the rest
of the code. When total pay is not being editted or does not have focus, the
control is not visible. Give the whole procedure a try and let me know what
you think.

James H. Brooks
Brooks Consulting

Maybe I missunderstood your question, but you can still use the formatting
even if the fieldvalues are Yes/No or True/False. These values are equal
to -1/0 and you could therefore put the following formatting to achieve the
desired output:
format = "Not used since possitive values";"Paid"[Blue];"Not paid"[Red];"Not
used since Null values"

After you have updated your table those records that are paid would then
show "Paid" in blue and those that are not paid would show "Not paid" in red
even if underlaying values are True/False.

--
regards
Hans-Chr. Francke

Hans,
What you say does work, but the problem was that he was looking at
formatting one control based on the value of another control (in this case
the paid control). The format property in and of itself does not allow this,
hence the workaround I developed.

James H. Brooks
Brooks Consulting