This is a simple task, surely it is possible in access

  • Thread starter Thread starter Darcy
  • Start date Start date
D

Darcy

I want to display a calculation based on items in a form and subforms.

There are several subforms on a tab control.

It would be nice if the calculation were updated when a user changes a value
on the form or any subform.

The acceptable compromise is to perform the calculation whenever a user
moves to a new record.

I tried the before and afterupdate methods of the form. They work great for
changes in the main form but don't fire when changes are made in the
subforms.

The current is great for executing code when the user arrives at a record
but when they make changes nothing happens.

Any suggestions?

Thanks in advance.
 
If the calculation reads data from the text boxes on the form and subform,
it will probably update automatically when a record is not updated.

If not, you could issue a Recalc to the form that contains the calcualtion,
in the AfterUpdate event procedure of both the main form and the subforms.
 
The calculation depends on a function that does a whole bunch of hocus
pocas.

I just went on my form and then hightlighted a subform. It doesn't have an
afterUpdate. It only has: On Enter and On Exit.

Thanks.
 
I just drilled down into the sub form and found that there is afterupdate
events for the individual fields on it.

I put
me.parent.DoMyCalculation
into each of the fields.

This works fairly well but waits till I either navigate within the subform
or navigate within the parent form.

If I DELETE a row in the subform the calculation isn't redone.

Thanks for your suggestions.
 
Ok, I got further...

I just had the onEnter, onExit when I clicked the subform, but if I drilled
down to the form, I got all the form level stuff. The form is an object
within a control so its confusing.

I added the call me.parent.DoSomeCrazyCalculation to the

AfterInsert
AfterUpdate
OnDelete

This worked ok but the OnDelete isn't firing.

I added
After Del Confirm

and that worked better.


This will work satisfactorily because it seems that if you do any of the
following the calculation will take place (and in this case I am storing it
in a text box which is bound to a column of the main table so I am storing
it).

Edit the main form
edit in a sub form
delete in a sub form
add a new record in subform

The problems that I'd like to overcome are this:

1) This doesn't fire until the user navigates in the mainform or subform. It
would be nice if fired whenever they make a change in the form or subforms.
Good thing I'm saving the calculation in the field because if the user
navigates the main form, they don't see the calculation unless they navigate
back to the same record on the main form.

2) I have around 5 or 6 subforms with lots of controls on them. I have to
put the me.parent.DoSomeCrazyCalculation in so many places its ridiculous.

Thanks for your input it has been most helpful.
 
My general solution is to fire the parent re-calc routines in the sub-forms
after update event.

me.Parent.MyReCalc

(you can use the above syntax if you make the recalc function in the parent
form public). This means any public function actually becomes a method of
the form.

So, really, for those 6 sub-forms, using the after update event means only 6
lines of code.

Of course, if you need the calc to update when the users hits tab, then I
would suggest you make a routine in the sub-form that re-calces like:


Function MySubCalc
me.refresh (this will force a disk write on the current subform record)
me.Parent.MyReCalc
end function

Now, in the after update event for all controls in the sub-form, you simple
call the above routine

Call MySubCalc

As mentioned, usually the forms after update event is suffices here.
However, if you need the re-calc to occur when the user changes a value and
hits tab to go to the next field in the sub-form, then the above code
approach is not too bad.
 
You may need to put the call to the calculation in the "On Change" event, to
get the effect you want. Be aware though that its very easy to get stuck in
a loop here, since any change fires the Change event.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Hello Albert, thanks for the input.

Will it get fired when the user deletes a record in the subform?
 
Darcy said:
Hello Albert, thanks for the input.

Will it get fired when the user deletes a record in the subform?
Click to expand...

Not likely...there is no after update event after you delete a record!

I don't see why a after update event should fire when the record is deleted.
(that just don't make sense at all here!).

You can certainly force a re-calc after a deleting occurs. So, just put your
recalc call in the AfterDelConfirm event. Note that you don't want a
me.refresh in this case...but just a call to the re-calc routine.
 
The form in the subform control has an AfterUpdate event.
It also has an AfterDelConfirm event.

Do NOT use the Change event of some control.
 
Yes. Although most times you should be able to put common routines in the
"On Current" event of a subform, to get it called whenever the subform is
edited. Again, its possible to get caught in a loop, or waste a lot of time
processing calls repeatedly.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top