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

[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.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

You actually can use

DoCmd.RunComman d 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:

[Code=vb]Sum50GWP: =Nz(DSum("[GWP]","ReQryForecas t","[Binding_Percent age] = 50"),0)[/code]

That gave me zero if the result was null

Now for the query approach:

I thought I would do the following:

[code=vb]
Field: Sum(Nz([tblAllForecast. GWP],0))
Table:
Total: Expression
Criteria: [Binding_Percent age]=50
[/code]

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(tblAllFo recast.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(tblAllFo recast.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 misunderstandin g 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 :)