Access 2010 - Calculated fields - using result and then editing

[snedders]

I have created a form which contains a calculated field called ReportDue from a query. The calculation in the Query takes a StartDate field and adds a Timescale to calculate the ReportDue. This works fine. However, on occasions this date may need to be adjusted because of factors outside our control, so the calculated field needs editing, which obviously I can't do, so I created a second field on the form and then used Default Value property to populate it with the contents o the ReportDue control. This works fine and I can edit this control if the date changes. However, what happens when I close the form is the edited date is lost and the default returns, not only that but every other record's default value has become the same...HELP!

[burrina]

We would need to see your query code and form control code. Of course Me.Dirty = False would save the results and a requery would refresh the data.

[John_G]

Hi -

Because you are using a calculated value from the query, in order to change it, you have to change the data that it is based on - there is really no way around that.

The new control you created on the form can be edited because it is not bound to a query field. But because it is not bound to a query/table field, that edited value cannot be saved, either. Where would it go?

The fact that it is unbound is also the reason that it is the "same for every record" - it isn't really, but the default value only affects the first occurance when the form is opened, and after that it keeps taht value, or whatever value you edit it to. If you want that unbound field to change every time you move to a new record, set it in the "On current" event of the form:

Me![unboundcontrolname] = me![Reportdue]

But you are still going to have to decide how to deal with that adjusted value - a new field in the underlying table would be the only way, I think.

HTH

John

[snedders]

Hi John. Thanks. Of course...derrr! Sometimes you just get too close to things. I already have a field in the underlying table called DateDue. I guess, thinking on my feet, I could somehow take the value from the Unbound control and pass it to that field? Might even add a button instead for changing the date, as it doesn't occur very often and then pop up a box for the new date to be entered, which in turn would populate the table field. I'm sure I'll get there. You have certainly given me food for thought. Thanks
PS Notice you are from Ottawa - had a fab time 40 years ago as a student in Ottawa, stayed with a lady who was PA to one of the govt ministers. Happy days!

[John_G]

Easier might be to just bind that "new" form control to the DateDue field, and only populate it automatically if it does not yet contain a date:

if isnull(Me![DateDue]) then Me![DateDue] = me![Reportdue]

in the On current event.

John

[snedders]

Thanks John, sounds like a good idea. I am back on it again this morning. Feel like the fog is starting to clear. Once this is sorted I can start on the reporting around this...jobs nearing due date, on due date and overdue.

Have a good day. Thanks again. Julie

Easier might be to just bind that "new" form control to the DateDue field, and only populate it automatically if it does not yet contain a date:

if isnull(Me![DateDue]) then Me![DateDue] = me![Reportdue]

in the On current event.

John