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_Percentage, I have the following code:

Private Sub Binding_Percentage_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
 

Basically, in my field Binding_Percentage 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

Private Sub Binding_Percentage_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
 

-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:

Private Sub Binding_Percentage_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
 

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_Percentage with just one of the controls:

 
    Me.SumGWP.Requery
 
End Sub
 

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:

=DSum("[GWP]","ReQryForecast","Val([Binding_Percentage]) >= 75")
 

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:

 
     With Me.SumGWP
          DoCmd.Requery
    End With
 
End Sub
 

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

Any ideas,

Keith.

[kcdoell]

Stewart:

In fact conducting some other experiments the:

Private Sub Binding_Percentage_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
 

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

me.dirty = false

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]

...

me.dirty = false

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:

Private Sub Binding_Percentage_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
 

Basically, once I change the Binding_Percentage 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):

Private Sub Binding_Percentage_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
 

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:

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

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?

[kcdoell]

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?

NeoPa:

Yes, it is the same database.

[NeoPa]

OK Keith. I'll arrange further via PM in that case.

[kcdoell]

OK Keith. I'll arrange further via PM in that case.

NeoPa:
Could my problem be that my [SumGWP], [Sum50GWP],
[SumNWP], [Sum50NWP], are Calculated Controls and are not Bound Controls. If I was to try to build a query to do the calculations and then bound the controls to that RecordSource, would make this problem go away? I am thinking that it should not make a difference. I was reading something I do not fully understand; that since my form is continous the many instances of this Control on the Continuous Form will display the same value in all instances....

Does that make sense to you?

Keith.

[NeoPa]

Until you mentioned the Continuous Forms bit with the unbound controls, No.

After that it all started to make sense. Yes, if you have unbound controls on a continuous form you would certainly expect for those controls all to have the same data (In reality "All" is a misnomer as they are actually multiple displays of the same control).

The suggested solution of working them out in the query coming in to the form seems a workable and eminently sensible one.

[missinglinq]

Stewart, when you say

not one of the old menu-based DoCmds which some people use to save the record

I assume you're referring to

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

You actually can use

DoCmd.RunCommand acCmdSaveRecord

I just use

If Me.Dirty Then Me.Dirty = False

because I learned it first and I can never remember the the exact command acCmdSaveRecord without looking it up!

Linq ;0)>

[kcdoell]

NeoPa:
I have been trying to convert my code in a query. I had the following in the unbound's control, control source:

Sum50GWP: =Nz(DSum("[GWP]","ReQryForecast","[Binding_Percentage] = 50"),0)

That gave me zero if the result was null

Now for the query approach:

I thought I would do the following:

Field: Sum(Nz([tblAllForecast.GWP],0))
Table: 
Total: Expression
Criteria: [Binding_Percentage]=50
 

But I can not seem to incorporate the null result, meaning it will display blank instead of a 0.

Why should it be so different. Thanks for all you help. I am feeling pretty dumb today...

Thanks,

Keith.

[NeoPa]

A number of things spring to mind, although I wouldn't expect the effect you describe to be the result of any of them.

1. Sum(Nz([tblAllForecast.GWP],0)) should probably be Sum(Nz(tblAllForecast.GWP,0))
2. In the query you are getting the data from a source of [tblAllForecast] whereas in the control it comes from [ReQryForecast].
3. In the query it's hard to determine whether the criteria is being applied before or after processing the data. It looks like it's after (SQL - HAVING) which makes little sense. The Control would be equivalent to a WHERE.
4. NB. You are calling the Nz() in the control AFTER the summing. In the query you are doing it before.

PS. When posting queries it's usually better to post the equivalent SQL rather than what is shown in the Query grid.

[kcdoell]

A number of things spring to mind, although I wouldn't expect the effect you describe to be the result of any of them.

1. Sum(Nz([tblAllForecast.GWP],0)) should probably be Sum(Nz(tblAllForecast.GWP,0))
2. In the query you are getting the data from a source of [tblAllForecast] whereas in the control it comes from [ReQryForecast].
3. In the query it's hard to determine whether the criteria is being applied before or after processing the data. It looks like it's after (SQL - HAVING) which makes little sense. The Control would be equivalent to a WHERE.
4. NB. You are calling the Nz() in the control AFTER the summing. In the query you are doing it before.

PS. When posting queries it's usually better to post the equivalent SQL rather than what is shown in the Query grid.

To tell you the truth, this thing did work until somebody noticed the the calculations where reverting to the past when they clicked into another record row that was being displayed. It is almost like the Cache is not being cleared. Now I am trying to build a query that will perform the calculation, serve as the contol source, but in my mind I still am going to point the contol source to a query which, your right, I am was already pointing to ([ReQryForecast]). The only difference being that I won't have to do a Dsum? or Sum???

Because I have never done this before I must be me misunderstanding how to point unbound controls based on a query (that perfoms calculations).



Keith.

[kcdoell]

Solved It!

Refreshing a contol on a form

Thanks,

Keith.

[NeoPa]

That doesn't seem right Keith. Binding and refreshing (or requerying) are two separate things.

I think you need to bind. That is to say you need to set the Control Source property to one of the fields returned by the underlying recordset (table or query).

[kcdoell]

That doesn't seem right Keith. Binding and refreshing (or requerying) are two separate things.

I think you need to bind. That is to say you need to set the Control Source property to one of the fields returned by the underlying recordset (table or query).

If I really knew what that meant, I am just relieved that the problem went away...... I think permanently. For now, I need to move on to other pressing issues. I can always double back when time allots.

Keith.

[NeoPa]

I can't argue with that. At least the point is made in here for when you (or anyone else for that matter) want to revisit the issue :)