Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41

    Unanswered: Changing properties

    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.

    Any ideas?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In a form, you can change the properties of a single textbox. If the form is in datasheet view, you can use the conditional formatting features, similar to what can be found in Excel.
    Have a nice day!

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    If you are looking at a continuous form, you can only use conditional formatting, the criteria of which is limited to the same field the formatting applies to.

    If you try to format the textbox using VBA by changing it's properties, the entire column will change, because there is only ONE textbox.

    There is no easy way around that.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41

    Changing Properties

    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.

    Again, many thanks for your suggestions.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Interesting. I always tend to name my controls with the same name as the fields they are bound to and I don't seem to struggle with any naming issues... but I never use datasheets either.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    @StarTrekker: You would have when working with the Collection object, such as:
    Code:
    m_MyCollection.Add Field1
    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.
    Have a nice day!

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks for the tip... If I ever use the collection object I will look out for it.

    TBH, it is more likely that I will do everything I can to steer clear of the collection object!! lol
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41

    Changing Properties

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •