Executing On Current Calculation W/O Opening Form

[kristi1023]

I have a textbox on a form that is based on the calculation (Date()-[Promise Date]), which updates OnCurrent event. Since this calculation needs to be updated daily, is there a way to force this calculation without manually opening the form and scrolling through all records?

Thank you.

 

[earthandfire]

The textbox is on a form, so surely the calculation would only be relevant when the form is displayed?

 

[kristi1023]

I'm using the calculation result in reports, as well. Therefore, I would like the calculation to update each time the database is opened. Is this possible?

 

[earthandfire]

Then could you not put the calculation in the report (or in the query that the report is based on)?

 

[TheAceMan1]

How are ya kristi1023 . . . . .

Using an [blue]AutoExec[/blue] macro run an [blue]Update[/blue] query on the proper table.

See Ya! . . . . . .

 

[PHV]

The calculation result should not be stored in the table but retrieved (always accurate) by the query.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kristi1023]

I am using this calculation in many places and should have planned better for this problem. I am also using the calculation result in code for changing the value of the backcolor property on reports. Having discovered this issue a bit late in my development, being able to force the oncurrent event, rather than changing my queries, reports and code would be the easiest method to solving this problem. Please advise if it is possible.

Thanks.

 

[kristi1023]

Thanks Aceman, I did try this, but it seems risky to update and delete records just to update one field. Also, I had a problem when adding the criteria (date()-[Promise Date]).

PHV, the form is based on a query, thus calculation retrieved from the query. My problem is prompting the query which needs input from the form. I could add the calculation to the query, but that would require changing all the references to the field DaysLate, which is calculated as Date()-[Promise Date] onCurrent event on the form.

I am missing something obvious?

Thank you.

 

[PHV]

Provided that [Promise Date] is already a field in your query, simply add a calculated field in the query grid:
DaysLate: Date()-[Promise Date]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

kristi1023 . . . . .

An Update query doesn't delete anything . . .

See Ya! . . . . . .

 

[kristi1023]

PHV, unfortunately, DaysLate is also a field in my query which I have many references to. As suggested, I have added a calculated field in the query grid,
DaysLate: Date()-[Promise Date], which partially solves the problem. I will now have to change the sql to reference the query rather than the table. This is why I would have prefered to cue the calculation tied to the form.

Thanks to everyone for your suggestions!