After Update Event, cursor jumps from the record in which I made the change to top...

[kcdoell]

Hello:

I have a continuous form that displays records. In the AfterUpdate event of one of the fields, Binding_Percent age, I have the following code:

[code=vb]
Private Sub Binding_Percent age_AfterUpdate ()
'Updates the Total calculation in the control "SumGWP" and "SUMNWP"on the quick reference
'table on the Forecast form

With Me![SumGWP] And Me![SumNWP] And Me![Sum50GWP] And Me![Sum50NWP]
DoCmd.Requery

End With
End Sub
[/code]

Basically, in my field Binding_Percent age a user can select various choices from a drop down list I created. Depending on their choice, through the above mentioned code, I am refreshing various unbound fields on summary table that I created on the form. This all works great except that since I am doing a DoCmd.Requery the cursor jumps from the record in which I made the change jumps to the first record on the list.

So let’s say there were 5 rows each row representing a record on my form and I made a change on row 3, after the change the cursor would jump to row 1.

I believe this is happen because at the same time I must be requerying the form thus impacting the record source since it is set to a query I built.

How can I stop that from happen?? Any ideas??

Thanks,

Keith.

[Stewart Ross]

Hi Keith. A requery carried out on the form resets the current record back to the beginning of the recordset, which is not what you need. You need to requery the affected fields only, and this is not done with a Docmd.

For future reference, trying to use With in an ANDed form is incorrect, and anyway a With is not needed here as you are not referring to a list of properties belonging to a common control.

Replace your code with
[code=vb]
Private Sub Binding_Percent age_AfterUpdate ()
'Updates the Total calculation in the control "SumGWP" and "SUMNWP"on the quick reference
'table on the Forecast form

Me![SumGWP].Requery
Me![SumNWP].Requery
Me![Sum50GWP].Requery
Me![Sum50NWP].Requery

End Sub
[/code]
-Stewart

[kcdoell]

Hi Keith. A requery carried out on the form resets the current record back to the ............... ...].Requery
Me![Sum50NWP].Requery

End Sub
[/code]
-Stewart

Stewart:

Thanks for the idea I am going to get back to you but I need to run off to a meeting........ ....

Thanks!

[kcdoell]

Stewart:

Okay, I changed the code to the following like you inidicated:

[code=vb]
Private Sub Binding_Percent age_AfterUpdate ()
'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
'on the quick reference table on the Forecast form

Me![SumGWP].Requery
Me![SumNWP].Requery
Me![Sum50GWP].Requery
Me![Sum50NWP].Requery

End Sub
[/code]

And nothing happens (does not requery). No errors were hit. Did I miss something?

Thanks,

Keith.

[kcdoell]

Stewart:

I am trying to understand what is going on so I focused just on my AfterUpdate Event of Binding_Percent age with just one of the controls:

[code=vb]

Me.SumGWP.Reque ry

End Sub
[/code]

So no matter how I write the Me. expression (with [ ], ! or .), still nothing happens. I do though see visually on the form that my "SumGWP" is going through the process of requering. I say this because it blanks out for a nano second and then reappears.

In the control source of "SumGWP" I have the following:

[Code=vb]
=DSum("[GWP]","ReQryForecas t","Val([Binding_Percent age]) >= 75")
[/code]

Could my issue somehow be in here? Not too sure what the docmd.Requery was doing differently?

All I know is that if I put back the following:

[code=vb]

With Me.SumGWP
DoCmd.Requery
End With

End Sub
[/code]

My SumGWP recalculates... ....but my jumping problem comes back

Any ideas,

Keith.

[kcdoell]

Stewart:

In fact conducting some other experiments the:

[code=vb]
Private Sub Binding_Percent age_AfterUpdate ()
'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
'on the quick reference table on the Forecast form

DoCmd.Requery

End Sub
[/code]

Is the only thing that updates the "SumGWP" calculation..

Now I am really confused....

Keith.

[Stewart Ross]

Hi Keith. The DSUM will not be updated unless the current record has actually been stored - not just one of its fields updated. If you update any field value the record is marked as 'Dirty' by Access (their jargon for altered) but not stored until you tab or mouse off the record. Without the record being stored the Requeries are operating on the previous values. The DoCmd execution is different, as to do the requery the 'dirty' record is stored first.

