Hi,

I have a query which updates a currency field, it simply replaces the value. Given the query has done the updating and not someone typing in the
change, the AfterUpdate event won't fire.

Can anyone point me in the right direction as
to how to get this AfterUpdate event to fire without typing intervention ?

Thanks,
Gerry

What is it that you want the After Update event to accomplish? If you post a little more info as to what you're trying to do with the update query and After Update event, we might be able to give you a better way to accomplish both. http://www.access-programmers.co.uk/ubb/smile.gif

BL

In my main form, I trigger an AfterUpdate event by changing a date, which executes a query, that updates a value in a subform.

When that value is updated (in the subform), I want to execute another query which deletes rows from a calculated table and then re-adds them.

Basically, If that value in the subform changes, I want to act on it. However there doesn’t seem to be a way to detect the change. The reason I want to detect the change in the subform is because I need the values in the subform to re-add the calculated entries.

I tried the OnChange event, but it doesn’t trigger via a query update.

Is there a way to do this ?

Thanks again

Okay, this is what I THINK you will have to do to make it work. Somebody else may have a better way, but I have not actually done this, so this is more of a conceptual thing based on what I know works. So, I hope it actually does when put into practice.

What I would say, is that you need to change your queries into SQL that can be executed from the code itself, so that you can code in exactly what you want to happen.

So, this is Pseudocode so you will have to get the actual figured out as I don't have enough time to get everything down for you.

Private Sub Field1_AfterUpdate()
Insert Dim statements for SQL strings and subform Pre-change value
Define SQL strings and subform prechange value

Execute the first SQL statement
Execute the second SQL statement
Requery the subform
Code to check the current value of the subform against the variable with the start value
If the value is different, run code you want to do if value has changed
Else
exit sub
End Sub

I hope that gets you on track. Use the search in the Bulletin Board for executing and the correct syntax (using quotation marks) of the SQL statements. There's alot there.

Good luck and don't hesitate to post more if you need to as you work your way through it. I may not be able to help where the SQL strings are involved (I have only done it a few times) but there are plenty of people here who can help.

BL http://www.access-programmers.co.uk/ubb/smile.gif
hth

You can't do what you want the way you are approaching the problem.

Events on a form are only fired when something happens on that record on that form. Running queries in the background is totally unrelated to what is happening on a form and there is no way to make a query fire form update events. Therefore, what ever code or calculations that you use on the subform will need to be incorporated into a second query that runs immediately after the first.

Pat:

That's why I suggested what I did so that they wouldn't be reliant on an After Update event to happen. They can run the SQL queries and then test to see if the value has changed, without having to trigger another event.

BL

Bob,
*** Code to check the current value of the subform against the variable with the start value.

I'm just starting to do the VBA programming,
so my next question would be ?

Would I use the .Value and .Text properties
to get the old and changed values OR
the .Value and .OldValue properties given
my value is currency datatype ?

Or do you have a more simple way in which
this check can be done ?

Thanks.

"What I would say, is that you need to change your queries into SQL that can be executed from the code itself, so that you can code in exactly what you want to happen."

You never need to use embedded SQL unless you are building it on the fly. If it never changes except for a parameter value, it should be stored as a querydef so that it can be optimized.

"Code to check the current value of the subform against the variable with the start value
If the value is different, run code you want to do if value has changed"

Since the queries are totally outside the control of the subform and its events, you do not have any way of comparing current and previous values of fields in the subform's recordset.