VBA change the color of a field in a table (1 Viewer)

[bastanu]

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

 

[Pat Hartman]

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

 

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

 

[Pat Hartman]

Find all the EDP-Competetor5 in tblEDP that are = the previous value.

Therein lies the rub. There is NO concept of "previous" in a relational database. "previous" exists (although not expressly) in a report but only because a report is a sequential process. That allows you to compare the value in the current record with the value in the previous record. Then save current to previous. HOWEVER, this won't help you because the "previous" record is already gone and you can't go back and colorize it.

However, if the dCount() counts the value in the current record, that should solve the problem because all rows of each set of duplicates should return a count >0. So if you have three instances of "123" in a row. The first will return a count of 3 so you color it. The second will return a count of 3 so you color it, and so will the third. This is probably your solution Of course if immediately following that you have two instances of "124", those also get colored and you have a block of 5 colored but they are actually two different sets of duplicates. So, the reality is, there is no true solution that isn't based on a recordset manipulated with VBA.

 

[jpl458]

Therein lies the rub. There is NO concept of "previous" in a relational database. "previous" exists (although not expressly) in a report but only because a report is a sequential process. That allows you to compare the value in the current record with the value in the previous record. Then save current to previous. HOWEVER, this won't help you because the "previous" record is already gone and you can't go back and colorize it.

However, if the dCount() counts the value in the current record, that should solve the problem because all rows of each set of duplicates should return a count >0. So if you have three instances of "123" in a row. The first will return a count of 3 so you color it. The second will return a count of 3 so you color it, and so will the third. This is probably your solution Of course if immediately following that you have two instances of "124", those also get colored and you have a block of 5 colored but they are actually two different sets of duplicates. So, the reality is, there is no true solution that isn't based on a recordset manipulated with VBA.

 

[jpl458]

So, the reality is, there is no true solution that isn't based on a recordset manipulated with VBA.

Which is what I thought in the first place. If you look at post # 14, I ask that very question.

Thanks

 

[Pat Hartman]

We've been circling that drain since the beginning. But, that's the bottom line. You need a linear process to do what you want to do and forms and queries are not linear. Queries process sets of data. ie. all members of a set get acted upon the same way.

 

[jpl458]

We've been circling that drain since the beginning. But, that's the bottom line. You need a linear process to do what you want to do and forms and queries are not linear. Queries process sets of data. ie. all members of a set get acted upon the same way.

Great minds, Pat. I thought I spelled it out but probably was not clear enough. What occurred to me is, the assemblers (3, Bal, Z 80, Compass, a CDC assembler) I wrote in were not verbose. This here is really verbose, and I spend half my time correcting spelling errors. Is there a best practices for naming conventions in ACCESS and VBA, or do you roll your own through experience. The latter is not cool if someone else is going to read your code. Been using tbl, frm, qry at the end of names, as an example.

 

[The_Doc_Man]

Actually, there HAVE been a few discussions in this forum over naming conventions. The best rule I've ever heard is to make a simple convention and then STICK TO IT. If the convention is too hard to maintain, it was a bad convention. Depending on the size of the project, it often makes sense to have a "Decisions Bible" in which you make notes about your conventions, choices, etc. and keep that around on paper as well as digitally. That way, if you have to put the project down then whoever gets it next doesn't work in a vacuum. The worst naming convention of all is "the best naming convention that you can't maintain." It is possible to overthink conventions. It is possible to underthink conventions. Sadly, deciding that EITHER of those situations has come to pass is a two-pass algorithm that can only be determined in hindsight.

 

[Pat Hartman]

Doc is 100% right. Consistency is your friend and mine too if I have to read your code.
Most people use standard prefixes for objects. But suffixes are technically superior if you are using them for column names because there is less typing to get to something meaningful. I tend to not qualify column names but if I were to change my methods, I would use suffixes although I don't like data type suffixes on column names.

