Date + 30 on Subform

[DataDiva via AccessMonster.com]

Hello All,

I have created a tabbed form with four subforms. In the first subform there
is a field named
[Corrective _Action_Req_Date]. I set the default value to =Date() in the data
section of the Property Sheet. I would like the date to automatically be
change to current date + 30 when the form is opened. I have used this code:

Private Sub Form_Current()
Me.Corrective_Action_Req_Date = Me.Parent.Todays_Date + 30
End Sub

The code is working but, I keep getting a message "Index or primary key
cannot contain a Null value." I have tried placing the code in almost every
other event within the control, subform and main form. I am not sure what I
am doing wrong. Any insight is appreciated!

Thanks,

Courtney

 

[Beetle]

Setting a control's Default Value does not create a new record. However,
the code that you are using is programmatically inserting an actual
value into the date field in the subform, thereby creating a new record.
This newly created record has no PK, hence the message you are getting
when you try to move off that new record (by closing the form, moving to
another record, etc.).

Why not just make the Default Value =Date()+30 ?

 

[Cheese_whiz]

Hi DataD,

That code looks like it would change the corrective action req date EVEN IF
you already had a value manually entered into it. It is also going to change
every time you open the form to a record on a new date. Is that really what
you want to do?

Most likely you just want the default value to be something like:

=DateAdd("d", 30, Date)

HTH,
CW

 

[Ken Sheridan]

Courtney:

I suspect the problem stems from the fact that your are assigning a Value to
the Corrective_Action_Req_Date control. This means that when you move to a
new record in the subform it is 'dirtied', i.e. an edit is initiated, so
Access will try and save the record if no more data is entered. Instead
assign it to the control's DefaultValue property. This property is a little
unusual in that it is always a string expression regardless of the data type
of the field in question. This is often not significant, but with dates it
is, as a date expressed in short date format (the default) will otherwise be
interpreted as an arithmetical expression. Because of the way the date/time
data type is implemented in Access this will not produce an error but an
incorrect date, probably 30 December 1899, which is 'day-zero' in Access.

What you need to do is (a) assign the date to the DefaultValue property, and
(b) when doing so in code wrap it in quotes characters so its interpreted as
a string expression. While you can use Date()+30 to compute the date 30 days
from now, its better to use the DateAdd function rather than simple date
arithmetic as you then avoid any reliance on the implementation, which is
better programming practice. So, putting all this together we come up with:

Me.Corrective_Action_Req_Date.DefaultValue = _
"""" & DateAdd("d",30,Me.Parent.Todays_Date) & """"

However, why not just set the DefaultValue property of the
Corrective_Action_Req_Date in the subform to:

=DateAdd("d",30,Date())

in the control's Properties sheet. You then don't need any code.

Ken Sheridan
Stafford, England

 

[DataDiva via AccessMonster.com]

Thanks to all three of you for the help!
I put =DateAdd("d",30,Date()) in the default value of my control and it seems
to have fixed my problem!

Happy Halloween!

Courtney