Color-coding in combo box or table

G

google3luo359

Hello,

Access 2000

Is it possible to color-code duplicate names in a combo dropdown list?
Or even the values in their table?

The idea would be that the user would be alerted to the dup names (with
same colour, perhaps red or blue) in the list and then act accordingly.

TIA Ric
 
M

Marshall Barton

Access 2000

Is it possible to color-code duplicate names in a combo dropdown list?
Or even the values in their table?

The idea would be that the user would be alerted to the dup names (with
same colour, perhaps red or blue) in the list and then act accordingly.


Not in a combo or list box. Maybe in a continuous form, but
wouldn't it be much better to prevent the duplicate names
before they ever get into the table?
 
G

google3luo359

Marshall said:
Not in a combo or list box. Maybe in a continuous form, but
wouldn't it be much better to prevent the duplicate names
before they ever get into the table?

Thanks for helping out Marsh.
I can't prevent two or more people from having the same name :)
It happens very infrequently in our case. The secretaries might not
always notice and not ask for an additional middle name or such.

The records are safe-guarded however because each student has their own
unique identifying number.

I was just thinking of making a secondary lookup available for admin,
one based on name rather than student number, as name is much easier to
work with.

Having a color code is not really that important. I suppose I could
include the student nos. to the right of the names, in a combo box.
That way they could note the numbers should dups surface in their
search (and they notice them).

Ric
 
M

Marshall Barton

I can't prevent two or more people from having the same name :)
It happens very infrequently in our case. The secretaries might not
always notice and not ask for an additional middle name or such.

The records are safe-guarded however because each student has their own
unique identifying number.

I was just thinking of making a secondary lookup available for admin,
one based on name rather than student number, as name is much easier to
work with.

Having a color code is not really that important. I suppose I could
include the student nos. to the right of the names, in a combo box.
That way they could note the numbers should dups surface in their
search (and they notice them).


Ahh, that's different from what I thought you might be
doing. This one is a tougher task and I can see where a
color highlight might be useful.

It think displaying the ID number is a good idea, but
there's no guarantee your users will pay attention to it
without some kind of additional nudge.

First things first, you can identify the duplicate names by
changing the combo box's RowSource query to be more like:

SELECT S.sname, S.snumber,
(SELECT Count(*)
FROM table As X
WHERE X.sname = S.sname) As DupCnt
FROM table As S

Now you can test a selection in the combo box to see if it
is a duplicate in its BeforeUpdate event:

If Me.combo.Column(2) > 1 Then
If MsgBox("Be sure to select by ID number", _
vbOKCancel, "Duplicate Name") = vbCancel Then
Cancel = True
End If
End If

You could even go so far as to skip the check if they click
in the ID column (instead of the name column) by using the
MouseUp event to capture the X coordinate of the mouse.

It may be more work than you want to get into, but it's
possible to simulate a combo box with a continuous subform.
A continuous form can use Conditional Formatting to color
code entries based on the values of the DupCnt field.
 
G

google3luo359

Thanks very much Marsh. You've given me a bunch of great ideas and
approaches for this.
I will ponder it for a bit and then make my choice.

Ric
 
J

Jeff Cox

Thanks very much Marsh. You've given me a bunch of great ideas and
approaches for this.
I will ponder it for a bit and then make my choice.

Ric

I have code on my form that when the secretary inputs a new student
record, if the name is the same but ID number is different, a message
appears informing the secretary to ask for any other middle name or
nickname.

No need for colourcoding as the secretary is aware of the issue as soon
as they save the record.

Hope this idea helps,

Jeff Cox
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top