This type of question always brings me back to my first year in the business. I was trying to find a bug in a program written by someone else who was conveniently on vacation. By the end of the second day, I was pulling my hair out. I couldn't find it. Keep in mind this was back in the days before everyone was developing with text editors on a personal computer and running 20 tests in an hour. If I needed to compile or test, I had to make the change, get it keypunched or take the elevator up 4 floors to the public key punches to do it myself, send the job to the computer room and wait for results which was always hours at a minimum. I'll give you a summary of the problem statement. Department63 was taking the wrong path through the code and so the resulting calculation was incorrect. It all came down to the D88Switch. Think of this as a type of multi-valued variable. You would expect it to contain ONLY the value 88 because it was tied specifically to something that happened only for department88. Turned out the month before, someone had added 63 as another value and this was the first time the code had run in production since it was changed. I was too young and trusting. Who would ever name something D88 if it referred to more than ONE department? Apparently, originally it only applied to D88 but when I finally got to examining the actual value of this field, turns out it contained FOUR, count em', FOUR department numbers and not one of those other three programmers had bothered to change the name of the Switch! The last one had added 63 but should have added 62 so apparently didn't even bother to test the change and that ended up being the bug. This was a monthly program and this was the first time it had run since the last change.

 

[jpl458]

Doc is 100% right. Consistency is your friend and mine too if I have to read your code.
Most people use standard prefixes for objects. But suffixes are technically superior if you are using them for column names because there is less typing to get to something meaningful. I tend to not qualify column names but if I were to change my methods, I would use suffixes although I don't like data type suffixes on column names.

This type of question always brings me back to my first year in the business. I was trying to find a bug in a program written by someone else who was conveniently on vacation. By the end of the second day, I was pulling my hair out. I couldn't find it. Keep in mind this was back in the days before everyone was developing with text editors on a personal computer and running 20 tests in an hour. If I needed to compile or test, I had to make the change, get it keypunched or take the elevator up 4 floors to the public key punches to do it myself, send the job to the computer room and wait for results which was always hours at a minimum. I'll give you a summary of the problem statement. Department63 was taking the wrong path through the code and so the resulting calculation was incorrect. It all came down to the D88Switch. Think of this as a type of multi-valued variable. You would expect it to contain ONLY the value 88 because it was tied specifically to something that happened only for department88. Turned out the month before, someone had added 63 as another value and this was the first time the code had run in production since it was changed. I was too young and trusting. Who would ever name something D88 if it referred to more than ONE department? Apparently, originally it only applied to D88 but when I finally got to examining the actual value of this field, turns out it contained FOUR, count em', FOUR department numbers and not one of those other three programmers had bothered to change the name of the Switch! The last one had added 63 but should have added 62 so apparently didn't even bother to test the change and that ended up being the bug. This was a monthly program and this was the first time it had run since the last change.

Been there, done that, and your post takes me back. I remember arguing with the DP manager that we needed 3270 terminals in all the branches, which would eliminate picking up the sales sheets in trucks every morning, and we could process them as they happened. He thought the idea was dumb. In my exit interview the CFO told me I was right, and that I should stay and manage the change, but the DP manager was still there, so, fat chance of me staying. I remember a trick we used to play on each other. In assembler you could use a Rep card to make minor changes to the code. So a guy would have his 800 card deck sitting on his desk in a tray. So we would slip in a print off card somewhere in the first half of the deck, then about 50 card later put in a print on card. Then watch him panic the next time he assembled the program and part of the print out was missing. Those were the days. Or, being in the computer room and setting your coffee on top of a 1403 N1 Printer while you went to the console that was 20 feet away, only to hear the printer run out of paper and the top started to rise spilling your coffee on 300 pages 17.5 11 inch printouts.
I could go on and on.

 

[Pat Hartman]

Somehow the old days provided more entertaining "oops" opportunities for slapstick comedy. I was about 7 months pregnant and my husband came to my office for lunch one day. On the way to the cafeteria, I had to drop off my deck to compile the program I was working. Being ever the gentleman, he insisted on carrying the box of cards for me. Stepping onto the escalator to go down, he tripped and the box flew up in the air and cards rained down on the crowd. Everyone going up or down scrambled to pick up the cards. I got them back into the box right side up and lucky me, I had resequenced the deck two days before so sorting got me almost back to ground zero

 

