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.
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:
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:
"[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.
Self Taught In ALL Environments.....And It Shows!