update value without rolling back to first record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a subform that I am using to update table information that also
updates a calculated control in the main form built on a query. When new data
is input I do not see the update to the control unless I advance the record
and come back. I have tried to apply a filter with a macro, but when it
applies it jumps back to record number 1. How do you update information in
the subform and see the change immediately and retain the current record? I
appreciate any help.
 
mike said:
I have a subform that I am using to update table information that also
updates a calculated control in the main form built on a query. When new data
is input I do not see the update to the control unless I advance the record
and come back. I have tried to apply a filter with a macro, but when it
applies it jumps back to record number 1. How do you update information in
the subform and see the change immediately and retain the current record?
Click to expand...


How did you try to do it?

The "normal" way is to use a text box in the subform with an
expression that calculates the value you want to display.
 
I tried to use an expression in a textbox, but it will not update the textbox
unless you advance the record and then come back to it. So I tried to run a
query in a macro using the "applyfilter" function. This works, but then sends
me back to the first record in the table, ie... I update in the form while on
record 43 and when the macro runs I am back at record 1.
Mike
 
In the after update of the field you are updating, have you tried to
repaint the main form?

The macro executing a query will always take you back to record 1.
 
Sorry, but that's too vague to work with. You need to be
much more specific - What expression? Where is the text
box?

If you have the text box in the subform's footer section and
it's using the Sum function on a field in the subform's
record source table/query, it should work as soon as you
save the record with the new/changed value. If your problem
is that you expect it to recalculate before you save the
record, that probably explains the problem.
 
Being a self taught access programmer I probably do not describe things very
accurately, but I will give it another go. I have a table of
information(tblregistration) which stores student names, id numbers,
addresses, etc... I have another table that has multiple
payments(tblpayments) tracked by date and amount linked by id number. I have
a third table which has variable fields, late fees, early
fees(tbllate/earlyfees) which are yes/no checkboxes and discount which is a
currency field again linked by id number.
I have set up a form which shows all of the tblregistration info, and then 3
subforms, one sub has the tblpayment info, one sub has the tblearly/late fee
info, and the third sub has an expression which sums payments, adds
fees(which have been given currency values in another query dependent on
"yes" or "no"), and then subtracts that amount from one of two numeric values
assigned to the student determinate on whether they are a commuter or
not(another yes/no checkbox in the tblregistration). I call that control
"Amount Due". So my problem is when I enter a value in the payment subform,
or I place a check in a checkbox, in order for the expression "Amount Due" in
the 3rd subform to update, the main form record must be advanced to another
student record and then returned. I tried to solve this by applying a macro
on afterupdate of all variable controls, which updated immediately, but it
threw my main form back to record number 1. So what I am trying to do is let
the user update any of the payment or variable fields and immediately see the
change in the 3rd subform control box of the record they are working in.
I do appreciate your help, and if this still doesn't help, I will quit
buggin you.
Mike

Marshall Barton said:
Sorry, but that's too vague to work with. You need to be
much more specific - What expression? Where is the text
box?

If you have the text box in the subform's footer section and
it's using the Sum function on a field in the subform's
record source table/query, it should work as soon as you
save the record with the new/changed value. If your problem
is that you expect it to recalculate before you save the
record, that probably explains the problem.
--
Marsh
MVP [MS Access]

I tried to use an expression in a textbox, but it will not update the textbox
unless you advance the record and then come back to it. So I tried to run a
query in a macro using the "applyfilter" function. This works, but then sends
me back to the first record in the table, ie... I update in the form while on
record 43 and when the macro runs I am back at record 1.
Click to expand...
Click to expand...
 
That helps explain what you're doing, but it would be good
to know HOW you are calculating the amount due so I could
try to figure out what invokes the recalculation.

Regardless of that, the changes must be saved before the
"expression" can recalculate the updated amount due. One
important fact about about when Access saves a record is
that not only are records saved when you navigate from one
record to another within a main/sub form, but a form's
(dirty) record is also saved when you tab/click between the
main/sub forms. This means that you could recalculate the
value in each of the **form's** AfterUpdate event (i.e. when
the form saves a record).

If your "expression" is just a text box control source
expression, then I think you might get the desired result by
adding a line of code to the two subform's AfterUupdate
event:
Me.Parent.subform3.textbox.Recalc
If you are using a VBA procedure to calculate the value for
the text box, then you will probably need to call that
procedure instead of using Recalc.
 
Back
Top