[jpl458]

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

Since the table will grow through time, I created a form in the form wizard, that in design view, only has one row:

But when I open the form there are 1500 rows. Now, if I want to apply code to the rows to find duplicates and change colors for consecutive groups of different sets of duplicates, how do I address each row in this kind of form. Or, is that not possible. I have the code sorted out in my mind, I just don't where/how to apply it, Lets say we want to apply colors to the Left6 column.

 

[jpl458]

Somehow the old days provided more entertaining "oops" opportunities for slapstick comedy. I was about 7 months pregnant and my husband came to my office for lunch one day. On the way to the cafeteria, I had to drop off my deck to compile the program I was working. Being ever the gentleman, he insisted on carrying the box of cards for me. Stepping onto the escalator to go down, he tripped and the box flew up in the air and cards rained down on the crowd. Everyone going up or down scrambled to pick up the cards. I got them back into the box right side up and lucky me, I had resequenced the deck two days before so sorting got me almost back to ground zero

The first thing you learn about dropping cards trays is to stand back, let them fall and watch, and try to remember the order of the clusters on the floor, and hope you get lucky putting the cards back together. If it was a lot of cards we ran them through a card sorter. If you ran your deck through a card reader a lot of times the cards would get frayed and you would have duplicate the deck on card/read/punch. As I think about, I really don't know how we got as much done as we did, back then. When I went to work for IBM 2 things showed up, me and the 2311 disk drive. That drive was hydraulic, it had an oil filled piston that moved the read/write head across the disc platter. If the computer room got warm, say close to 80 degrees, we would get seek errors since the oil had expanded in the pistons. I consider all that to be pre columbian processing. If your young and reading this, you are all soft!

 

[Pat Hartman]

how do I address each row in this kind of form. Or, is that not possible

Access is NOT Excel. There is no such thing as a cell. Excel is a flat matrix where every column of every row has a specific name. That point is called a cell. In a relational database you work with sets. A query selects a set of records and you do something with that set such as update all the rows or display them in a form or report.

You've still got your spreadsheet hat on.

In a form, the code behind the form refers to the CURRENT record. That is the record with the focus. So, if focus is on the sixth row, and you refer to Me.StartDate, you are referring to StartDate in the 6th row. NOT R6C3.

If you want to use a code loop to process a set of data, you can do that but you would NEVER do it in the code behind a subform where you are looking at ONE (at a time) of many rows in the recordset.

There is no built in way to handle colorizing sets of duplicate records. I told you how to "almost" get what you want back in #25. This is the simplest method although not efficient and has only a single flaw -- two adjacent sets of duplicates won't be distinguished.

You can get closer with a recordset process but you would need to add a field to the table to indicate what "set" a duplicate belonged to. Then you can sequentially assign set numbers to all the duplicate records. Conditional formatting can then be used to convert those set numbers to colors. Now we get to the options for color available in Conditional formatting, they are sparse and bad and so limited that it is likely that you would run out of usable colors if you had more than a few sets of duplicates. Then you have to clean up the color fields using an update query and rerun the process again until you end up with no more duplicates. Tell me PLEASE that you will then IMMEDIATELY fix your RI to prevent future duplicates or this will become the life's work for someone.

You are obsessed with color. Maybe you should take a different view. Create a report using a query that only selects the duplicates. Add grouping so that there is a visible break for each set. Now - what are you going to do about the duplicates? Are you going to delete them? Are you going to change something so they are no longer duplicates? If you display the report in Report view rather than Print preview, you can add buttons that work and you can use the doubleclick event of a control to open a form that lets you fix that record. You might be able to update the report while it is open but i doubt it unless the Refresh All button will fix it. But it will be disconcerting for records to disappear from the report on the fly so I wouldn't do it.

