Change color of duplicate fields in a form

[jw rutgers]

I have an Access form where, in the current record, I want a field which has
a duplicate in other records to color red.
I've tried conditional format but that doesn't seem to work. I found out
that I can let a msgbox appear when such duplicates occur.
But when I try to color the field, instead of coloring only the field in the
current record, Access colors the field in all the records.

Is there a way in which I can let only the field in the current record take
the color red?

I use the following code:

Private Sub Form_Current()

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

If ((DCount("[comp]", "deal1", "[comp] ='" & Me!comp & "'"))) >=2 then

Me![comp].BorderColor = lngRed
Me![comp].ForeColor = lngRed
Me![comp].BackColor = lngYellow
Me![comp].SetFocus

End If
End Sub

Thanks!,
JW

 

[Douglas J. Steele]

That's the way continous forms work. While it may look as though you've got,
say, 20 occurances of a particular control, you've really got the same
control repeated 20 times, so that changes you make to one affects all
occurances.

Look into Conditional Formatting.

 

[jw rutgers]

I use a single form view, and want to change the color of a field if the
contents of that field already exists in another record.
Is there a conditional format expression that I can use to get that result?

Thanks,
JW

That's the way continous forms work. While it may look as though you've
got, say, 20 occurances of a particular control, you've really got the
same control repeated 20 times, so that changes you make to one affects
all occurances.

Look into Conditional Formatting.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have an Access form where, in the current record, I want a field which
has a duplicate in other records to color red.
I've tried conditional format but that doesn't seem to work. I found out
that I can let a msgbox appear when such duplicates occur.
But when I try to color the field, instead of coloring only the field in
the current record, Access colors the field in all the records.

Is there a way in which I can let only the field in the current record
take the color red?

I use the following code:

Private Sub Form_Current()

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

If ((DCount("[comp]", "deal1", "[comp] ='" & Me!comp & "'"))) >=2 then

Me![comp].BorderColor = lngRed
Me![comp].ForeColor = lngRed
Me![comp].BackColor = lngYellow
Me![comp].SetFocus

End If
End Sub

Thanks!,
JW

 

[tina]

you should be able to do it by using an Expression for the condition, for
example

DCount(1,"TableName","Fieldname = " & [Fieldname]) > 0

replace TableName with the name of the table that's bound to the form.
replace both instances of Fieldname with the name of the field that holds
the value you're concerned with. if the field is a Text data type, change
the syntax to

DCount(1,"TableName","Fieldname = '" & [Fieldname] & "'") > 0

for clarity, thats ' " & [Fieldname] & " ' "

if the field is a Date/Time data type, change the syntax to

DCount(1,"TableName","Fieldname = #" & [Fieldname] & "#") > 0

hth

I use a single form view, and want to change the color of a field if the
contents of that field already exists in another record.
Is there a conditional format expression that I can use to get that result?

Thanks,
JW

That's the way continous forms work. While it may look as though you've
got, say, 20 occurances of a particular control, you've really got the
same control repeated 20 times, so that changes you make to one affects
all occurances.

Look into Conditional Formatting.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have an Access form where, in the current record, I want a field which
has a duplicate in other records to color red.
I've tried conditional format but that doesn't seem to work. I found out
that I can let a msgbox appear when such duplicates occur.
But when I try to color the field, instead of coloring only the field in
the current record, Access colors the field in all the records.

Is there a way in which I can let only the field in the current record
take the color red?

I use the following code:

Private Sub Form_Current()

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

If ((DCount("[comp]", "deal1", "[comp] ='" & Me!comp & "'"))) >=2 then

Me![comp].BorderColor = lngRed
Me![comp].ForeColor = lngRed
Me![comp].BackColor = lngYellow
Me![comp].SetFocus

End If
End Sub

Thanks!,
JW

 

[jw rutgers]

Thanks, I managed to get your expression working in a Form_Current() event
sub.
JW

you should be able to do it by using an Expression for the condition, for
example

DCount(1,"TableName","Fieldname = " & [Fieldname]) > 0

replace TableName with the name of the table that's bound to the form.
replace both instances of Fieldname with the name of the field that holds
the value you're concerned with. if the field is a Text data type, change
the syntax to

DCount(1,"TableName","Fieldname = '" & [Fieldname] & "'") > 0

for clarity, thats ' " & [Fieldname] & " ' "

if the field is a Date/Time data type, change the syntax to

DCount(1,"TableName","Fieldname = #" & [Fieldname] & "#") > 0

hth

I use a single form view, and want to change the color of a field if the
contents of that field already exists in another record.
Is there a conditional format expression that I can use to get that result?

Thanks,
JW

That's the way continous forms work. While it may look as though you've
got, say, 20 occurances of a particular control, you've really got the
same control repeated 20 times, so that changes you make to one affects
all occurances.

Look into Conditional Formatting.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have an Access form where, in the current record, I want a field
which
has a duplicate in other records to color red.
I've tried conditional format but that doesn't seem to work. I found out
that I can let a msgbox appear when such duplicates occur.
But when I try to color the field, instead of coloring only the field in
the current record, Access colors the field in all the records.

Is there a way in which I can let only the field in the current record
take the color red?

I use the following code:

Private Sub Form_Current()

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

If ((DCount("[comp]", "deal1", "[comp] ='" & Me!comp & "'"))) >=2 then

Me![comp].BorderColor = lngRed
Me![comp].ForeColor = lngRed
Me![comp].BackColor = lngYellow
Me![comp].SetFocus

End If
End Sub

Thanks!,
JW

 

[tina]

yes, that's as good a place to put it as any, and you're welcome

Thanks, I managed to get your expression working in a Form_Current() event
sub.
JW

you should be able to do it by using an Expression for the condition, for
example

DCount(1,"TableName","Fieldname = " & [Fieldname]) > 0

replace TableName with the name of the table that's bound to the form.
replace both instances of Fieldname with the name of the field that holds
the value you're concerned with. if the field is a Text data type, change
the syntax to

DCount(1,"TableName","Fieldname = '" & [Fieldname] & "'") > 0

for clarity, thats ' " & [Fieldname] & " ' "

if the field is a Date/Time data type, change the syntax to

DCount(1,"TableName","Fieldname = #" & [Fieldname] & "#") > 0

hth

I use a single form view, and want to change the color of a field if the
contents of that field already exists in another record.
Is there a conditional format expression that I can use to get that result?

Thanks,
JW

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> schreef in bericht
That's the way continous forms work. While it may look as though you've
got, say, 20 occurances of a particular control, you've really got the
same control repeated 20 times, so that changes you make to one affects
all occurances.

Look into Conditional Formatting.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have an Access form where, in the current record, I want a field
which
has a duplicate in other records to color red.
I've tried conditional format but that doesn't seem to work. I found out
that I can let a msgbox appear when such duplicates occur.
But when I try to color the field, instead of coloring only the

field
in

the current record, Access colors the field in all the records.

Is there a way in which I can let only the field in the current record
take the color red?

I use the following code:

Private Sub Form_Current()

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

If ((DCount("[comp]", "deal1", "[comp] ='" & Me!comp & "'"))) >=2 then

Me![comp].BorderColor = lngRed
Me![comp].ForeColor = lngRed
Me![comp].BackColor = lngYellow
Me![comp].SetFocus

End If
End Sub

Thanks!,
JW