VBA change the color of a field in a table

[jpl458]

I have a routine hunting for duplicate phone number is a table. I just want to color the duplicate fields., but can' figure out how to change the color.
Here si the code. BTW the table is sorted in ascending sequence on the phone number.

Do Until rs.EOF
    Lastrec = rs("callno")           'Save record for compare
    rs.MoveNext                      'Next record
    Currec = rs("callno")            'Save current record
    If Currec = Lastrec Then         'See if both are the same
        rs.MovePrevious                  'Read previous record
        rs("Callno").BackColor = vbRed   'Set color
        rs.MoveNext
        rs.MoveNext
    End If
    counterbox = num
    num = num + 1
    rs.MoveNext

rs("Callno").BackColor = vbRed is. (There is much more that has to be added so I can't use the Find Duplicates wizard.)

How do you set the backcolor of the field in a table?

 

[bastanu]

You don't, you do it in a form instead using conditional formatting; for duplicates you could use dCount("*","tblYourTable","[callno]='" & [callno] & "'")>1 in the Conditional Formatting expression. Then set the desired back\fore colors as desired.

Cheers,

 

[jpl458]

You don't, you do it in a form instead using conditional formatting; for duplicates you could use dCount("*","tblYourTable","[callno]='" & [callno] & "'")>1 in the Conditional Formatting expression. Then set the desired back\fore colors as desired.

Cheers,

I was coming to the conclusion that changing the colors in a form is not available.

Take your spreadsheet hat off Tables store data. Queries retrieve or update data. Reports display data. Forms display data but also allow updating.

I prefer to call it a fedora, with feather in the left side.

 

[ebs17]

hunting for duplicate phone number

Do you think color would be the appropriate prison for hunting success?
A colorful pinball surface is not necessary. Instead of looking into a big pile of color blobs, one would conveniently filter directly to information of interest.

Your code looks like a wild gimmick. In a serious activity you use a query to find duplicates, there is also a wizard to create this simple query.
With the query you would also immediately have the possibility to do something with these duplicates, e.g. initiate an elimination.

 

[bastanu]

I was coming to the conclusion that changing the colors in a form is not available.

How would you come to this conclusion when forms and reports are the main Access user interface objects?
Cheers,

 

[jpl458]

Do you think color would be the appropriate prison for hunting success?
A colorful pinball surface is not necessary. Instead of looking into a big pile of color blobs, one would conveniently filter directly to information of interest.

Your code looks like a wild gimmick. In a serious activity you use a query to find duplicates, there is also a wizard to create this simple query.
With the query you would also immediately have the possibility to do something with these duplicates, e.g. initiate an elimination.

As I stated, there is much more to it than finding duplicates. I know how to find duplicates. I know about the Wizard. If it solved the problem completely I would use it. But it doesn't.

 

[jpl458]

How would you come to this conclusion when forms and reports are the main Access user interface objects?
Cheers,

I misspoke. I've found no way to change colors in a table, is what I meant. Sorry for that.

 

[bastanu]

And don't you think if there is much more to it you should share that when asking for help? You cannot do it in a table\recordset but might\should be able to do it in a datasheet form as long as we get the "more to it"....
Cheers,

 

[bastanu]

Here are some links that might help:

MSAccess - Conditional Formatting in Adjacent Rows

I am trying the add conditional formatting to an MSAccess datasheet. I want to highlight the following condition: Number Description ====== ============== 1111 Same Desc 1111 Same Desc 22...

stackoverflow.com

Conditional Format Based on Previous and Current Record

I'm having trouble with a tricky conditional format in a continuous subform. The criteria is based on field [GroupNo] when it changes (increases) which are always listed in numerical order. Basically all I want to is for the text box colour to alterante (eg blue & yellow)when GroupNo changes...

www.access-programmers.co.uk

Conditional Formatting: Comparing fields on different records

Hi All, I need to perform Excel-like conditional formatting. If a field value changes from the previous record, I need the field to display in a color. If the value is unchanged, no formatting is applied. Conditional formatting (Access 2007 and earlier) does not provide this feature. I...

www.tek-tips.com

Please post back if you get stuck !
Cheers,

 

[The_Doc_Man]

The way you stated the problem reveals a certain connection that should be disconnected. You store raw, unconditional data in tables. Color is a matter of presentation, which is NOT the purpose of a table. To say "change color in a table" is to announce that you are still thinking in spreadsheet terms. In a spreadsheet, though there ARE such things as block, row, and column formatting, all cells are independent of each other UNLESS the cell contains a formula that establishes a dependence. In a spreadsheet, you CAN pick one cell to have a different color, manually or by cell-based formula. No biggie. But that is not the case in Access tables.

The others have told you, and I will amplify, that tables should remain pure of purpose. They are for data storage and sometimes to feed computation. Queries, forms, and reports can do various presentation-related things for you. Queries can affect presentation via conditional formatting, and that MIGHT repeat MIGHT be all you need. But if not, forms and reports certain can do tests in event code to initiate proper presentation via color foreground, border, and background changes.

 

[jpl458]

Pat said the same thing. Understand.

 

[jpl458]

Thanks for setting me straight on the purpose of tables, forms and reports. And excuse me for asking a question when i hadn't thought though it first. After some thought and playing with conditional formatting, here is the problem; if i have a sequence of numbers that looks something like:

1
2
3
4
4
5
5
7
8

the duplicates of 4 and 5 will have the same color and are adjacent vertically. Correct me if I am wrong, ( which may very well be the case) but I think the only way to apply different colors to adjacent groups vertically, in the output, would be VBA. I can't find a way of doing it in conditional formatting. There is a place for a custom rule in conditional formatting, and is it possible to create a rule that handles my problem? If so
how to do that is way above my paygrade.

Thanks to all.

 

[bastanu]

Here is a sample showing how to use DCount to do conditional formatting.

Cheers,

 

[ebs17]

To his expertise you should add that conditional formatting is a performance brake. You don't have to measure it, you can already see it.
If the condition then is a DCount, which is executed per dataset, this already fulfills the fact "bad".

But you can crown this by trying to determine the colored fields / records by code to do something more than just view.
Nothing against the technical implementation, but before you should think about the sense and consequences of what you do.

 

[bastanu]

To his expertise you should add that conditional formatting is a performance brake. You don't have to measure it, you can already see it.
If the condition then is a DCount, which is executed per dataset, this already fulfills the fact "bad".

Eberhard, care to elaborate my bad expertise? I gave the OP a solution to the original request instead of your hyperbolic and diminishing reply in post # 5.

Maybe highlighting the existing duplicates sometimes is not such a bad idea such as when importing some existing data that needs to be cleaned-up; I know I had many requests like this over the years...But I guess in your perfect world nothing can or will go wrong.

Cheers,

 

[ebs17]

After all, this action is not an action in itself.

As I stated, there is much more to it than finding duplicates.

What do you derive from this?

And yes: In my perfect world, a unique index prevents unwanted duplicates, and per inconsistency checking, duplicates are not fired at a unique index during an import.

 

[jpl458]

Here is a sample showing how to use DCount to do conditional formatting.

Cheers,

I can't find where the Dcount expressions is. Open tblEDP in design view, opened conditional formatting and found no way to edit rule so I could see how it is written. I am in a part I have never been in before.

 

[bastanu]

Click in the field then click Conditional formatting and click on the Edit rule.
Cheers,

 

[jpl458]

Click in the field then click Conditional formatting and click on the Edit rule.
Cheers,

Found it, but having trouble understanding the statement. Does it t read like this in narrative form:

Find all the EDP-Competetor5 in tblEDP that are = the previous value. I don't see how that changes the colors of consecutive clusters that are not the same? What part am I missing? It must work per your example. What triggers the color change?

 

[jpl458]

The dCount() might work if the recordsource contains any duplicates in the given column but it can't specifically find adjacent duplicates.

I was aware if that and made sure the input was sorted accordingly. And, in my terms, it works vertically, not horizontally, if the data is in sequential order, vertically. is that correct.