Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Deer Park, Long Island
    Posts
    12

    Unanswered: Change Main form backcolor based on ANY value in subform

    Hello to all!

    Assume I have a main Form "Customers" and a subform "Orders".

    I want to change the color of the main forms Detail section(Section(0)) to red if ANY value in the ShipCountry Field in the subForm is 'USA'

    For example: If move to CustomerID 37 in the main form and they have three orders:
    1 to Spain, 1 to Germany, and 1 to USA,
    then I want the detail section of the main form to turn red (or else stay standard grey)

    Now If I move to the next customer in the main form (CustomerID 38) and they have two orders:
    1 to Italy, and 1 to France
    I want the Main form detail section to stay the standard grey.

    Make sense?

    Thanks
    jeffcoach

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    In the Main Form's OnCurrent event:


    Code:
    IF Nz(DLookup("[ShipCountry]", "Orders", "[CustomerID] = " & CLng(Me.CustomerID) & "[ShipCountry] = 'USA'"), "") = "USA" Then
       Me.Detail.BackColor=vbRed
    Else
       me.Detail.BackColor = -2147483633
    End If
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Jan 2004
    Location
    Deer Park, Long Island
    Posts
    12
    OK,

    Thanks for your help so far.

    Two things:

    I forgot to metion that I am using the Northwind sample database so CustomerID is "text".

    So when I ran your code I got "Error 13 Type mismatch" on the first line.

    OK, so I then changed Clng to Cstr and I got:
    Runtime Error 3075
    Syntax error (missing Operator) in query expression
    '[CustomerID]=ALFKI[SpipCountry]='USA''

    Can you offer any insight?

    Thanks again
    jeffcoach

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Hmmmm...me bad...Try this.....

    Code:
    IF Nz(DLookup("[ShipCountry]", "Orders", "[CustomerID] = '" & Me.CustomerID & "' AND [ShipCountry] = 'USA'"), "") = "USA" Then
       Me.Detail.BackColor=vbRed
    Else
       me.Detail.BackColor = -2147483633   ' Default
    End If
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Jan 2004
    Location
    Deer Park, Long Island
    Posts
    12
    Thanks for the speedy reply!


    It works fine now.

    Can you do me a favor and explain "How" this works.

    I was under the impression that I would have to loop through all the records in the subform, searching for any ShipCountry of "USA"

    Thanks again!
    jeffcoach

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    Based on the assumsion you provided (which was obviously adequate information), I carried the assumsion futher to determine the the SubForm's Link Child Fields and Link Master Fields properties were related to the CustomerID field since it displayed all the particular orders for the current Customer.

    With this in mind... I poled the Orders table for the current CustomerID which matches the CustomerID displayed on your Main Form. I used the DLookup function for this within the Main Form's OnCurrent event because the OnCurrent event is fired every time you display a different record (so to speak). See Access Help with regards to the DLookUp Function.

    In mean reality, the Criteria within the DLookUp function:

    Code:
    "[CustomerID] = '" & Me.CustomerID & "' AND [ShipCountry] = 'USA'"
    should also go a little further and include some other criteria that would indicate perhaps the orders we want to pole are only non filled orders otherwise it is concievably possible that we could pull up a previous record of the same Customer with a previously filled order shipped to the USA. The DLookUp function will pull up the first record encountered in table which meets the criteria. Therefore we will need to be a little more specific. Perhaps something like this:

    Code:
    "[CustomerID] = '" & Me.CustomerID & "' AND [ShipCountry] = 'USA' AND [OrderShipped] = False"
    The DLookup function was enclosed within the Nz() function so as to handle the fact that DLookup would pull up nothing in which case would be NULL and we don't want that. We want to handle either USA or a empty string otherwise you just get an error stateing something like "Invalid use of NULL".

    In a nutshell, what the DLookup is doing is this:

    Check all records in the Orders Table starting from the top and let me know if there is a record in there that has such and such CustomerID and has shipping to the USA.

    Once you read up on the DLookUp function you will get a better grasp of things.


    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  7. #7
    Join Date
    Jan 2004
    Location
    Deer Park, Long Island
    Posts
    12
    Thank you so much!
    jeffcoach

Posting Permissions

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