Subform sum updating mainform control problem

[vmon]

I have a control on the footer of a subform that sums a column. I set a control on the mainform equal to the control from the subform footer. I want to change the color of the mainform control based on the sum value. Problem seems that the sum is not updated in time for it to be evaluated for setting the color. Is it possible that the code runs too fast for the value to get to the mainform? I don't want to slow it down, is there another way?

John

 

[fwatanabe]

John,

Chances are that it is not the code running too fast (no such thing - all code should go fast!!) but that you are trying to do the color change in the wrong event.

I just opened up the Northwinds database and made this mod to the Orders form.

Private Sub Form_Current()
If [Orders Subform].Form![OrderSubtotal] > 500 Then
    Me.Subtotal.ForeColor = vbRed
Else
    Me.Subtotal.ForeColor = vbGreen
End If
End Sub

Try it out and see if this helps.
Fuyo

 

[vmon]

That works great but I have a twist to it. I have two subforms each with a subtotal on the mainform. I add them together and compare the sum to a control on the mainform and then set the color. Problem is when the two values are added the sum is displayed but the color setting does not work. Both subforms are 1 to many to the mainform. ANy ideas?

 

[fwatanabe]

John,

The event you place your code on is likely the problem here. Are the subform values being edited and then you expect the color to change? If so, you will have to put your code on the AfterUpdate event of each subform.

You may have to explain the sequence of events to me (e.g. what steps does the user perform, then at what point you expect the color change to happen.) Also copy & paste your code along with what event it is on.

 

[vmon]

One problem I may have is 1 to many relationships between the main form and the subforms. But the sequence is this. A column in each subform is sum'd in the footer of the subforms and set on the mainform (downtime and runtime). I add those values together in another field on the mainform (total time). Then I compare total time to a field on the mainform (scheduled). If equal no color. If less than scheduled yellow, if greater than scheduled then red. As records are added, change, or deleted from subforms the sum's values change but the color is not. The after update is from the subforms, I also included the call.

Thanks for taking your time to help.

Private Sub Form_AfterUpdate()

Call calcTotalTimeColor

Me.Shop_Order.SetFocus

DoCmd.GoToRecord , , acNewRec

End Sub

Public Sub calcTotalTimeColor()

If [Forms]![frmproduction]![txtRunTime] = "" Or [Forms]![frmproduction]![txtRunTime] = 0 Then
If [Forms]![frmproduction]![txtDownTime] = "" Or [Forms]![frmproduction]![txtDownTime] = 0 Then
If [Forms]![frmproduction]![Scheduled Time] < 1 Then
[Forms]![frmproduction]![calcTotalTime].BackColor = vbWhite
Else
[Forms]![frmproduction]![calcTotalTime].BackColor = vbYellow
End If
End If
Else
[Forms]![frmproduction]![calcTotalTime].BackColor = vbRed
End If

End Sub

 

[vmon]

I think I have found the problem but not sure how to fix. When the mainform loads is does an add new record. The sum'd field in the footer of subform 2 is null. I changed the table structure to be 1 to many mainform to subform1 and 1 to many subform1 to subform2. If I debug the form the field on the mainform that gets the sum'd footer value is showing #Error and subsequently the calculated value. The fieled on footer1 and footer2 are setup the same but no errors on footer1.

What do you think?