If I am right you should be able to resolve this by placing the following line before the requeries
[code=vb]me.dirty = false[/code]
which will save the record for you.

-Stewart

[kcdoell]

Stewart:

Thanks for getting back to me. I did as you said and it worked! I was also reading and applied the Me.Refresh all by its lonesome in the AfterUpdate event and that worked as well.

In the end, I left your solution since that was what I was shooting for, thanks!! But I am curious how the Me.Refresh works......... If you have any other great insights let me know.

I like how you explain what is happening, it helps to "really" understand what is going on....

Keith.

[NeoPa]

...
[code=vb]me.dirty = false[/code]
which will save the record for you.

-Stewart

Are you sure Stewart?

I thought that would tell the form that the record needn't be saved (without saving it), rather than actually saving it?

This is quite an old concept (from early OS work) where there is a Dirty flag for each buffer to indicate to the OS whether or not the buffer needs to be flushed to disk.

Saving the record (flushing) doesn't remove the data, but sets the Dirty flag to FALSE.

Setting the flag to FALSE manually, causes the automatic saving (flushing) to be omitted as it thinks there is nothing to save.

Any change to the record (buffer) automatically causes the flag to be set to TRUE.

[Stewart Ross]

Hi NeoPa. I did test it out myself, and it does indeed save the record. Anti-intuitive I know - and when I first saw this solution (from another excellent contributor to this very forum! - thread linked here) I was sceptical - but it works. I would rather have an explicit command such as me.save though (and not one of the old menu-based DoCmds which some people use to save the record).

-Stewart

[Stewart Ross]

Hi Keith. Refresh updates the records to show all changes made; requery can do the same, but in addition it will show all new records added and remove records deleted by other users, which refresh does not do. In the context of the DSUM performed by your unbound fields saving the record occurs both with the refresh and with setting me.dirty false. As you will see from NeoPa's post, the use of me.dirty in this way is distinctly non-standard in terms of database practice in general, so refresh may be more correct in this circumstance.

The use of requery in a form context is usually more localised to specific controls (such as combo boxes) where an update has taken place and dependent values need to be requeried to refresh them. In my experience it is more unusual to requery the form's own recordset, as to do so resets the current record pointer as discussed in post 2. Refresh appears a better choice in the circumstances.

-Stewart

[NeoPa]

... I would rather have an explicit command such as me.save though (and not one of the old menu-based DoCmds which some people use to save the record).

-Stewart

Amen to that Stewart.

I could never find a good explanation for why a Save is done via DoCmd (at least why Me.Save is not at least an option).

PS. I did check the Help and it was quite sensible about how it described the Dirty flag. That is excepting of course, that it was inaccurate :(

[kcdoell]

Hello:

I thought I was done with this one but a user who is testing my DB just pointed out a problem.

I used the following in the afterupdate event:

[code=vb]
Private Sub Binding_Percent age_AfterUpdate ()
'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
'on the quick reference table on the Forecast form.

Me.Refresh

End Sub
[/code]

Basically, once I change the Binding_Percent age and tab into another field on the same row my numbers (My unbound controls that are performing a calculation) change appropriately but the minute I click on to another row (using the mouse or tab) on my table the numbers change. It looks like the numbers change to the old settings. When I click back to the row I changed the numbers go back to what they are supposed to reflect. I changed the code to the following (Stewart's Suggestion):
[code=vb]
Private Sub Binding_Percent age_AfterUpdate ()
'Updates the Total calculation in the control "SumGWP", Sum50GWP, "SUMNWP" and Sum50NWP
'on the quick reference table on the Forecast form.

Me.Dirty = False
Me![SumGWP].Requery
Me![Sum50GWP].Requery
Me![SumNWP].Requery
Me![Sum50NWP].Requery

End Sub
[/code]

But the same strange thing happens......


Any ideas,

Keith.

[kcdoell]

Stewart:

I was just experimenting and when I put back my old method that created the jumping to the first record issue:

[code=vb] With Me![SumGWP] And Me![SumNWP] And Me![Sum50GWP] And Me![Sum50NWP]
DoCmd.Requery
End With
[/code]

I did not have this problem where values would revert back to previous ones once a user clicked on other rows on the form.

Any ideas? This is a show stopper for me..

Thanks,

Keith.

[NeoPa]

Is this the same database that you're working on in the Query results based on two control fields, I am at wits end....... thread?