G
Guest
I am using Access 2002. I'm not quite a beginner, but although I'm learning
fast I'm still not much good at writing code. Apologies for the length of
this post, but the problem is complicated (or it seems that way to me).
I have set up a form which contains two subforms. The form shows clients
from a list, the first subform displays the budgets they've been allocated
for various items, and the second subform displays the expenses they've
incurred against those budgets. There is a sum field on the second subform,
but because I'm showing it in datasheet format it's not displayed. I want
users to be able to change the amount of an expense in the second subform,
and then i want them to be prevented from saving it if the new total expenses
(ie the sum field on the second subform) is greater than the budget amount
(ie the textbox on the first subform).
I've tried to do it like this (probably not the best way, but all I could
think of):
First, I've set up a text box on the main form which compares the two values
and shows the text strings "ok" or "error", as appropriate. Then, I've set
up a "save" button with a macro attached, and the macro has conditions which
save the changes and close the form if the textbox says "ok", or else pop up
message box and stop the macro if it says "error".
So far this all works fine. The problem is that if a user changes an amount
in the expenses subform, the sum field on that subform isn't updated until
they press return or move to another control. Hence the text box on the main
form isn't updated either. If they change the expense amount to a value that
should cause an error, and then click the save button WITHOUT pressing return
or tab or clicking anything else in between, then the text box is still
saying "ok" and the change is saved.
I've tried a whole range of things to fix this, including setting up a macro
to repaint the subform and form and trying to get it to run before the save
macro. I've nested the update macro and the save macro inside a third one so
that they run sequentially, but that seems to bypass the conditions on the
save macro. I should probably do it by writing some if-then-else statements
instead of using macros, but my code-writing skills aren't up to it.
Can anyone help? What can I do to ensure that the text box is updated
before the save macro runs?
fast I'm still not much good at writing code. Apologies for the length of
this post, but the problem is complicated (or it seems that way to me).
I have set up a form which contains two subforms. The form shows clients
from a list, the first subform displays the budgets they've been allocated
for various items, and the second subform displays the expenses they've
incurred against those budgets. There is a sum field on the second subform,
but because I'm showing it in datasheet format it's not displayed. I want
users to be able to change the amount of an expense in the second subform,
and then i want them to be prevented from saving it if the new total expenses
(ie the sum field on the second subform) is greater than the budget amount
(ie the textbox on the first subform).
I've tried to do it like this (probably not the best way, but all I could
think of):
First, I've set up a text box on the main form which compares the two values
and shows the text strings "ok" or "error", as appropriate. Then, I've set
up a "save" button with a macro attached, and the macro has conditions which
save the changes and close the form if the textbox says "ok", or else pop up
message box and stop the macro if it says "error".
So far this all works fine. The problem is that if a user changes an amount
in the expenses subform, the sum field on that subform isn't updated until
they press return or move to another control. Hence the text box on the main
form isn't updated either. If they change the expense amount to a value that
should cause an error, and then click the save button WITHOUT pressing return
or tab or clicking anything else in between, then the text box is still
saying "ok" and the change is saved.
I've tried a whole range of things to fix this, including setting up a macro
to repaint the subform and form and trying to get it to run before the save
macro. I've nested the update macro and the save macro inside a third one so
that they run sequentially, but that seems to bypass the conditions on the
save macro. I should probably do it by writing some if-then-else statements
instead of using macros, but my code-writing skills aren't up to it.
Can anyone help? What can I do to ensure that the text box is updated
before the save macro runs?