Using date add in form Date/timebox

[wattyl]

I have a form with 4 date boxes the first box is the initial date and the other three are phased at 3days,6months and 1year using the date add() inthe control source inthe box optionson the form. but using this leaves only the initial date on the corresponding table with the projected dates blank on the table, this even though they show on the form. Anyone know what I am doing wrong.

 

[raskew]

Rule of thumb is that you don't store data which can be calculated using queries. Why? Suppose your initial date is changed, then all of the projected dates must also change, and there must be a mechanism to make this happen. Since I, as a user, don't know for a fact that the appropriate updates have occurred, your data is always somewhat suspect.

However, to answer your question, you'd need to use an update query to populate the projected dates in your table. Your present method of using calculated fields to display future dates does nothing for the table. Again, stick with the calculated fields and do away with the additional projected date fields in your table.

Bob

 

[tman138]

This is a common problem. Often only the active control (text box or combo) delivers a value from a query especially if you are pulling it as a select (top row of query) and not as criteria. To test this create a select query and enter pointers to the fields you wish to pull from. Execute the query and see if you get the values from teh form.
Instead of using pointers to form objects in your query, write public functions with the pointers in them.

example In your query- expr1: = fnGetFormField1()

in a code module-

Public Function fnGetFormField1()
fnGetFormField1 = Forms!frmMyForm.tbMyTextBox
End Function
Public Function fnGetFormField2()
fnGetFormField1 = Forms!frmMyForm.tbMyComboBox
End Function
Public Function fnGetFormField3()
fnGetFormField1 = Forms!frmMyForm.tbMyListBox
End Function

Use this technique whenever you run into this problem. Another thing you could check - If your form is bound to a local table, you may have to put a save record command on the after update expression. In DAO 3.60
DoCmd.RunCommand acCmdSaveRecord