Let me summarize, If you are going to run any code on a set of records, it would not be within a form bound to that recordset. So, if you want to run any of the code suggestions, make an unbound form. Add some controls to allow you to select sets of records unless you will work with the whole table at one time. Then have the code open the Edit form (after you updated the color flag) showing the duplicates or containing all the records if that works better for you.

 

[jpl458]

Access is NOT Excel. There is no such thing as a cell. Excel is a flat matrix where every column of every row has a specific name. That point is called a cell. In a relational database you work with sets. A query selects a set of records and you do something with that set such as update all the rows or display them in a form or report.

You've still got your spreadsheet hat on.

In a form, the code behind the form refers to the CURRENT record. That is the record with the focus. So, if focus is on the sixth row, and you refer to Me.StartDate, you are referring to StartDate in the 6th row. NOT R6C3.

If you want to use a code loop to process a set of data, you can do that but you would NEVER do it in the code behind a subform where you are looking at ONE (at a time) of many rows in the recordset.

There is no built in way to handle colorizing sets of duplicate records. I told you how to "almost" get what you want back in #25. This is the simplest method although not efficient and has only a single flaw -- two adjacent sets of duplicates won't be distinguished.

You can get closer with a recordset process but you would need to add a field to the table to indicate what "set" a duplicate belonged to. Then you can sequentially assign set numbers to all the duplicate records. Conditional formatting can then be used to convert those set numbers to colors. Now we get to the options for color available in Conditional formatting, they are sparse and bad and so limited that it is likely that you would run out of usable colors if you had more than a few sets of duplicates. Then you have to clean up the color fields using an update query and rerun the process again until you end up with no more duplicates. Tell me PLEASE that you will then IMMEDIATELY fix your RI to prevent future duplicates or this will become the life's work for someone.

You are obsessed with color. Maybe you should take a different view. Create a report using a query that only selects the duplicates. Add grouping so that there is a visible break for each set. Now - what are you going to do about the duplicates? Are you going to delete them? Are you going to change something so they are no longer duplicates? If you display the report in Report view rather than Print preview, you can add buttons that work and you can use the doubleclick event of a control to open a form that lets you fix that record. You might be able to update the report while it is open but i doubt it unless the Refresh All button will fix it. But it will be disconcerting for records to disappear from the report on the fly so I wouldn't do it.

Let me summarize, If you are going to run any code on a set of records, it would not be within a form bound to that recordset. So, if you want to run any of the code suggestions, make an unbound form. Add some controls to allow you to select sets of records unless you will work with the whole table at one time. Then have the code open the Edit form (after you updated the color flag) showing the duplicates or containing all the records if that works better for you.

Reading this over and over. It;s processing by exception. We don't need to see the records that are not duplicated. Only the dupes matter. That part was the snake that biting me till I read your post. Let me try some things, and if they don't pan out I'll be back. Well, I checked and I had already created a query to find dupes using the query wizard. Now I have to figure out how to best present it.

I can't thank you enough, Pat.

 

[Pat Hartman]

You are quite welcome. Sometimes, it is hard to step back and see what is really required. The best way to present the dups will depend on how the user wants to work with them. The report will be the most clear since you can easily separate the groups and don't need color to do it. a form will be more difficult since you don't have a grouping option. If seeing all the dups together will be too confusing for the users, you can use the method I mentioned where you open a report in Report view rather than in Print preview. The user can then double click on a group and that can open a form showing ONLY that set of duplicates The user can then move on to the next set of dups.

 

[jpl458]

You are quite welcome. Sometimes, it is hard to step back and see what is really required. The best way to present the dups will depend on how the user wants to work with them. The report will be the most clear since you can easily separate the groups and don't need color to do it. a form will be more difficult since you don't have a grouping option. If seeing all the dups together will be too confusing for the users, you can use the method I mentioned where you open a report in Report view rather than in Print preview. The user can then double click on a group and that can open a form showing ONLY that set of duplicates The user can then move on to the group