Problem setting default value - referencing issue

[charlie442]

Hi

I have a database which contains a Public Function forecasting dates based on working days, ie excluding weekends and Public Holidays. I wish to update fields based on this formula, but I want the controls to default to this calculation so that the record source can be updated.

If I place the formula in the record source of the control it calculates fine but if it is placed in the default value of the control then it does not seem to be working. I am sure there must be somethinig simple I am missing but not exactly sure what it is.

I have attached the basics in a database. I would be grateful if anyone has any ideas as to why this is not working

Charlie

 

[bob fitz]

Can you post a copy of the db in A2003 mdb format.

 

[charlie442]

Sure. Here it is.

 

[bob fitz]

If you remove the Default Value setting of "Text4" and set it in the form's OnCurrent event

Private Sub Form_Current()
    Me.Text4.DefaultValue = "=basMtgDate(10, [Forms].[Form1].[Text0])"
End Sub

then I think it works as you want it to.

 

[charlie442]

Thanks,

This seemed to work initially, However when I linked the form to the Table Record Source, and the control to the control source field (this is a date field in the underlying table) then it seems to go on the blink again.

Would you know how this is caused?

 

[bob fitz]

Can you you tell us:

1 Are all the text boxes bound to fields in the table? If not which fields are?

2 How do you display dates? (dd/mm/yyyy, mm/dd/yyyy, or something else)
3 The first text box (Text0) shows today's date, as would be expected. With today's date (22/08/2011) in Text0, what date should be returned by your function?

 

[missinglinq]

Placing it in the Form_Current event, you shouldn't be assigning it to

Me.Text4.DefaultValue

but simply to

Me.Text4.Value

or, more simply

Me.Text4

You'll probably also want to place the code in the AfterUpdate event of Text0.

As to storing the results in the underlying Table, you shouldn't! Calculated Values very seldom warrant being stored. They should simply be re-calculated, as needed, in other Forms, Reports, etc.

Linq ;0)>

 

[charlie442]

Thanks Bob

Right basically I am working on a timeline for projects. Each project will have various steps along the way which will all be granted the same length of time (or work days) to complete, but begin at differing dates throughtout the year. The function goes like this #Returned Date# = (number of workdays to complete, start date).
So given the start date I will be able to inform management of the future dates by which certain steps in all the projects should be completed. Hope all that makes sense.
The start date on the form will be bound to the project start date in Tbl_Projects and there will then be around 10 future date fields based on the start date that will have a differing number of WorkDays in the formula but all update to the relevant field in Tbl_Projects (all date fields by which that step in the project should be complete).

The date format I am using is YYYY-MM-DD.

 

[bob fitz]

missinglinq has correctly reminded us that there is seldom a good reason for saving calculations in data tables.

 

[charlie442]

Thanks guys for your assistance,

Let me try and explain myself in a better fashion. I quite agree that storing this calculation in a table is a bad idea. However is it possible to store just the result of the calculation in the table. This would allow me to track progress better. Built into the table is the possibility of extensions on a few steps in the process, thuis it would be handy to be able to save down the projected dates, and later the extensions so that comparison is possible.

 

[charlie442]

Hey

I have successfully manged to do what I set out to do. Thank you both for your help - it is really appreciated
Charlie

 

[missinglinq]

Glad we could help!

Good luck on your project!

Linq ;0)>