Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Unanswered: Conditional formatting

    Hi Guys,

    I'm a bit stuck with conditional formatting, and was wondering if anyone could help.

    I've got a form with address details on it, I want to format the postcode box a certain colour if it exists in another table i.e. [Postcodes].

    in the conditional formatting box I've selected "Field Value Is" then "equal to" then added [Postcodes]![PostCode] and changed the background colour to red.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    don't use conditional formatting
    instead consider putting some code in the controls on format event

    if (postcode exists in table) then
    mycontrolname.backcolor=vbred
    else
    mycontrolname.backcolor=vbwhite
    endif
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2009
    Posts
    5
    Thanks, for the quick reply.

    I'm no good with vb, I was hoping conditional formatting would be able to do it.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by g_walker
    Thanks, for the quick reply.

    I'm no good with vb, I was hoping conditional formatting would be able to do it.
    well then may be this is your opportunity to start using vb, you wont get better unless you try to use it.

    conditional formatting may work... but I don't know, I don't use it, I've hated it with a passion for years. its inflexible, you are limited to 4 conditions, you can only set values for a specific control, its a pig to debug....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2009
    Posts
    5
    OK, I have just had a bash a this, what should the condition part look like?

    I have:

    Private Sub Postal_Code_GotFocus()
    If ([Postal Code] = Tables![Postcodes]![PostCode]) Then
    Postal_Code.BackColor = vbRed
    Else
    Postal_Code.BackColor = vbWhite
    End If
    End Sub

    It errors with:
    "run-time error '424';
    Object required"
    Last edited by g_walker; 01-08-09 at 07:30.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by g_walker
    OK, what event would be best to use?
    Quote Originally Posted by healdem
    don't use conditional formatting
    instead consider putting some code in the controls on format event

    if (postcode exists in table) then
    mycontrolname.backcolor=vbred
    else
    mycontrolname.backcolor=vbwhite
    endif
    you would need to find a mechanism to see if the postcode exists.. thats up to you and your code
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    You could use DLookup by placing it in an unbound textbox

    Here is an example I just pulled from one of my forms and I tested it with Conditional Formatting and it worked.

    =DLookUp("[4NS2]","onesixty","[AgeNB]=[Forms]![PForm]![anb]")

    [4NS2] is a field in the table onesixty and [AgeNB] is the field being referenced in the table onesixty

    This [Forms]![PForm]![anb] is the texbox on the form PForm that is containing the match.

    If 40 is the entry in [Forms]![PForm]![anb] then DLookup looks in the table called onesixty in the field [AgeNB] and goes to the row that has the entry for 40. It then gets the value from the field [4NS2] in table onesixy on the same row where AgeNB is 40

    As a side note the 40 is age next birthday and the entries in field [4NS2] are premium rates for that age.

    Now lets say I enter 70 then the textbox where the DLookup is will be Null because the field [AgeNB] in table onesixty does not go as far as age 70

    Let's say the textbox that has the =DLoookup is called Premium and the field where you want conditional formatting is called MyCondition then for conditional formatting you would select the Expression option and have something like

    [Premium] Is Not Null and perhaps you select bold blue for the font. If Premiums has a value you will get bold blue in [MyCondition] if it is null you will get the font you set in form design.

    So in your case postcode would be the matching field. If there is a matching postcode then the textbox with =DLookup will display the matching postcode from the other table and if there is no matching postcode then the textbox with =DLookup will be null.

    As a side note conditional formatting is meant for continuous forms. As healdem said it is limited but it is very hard to beat on a continous form and especially if the background of the textbox is formatted.

    Hope that helps you some.
    Last edited by Mike375; 01-08-09 at 07:50.

  8. #8
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I just made a little example for you that might make it easy to follow

    =DLookUp("[abc]","pcode","[abc]=[Forms]![PForm]![ClientPostCode]")

    I made a little table with one field called abc and the table was named pcode

    I entered a few postcodes in the field abc in the table pcode.

    When there were matches with the postcode in the main form field ClientPostCode the match was displayed and no match and the DLookup textbox was Null

  9. #9
    Join Date
    Jan 2009
    Posts
    5
    Thanks, Mike375, I see now why it would be eisier to use vb.

    Do you know the correct condition for the VB if statement above?

  10. #10
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by g_walker
    Thanks, Mike375, I see now why it would be eisier to use vb.

    Do you know the correct condition for the VB if statement above?
    It won't be harder if you have continuous form. DLookup is simple

    Just add an unbound textbox to your form and paste the following in to it as the formula

    =DLookUp("[abc]","pcode","[abc]=[Forms]![PForm]![ClientPostCode]")

    Replace what is in red with the other table name that has the second set of postcodes, that is, change "pcode" to your other table name and change the [abc] to the field name in your other table that has the post codes.

    For the blue stuff change the entries to what your form name is and the field name on your form that contains the post code.

    I don't know how to use VBA to get a matching value from another table and if I did it is unlikely I would use it since DLookup is so simple.

    However, I think you are missing healdem's point. His issue is about conditional formatting.

    Whether VBA or DLookup is used to do the match then either way you have a match or you don't have a match, that is, the result is the same.

    So either way you will return a postcode number or return nothing. You formatting is now based on this result.

    Put it this way, in essence you simply want to know if there is a matching postcode in another table that matches the post code on your form. The answere is either Yes or NO and in this case (with the DLookup) the Yes is shown as the psotcode and the No is a null.

    Once you have either value in the DLookup you even use a simple SetValue macro action to change the font colour etc and the SetValue action would have an entry in the condition column

  11. #11
    Join Date
    Jan 2009
    Posts
    5
    Ok, I'll do that.

    Thanks, for your help.

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Here is an example of using that DLookup in VBA to change the font colour

    If IsNull(DLookup("[abc]", "pcode", "[abc]=[Forms]![PForm]![clientPostCode]")) Then

    Forms!PForm!Text105.ForeColor = 32768

    End If

    32768 is green

    Here is a double set of If Then. In this case font for Text105 will turn green when DLookup returns Null and it will turn red if DLookup returns a value

    If IsNull(DLookup("[abc]", "pcode", "[abc]=[Forms]![PForm]![clientPostCode]")) Then

    Forms!PForm!Text105.ForeColor = 32768

    End If

    If (DLookup("[abc]", "pcode", "[abc]=[Forms]![PForm]![clientPostCode]")) Then

    Forms!PForm!Text105.ForeColor = 255

    End If

    For what you are doing such code would be put on the form's OnCurrent Event.

    This will illustrate what healdem was saying about conditional formatting. Let's say that instead of the DLookup returning a postcode or null as is the case here it was like my own first example where a premium is returned.

    Then there could be a whole of bundle of If Then End If covering differing premium rates or premium brackets and you would have virtually an unlimited number of font colours, back colours, altering the size of the text box etc. and etc. all depending on what Premium DLookup returned.

    In other words whether VBA or DLookup gets the value from another table that is irrelevant as to whether conditional formatting or VBA is used to format.

    But if you have a continuous form then it is about 10000 times easier to apply conditional formatting.
    Last edited by Mike375; 01-08-09 at 09:39.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    oops just re read the OP.. you are using it on forms... if its continuous forms then conditional formatting it has to be.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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