Conditional Formatting

B

Ben

I'm still learning Access and coding. Here's my dilemma:
On my form I would like the "Last Name" field to flag if the same last name
has already been entered. I want to allow duplicate last names, but I'd like
to have the field turn red (for example) if the name has already been
entered. In Excel I'd use conditional formatting based around:
=COUNTIF(A:A,A2)>1
....Where column "A" was my "last name" column
I'm sure it's pretty easy, but I'm just lost.

The second part to that question is:
Will that conditional formatting carry over to the report based on that
form? If not, how do I do that (so that repeating last names in the report
are all red, for example)?

Thanks,
Ben
 
F

fredg

I'm still learning Access and coding. Here's my dilemma:
On my form I would like the "Last Name" field to flag if the same last name
has already been entered. I want to allow duplicate last names, but I'd like
to have the field turn red (for example) if the name has already been
entered. In Excel I'd use conditional formatting based around:
=COUNTIF(A:A,A2)>1
...Where column "A" was my "last name" column
I'm sure it's pretty easy, but I'm just lost.

The second part to that question is:
Will that conditional formatting carry over to the report based on that
form? If not, how do I do that (so that repeating last names in the report
are all red, for example)?

Thanks,
Ben
Click to expand...

If you are using Access 2000 or newer you can use the control's
Conditional Formatting property.
Select the control. Click on Format + Conditional formatting
Set the Condition1 dropdown to Expression Is
Write (all on one line):
DCount("*","YourTableOrQueryName","[LastName] = " & Chr(34) &
[LastName] & Chr(34)) > 1

in the alongside dialog box.
Select whatever color you want.
Click OK

This does not carry over the a report, but you can use the same
expression in the report (assuming you are not further filtering the
data).
 
B

Ben

Thank you very much! Easy as pie! I really appreciate you and everybody
else taking the time to help poor clueless me.

fredg said:
I'm still learning Access and coding. Here's my dilemma:
On my form I would like the "Last Name" field to flag if the same last name
has already been entered. I want to allow duplicate last names, but I'd like
to have the field turn red (for example) if the name has already been
entered. In Excel I'd use conditional formatting based around:
=COUNTIF(A:A,A2)>1
...Where column "A" was my "last name" column
I'm sure it's pretty easy, but I'm just lost.

The second part to that question is:
Will that conditional formatting carry over to the report based on that
form? If not, how do I do that (so that repeating last names in the report
are all red, for example)?

Thanks,
Ben
Click to expand...

If you are using Access 2000 or newer you can use the control's
Conditional Formatting property.
Select the control. Click on Format + Conditional formatting
Set the Condition1 dropdown to Expression Is
Write (all on one line):
DCount("*","YourTableOrQueryName","[LastName] = " & Chr(34) &
[LastName] & Chr(34)) > 1

in the alongside dialog box.
Select whatever color you want.
Click OK

This does not carry over the a report, but you can use the same
expression in the report (assuming you are not further filtering the
data).
--

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Click to expand...
 
Top