Code ONLY works w/ctrl+break in debug mode

[babowden]

Within an Access 2000 form (continuous), there is a total calculated at the bottom of the form which is the sum of all the records (currency field). This total value is compared to a constant yielding a difference (variance). This variance is evaluated and if positive, the background color of the variance field is set red, else it is set to green. While the variance figures are calculated correctly immediately upon advancing to the next record (on-current event), the colors are not correct until advancing to yet another record (2nd on-current event occurence).

As soon as a control break is inserted into the code, the program works perfectly without requiring the 2nd on-current event occurrence. Remove the control break and the problem reoccurs.

Any ideas?

 

[willir]

babowden

Try inserting a Me.YourField.Requery method in your code. This will force the field to update itself.

Richard

 

[babowden]

This is already in the code.

The code works when you step through it and it doesn't when you just try to run it without stepping through the code.

 

[HBWAL]

try me.repaint

 

[RoyVidar]

[tt]Me.Recalc[/tt] ' recalculates all calculated controls

- perhaps place it in the after update event of the controls this sum is dependent upon...

Since you're using a2k, have you considered using conditional formatting? (format menu, in the first dropdown there's a possibility of using "expression is"...)

Roy-Vidar

 

[CajunCenturion]

Sounds like a timing problem. Could you please post the code in the OnCurrent event?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[babowden]

Here is the code for Form_Current and ColorRefresh Subs

Let me say again...when I step through the code using the visual basic debugger, everything works fine.[\b]

there may me some code which is commented out...I've tried putting this everywhere

BTW..conditional formatting didn't work - thanks anyway


Private Sub Form_Current()
adhLogEvent "Form Current", conEventData 'event log
If Screen.ActiveForm.NewRecord Then
Screen.ActiveForm.KeyPreview = True
[BLI] = BLISave 'needed for ChildBLI dropdown box
[Office] = OfficeSave
[ChildBLI] = Null ' Reset to Null due to BLI change
DoCmd.Requery "ChildBLI" 'Refresh ChildBLI field
End If
DoCmd.Requery "PlannedCommit" 'Refresh Planned-Commit-Total field
DoCmd.Requery "VarAmt" 'Refresh Variance field
ColorRefresh
DoCmd.Requery "ChildBLI" 'Refresh ChildBLI field
End Sub

Private Sub ColorRefresh()
'Forms![2004 SPEND EXPRESS]![VarAmt].SetFocus 'Set Focus upon entry to the form
If [VarAmt] > 0 Then [VarAmt].BackColor = 255 ' Set Variance to red
If [VarAmt] = 0 Then [VarAmt].BackColor = 13303807 ' Set Variance to light yellow
If [VarAmt] < 0 Then [VarAmt].BackColor = 8454016 ' Set Variance to green
DoCmd.Requery "VarAmt" 'Refresh Variance field
End Sub

 

[willir]

babowden

Nothing obvious...

Do you know where it fails. Specifically, does it enter and exit the ColorRefresh module? (I am sure you know about using Debug.Print to the immediate window)

Do you normally reference the form without the Me reference? I find using Me.FieldName pretty helpful.

You can also try adding Refresh...
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Richard

 

[CajunCenturion]

You have a DoCmd.Requery "VarAmnt" in the Form Current immediately before the call to ColorRefresh, and then as the last line in ColorRefresh you requery VarAmnt again. Why twice?

For the purposes of speed, I would use a case statement to set the color, which will be much faster than executing 3 if statements.

Also, there are some properties which require the control to have the focus, but BackColor is not one of them. I would remove the SetFocus call because that is generating lots of additional messages, and possible, depending on the overall Forms' designs, additional OnCurrent events.

I would also take all of the code in the Form_Current event and move it into a separate function.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[RoyVidar]

You're all faster than me... (probably a timing issue, that too;-))

I think the method Willir mentioned in his first reply is faster than docmd.requery <controlname> for requerying individual controls.

If you're using the Sum function, or adding up controls etc, I still think the Recalc method in the after update event of the control(s) your sum is dependent of might be worth a try (in stead of individual requeries in the on current). That way, the calcuations would be finished before the on current event fires (or try it as one of the first executable lines of the on current event).

If you're using other means of calculations, such as the domain aggregate functions (Dlookup, Dsum...), then consider changing those. At least in a FE/BE setup, they are known to be slow.

Also, on possible redundancy, the ChildBLI control is requeried twice if it's a new record (if newrecord, first assigned Null, then requeried).

Roy-Vidar