DateADD

[Stemdriller]

Hi everyone.

A subform has a TextBox called 'PlannedStartDate' bound to table tblCSP
The main form (frm_ContractHeader) has a DateRequired Field.

On the subform, if I puts it's default value as =DateAdd("d",-33,[Forms]![frm_Contractheader]![DateRequired]) then the textbox is blank.

If I make the 'Control Source' =DateAdd("d",-33,[Forms]![frm_Contractheader]![DateRequired]) then the correct date appears in the text box, but will not add an entry into the field in the table.

Can someone offer any light?

Thanks
Gareth

 

[Beetle]

Can you offer any more details about the context?

It's typically not a good idea to store calculated results like this, but you may have a scenario that calls for it.

If the PlannedStartDate is always going to be 33 days prior to the DateRequired, then I would recommend you just display the calculated result in your text box rather than store it in a table field. On the other hand, if the PlannedStartDate can vary, and you need users to be able to overwrite this 'default" value if necessary, then you would store it in a table field, but in that case you'll need to use a little code in the Before Update event of the form to insert the value. Something like;

Private Sub Form_BeforeUpdate (Cancel As Integer)
 
If Me.NewRecord Then
    Me!PlannedStartDate =DateAdd("d", -33, Me.Parent!DateRequired)
End If
 
End Sub

 

[Stemdriller]

Thanks for responding,

I believe I may have to query it at a later date, so if the date is in a table then I have that option.

You make a good point though.

Thanks again

 

[Stemdriller]

Forgot about weekends and holidays!!!!

Looking through this forum adding this little parameter isn't going to be easy.

Any suggestions

GW

 

[Stemdriller]

Hi

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!PlannedStartDate = wdateadd([Forms]![Frm_ContractHeader]![Daterequired], -49)

End If

End Sub

The textbox (PlannedStartdate] does contain the correct date, but still can't get this date into the table

 

[Beetle]

Is the text box bound to the field in the record source?

 

[Stemdriller]

If I put the function =wdateadd([Forms]![Frm_ContractHeader]![Daterequired],-49) as the ControlSource the correct date appears. But if i bound it to the relevant field in the table it comes up blank?

 

[Beetle]

The text box needs to be bound to the relevant field in your form's record source (table or query). So the Control Source needs to be the field name. That's how the data gets to the table. To get the "default value" into the text box, you need to use some code in the form's Before update event, as I demonstrated in #2. You can't put an expression in the Control Source of the text box because the Control Source is use to bind the text box to the field.

Have you ever created an event procedure before?