I have a database showing the numbers on lottery tickets with check boxes for matches. Each record represents a lottery ticket. It contains the following fields:
TickNr and SFX (Ticket Number and Suffix) identify each ticket.
N1, N2, N3,N4,N5,N6 (integers) and M1,M2,M3,M4,M5,M6 (Yes/No with default of No). The N’s are the fields containing the numbers from the lottery tickets. When the lottery is drawn, I match the numbers drawn to the numbers on the lottery ticket (using VBA) and if a number matches, I change the corresponding M to Yes (using VBA). I created a form that shows the numbers with the corresponding checkbox beside it but that really doesn’t help much in my “analysis”. What I’d like to do is change the number to a bold red if it matches.
I tried several things. One was trying to change the forecolor and fontweight properties on the form and another was making the change in the table. The problem is that I could only change a property for a field (column) – not for a specific field in a specific record.
I was thinking of exporting the table to an Excel WS and setting up conditional formatting based on the values in the Yes/No fields. However, I’d rather stay in Access.
When I tried to use a datasheet there was no way to get to the "conditional formatting" icon but I persevered and finally set up the icon on the quick toolbar and got it to do what I wanted. Thanks. One of the things I learned in my searches thru the Help file is that if you're going to do something like this, your form controls should not have the same names as the field names in your table. It says that Access is confused by this but doesn't pop an error.
I found an article in the Help file ("Textbox.ForeColor") Property that shows how to do what I was trying to do in VBA. However, I never did get it to work for my form but have a couple of more things to try. I pulled out my old Access 2000 Developer's Handbook by Getz, Litwin and Gilbert and found some interesting stuff but haven't tried them all.
@StarTrekker: You would have when working with the Collection object, such as:
You never now for sure what is added to the collection, the control object, the field object, or their value. I remember spending hours trying to figure out why my application was not working the way I intended because of such a problem.
I did some further study and have things working as I'd like them to but not by using VBA. I used the Conditional Formatting Tool. It worked on a Datasheet form, a Multiple Items form and a single items form. I also tried it with the "Match" fields as text (T or F) and as Y/N fields. They all worked.
I opened the form in design view then selected the number field (Nr1, Nr2, etc) that I wanted to change the forecolor and fontweight properties of. Then I clicked on the Conditional Formatting tool and under Condition 1, I opened the drop down and selected "Expression is" then typed in the name of the corresponding match field (Match1, Match2, etc) and entered Matchx = "T" or Matchx = "True".then clicked on the Bold and Red selections. I had to do this for each number field. It worked exactly as I wanted.
BTW, it worked when I left the textbox names on the form the same as the field names in the table.