Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting? (2000)

    I have a control in the header section of a subform that totals a list of controls in the detail section of the subform, using an expression in the total control's Control Source property (=Sum([Allocation])). I want this control to change it's background colour to red when its value exceeds the value of a control on the subform's parent form. The form is part of a stock allocation application. The parent form has a control called Available, and I want to warn the users if the total stock that they are trying to allocate exceeds the stock that is available.

    The users can over-type the allocations in the detail section of the subform. When they do, I run a test in the after update event of the detail section control (first using Me.Recalc to allow the total control to keep up with any changes) and turn the total control red if its value gets too large. This works fine. What I am having trouble with is getting the total control to stay red if the user moves to another record in the main form, then returns to the original record. When they do, the total control is back to its normal colour.

    I've tried putting a similar test in the subform's onCurrent event but I get an error because, at that stage, the calculated control has no value. I assume from this that calculated fields are given values after the current event has fired. Is there another event that I can use, that fires after the calculated fields have had their values set? I could run a query, or a DSum, in the onCurrent event, to find out what the total value will be, but that might be quite slow and the users want to flick through the records on the main form quickly as they look for problems.

    I hope this makes sense.

    Ian

  2. #2
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting? (2000)

    I've just discovered that the onCurrent event is not the right one, as this would fire each time the user changed records in the subform. I then tried the subform's onActivate event, but this does not fire at all if the user just flicks through the records in the main form. I'm stuck.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting? (2000)

    Did you set the else part in your code ?
    If Condition = True Then
    Background color = Red
    Else
    Background color = White
    Endif
    If you don't, once turned red it will stay red.
    Francois

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting? (2000)

    Yes I did have an else section to my If statement, and that worked fine when changes were being made in the subform. The problem was when I moved to a new record in the main form, and the total allocated (from the data in the subform) exceeded the total available (held in a control on the main form). At this point the calculated control, which is on the subform, should really be red, to warn the user, but in fact had returned to its default colour. I was unable to find a suitable event that would allow me to compare the values of the controls on the subform and main form when the user flicked between records on the main form.
    In the end, I gave up, and the warning indicator is now on the main form. It doesn't look as impressive, but it works.
    Thanks for your help.

    Ian

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting? (2000)

    You could put the code to check in the on current event of the main form.
    Francois

Posting Permissions

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