forms/subforms (1 Viewer)

[jcridgeway]

I have a form with a subform. Main form contains info for a requsition. Subform shows ALL receipts against that requisition number. I have a control on the subform that sums the receipts. I want to compare that sum against the amount ordered on the main form. If the sumn of the receipts are LESS than the amount ordered I want the color of the sum on the subform to be red, otherwise, black.
Can anyone help me? Thanks.
- jc -

 

[Matthew Snook]

You could use an event, like "lostfocus" for the subform, or "oncurrent" for the main form; then write a simple "IF" statement to assign the "Fore Color" property of the control that presents your subform sum.

Private Sub Form_Current()
If Me!RequisitionSum >= Me!ReceiptSum Then
Me!ReceiptSum.ForeColor = (black)
Else
Me!ReceiptSum.ForeColor = (red)
End If
End Sub


Matt

 

[jcridgeway]

I had already tried putting code similar to that in the On_Current of the subform and I tried your suggestions... the code is getting evaluated BEFORE the sum of the receipts is getting computed. The sum of receipts is ALWAYS showing up as zero in those code segments. It only gets updated AFTER those events.
If I put the code in the On_Current of the subform, I can get the colors to work properly IF I move off of the first record on the subform onto another record or a new record.
I am at my wits end...
If you (or anyone else) would like to see what I have done, I would be happy to send you a sample mdb.

- jc -

 

[Matthew Snook]

OK, how about if the code is put into either the "afterupdate" event of the sum field itself (maybe when it calculates the sum that is considered an update), or into the "gotfocus" event of the first field of the form.

Sometimes what I do is establish an entry control for the form, which does nothing much except accept the cursor or focus. I put a "gotoCtrl" into the forms "oncurrent" event. Then anything that's in the "onfocus" event of that control is the last thing done when you enter that record.

As far as the wits end, you can tell from my junior status that the end is always in sight for me...

Matt

 

[jcridgeway]

Thanks for you reply Matt...BUT!
Neither of those suggestions work. The On_Update is not triggered by code, it has to be a manual entry (from what I have tried and read).
I tried the On_Focus of the main form with the same results. It appears that ALL of the code of ALL of the events is being executed BEFORE the sum is calculated.

And don't worry about being of "junior status"... at least you answered and tried. Apparently no one else has an answer either.

I reckon that I will have to try somewhere else.
Thanks again.
- jc -

 

[Rich]

The Sum of invoices will not be updated untill you leave the record that contains the amount you are trying to sum, how can it? You will have to Sum the fields with vba in the form's current event and update it in the after update of the amount text box.
HTH

 

[jcridgeway]

Rich,
Forgive me if I am being dense, but I don't understand what you have told me.
I have a main form whose recordsource is a table called Reqs which contains info on each requisition including QtyOrder. This form shows one record at a time.
The subform lists all of the receipts that have been received against that requisition. On that subform is a control called TotRecd which contains the code "=sum(qtyrec)". QtyRec is a field in the table called Receipts that is the recordsource for the subform. There is a one (Reqs) to many (Receipts) relationship established. The main form and the subform work just fine... the TotRecd on the subform is displayed with the correct amount... the only thing I cannot do is evaluate the comparison between TotRecd and QtyOrdered.

When you say "until you leave the record"? Are you saying leave the record on the main form or the subform? The Before and After Update events cannot (to the best of my knowledge) be triggered by vb code. One must type in the control or paste into it.

If I am being hard headed, I apologize, but for the life of me, I do not understand. I just want to be able to skip through the requisitions one at a time and look at the receipts and see if I have received everything that I have ordered. Like I said, the totals work, just not the comparison that changes the colors.

Thanks for your response Rich... I really do appreciate it. Again, I apologize that I don't understand.
- jc -