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,

 

[Pat Hartman]

but can' figure out how to change the color.

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

 

[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 - Microsoft: Access Forms

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,

 

[Pat Hartman]

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

Changing colors in a form or report is quite doable. You were trying to do it in a query where it is NOT possible in the way you want.
The query looks for demographic errors. i.e. records with missing info. The validation is done on the data entry form for new client records but our converted data was pretty bad and so I couldn't enforce RI at the table level. The users used this search to go through and find errors that needed fixing.
Here's a chopped up image. Too much sensitive data to show the whole form.

 

[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.

 

[Pat Hartman]

In a continuous or ds view form, you have a SINGLE instance of the form repeated multiple times to show multiple rows. Because this is actually a SINGLE instance of the form, there is only a SINGLE set of properties and the properties apply to all visible instances of the form. So, if the background of CustName is green, it is green for ALL visible rows. Conditional formatting gives you a way of controlling this to some degree but VBA in general will result in affecting all visible instances of the object, not just the one holding the current record. Conditional formatting relies on values in the CURRENT record.

You are trying to use the previous record to control the format of the current record. In a relational database, there is no concept of a fixed previous record as there is in a flat file which is why "previous" isn't a reserved word. It has no meaning. In a flat file, "previous" is fixed because records are stored physically character by character end to toe. When the records are fixed in length, there may or may not be a record separator such as CrLf but if the records are variable as .csv files are, then there will be a separator between each record.

xxxxxx!dudnsal!hhhhhhhhhhhhhhhhhhhhh!

In a relational table, the "previous" record could be anywhere in the table space including after the current record.

If this formatting is important to you, you can do it with a report. You can even use the report as a subform, except of course that the report is not updateable.

The best solution for "duplicates" is to not allow them to be entered. PERIOD. If you can't avoid entering them, then use queries to return rows where duplication occurs and deal with the duplication separately.

 

[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.