How do you create a field which has a default value, but is editab

[Melanie H.]

Thank you in advance for your answer.

I have a database that I am constructing for some Project Managers. I
actually downloaded it off of the Microsoft Site, but what I have now
scarcely resembles what I started out with.

I need some help with a calculation.

I have used =DateAdd() in the past in two capacities:

#1. =DateAdd("d",7,Date()) as a Default Value on for a field in my table.
Works Good. You can edit it on any form.

#2. =DateAdd("d",7,[Complete Returned Approval]) in Control Source property
of a text box for a form or report. You cannot edit this date, since it is
bound. It does not save to the table either. For obvious reasons, you don't
have a field name in the Control Source.

My problem is my current instance, neither one of these workable cases
apply. As I stated earlier I have added numerous columns and other tables to
the original database. I have a master table titled Projects and it has 35
or so fields. I also have a Form entitled Project Details where a user will
feed the information to the form. All the information can not be known or
entered at the time the project is set up and the data will be entered as it
becomes available. I have multiple instances where I need the same basic
code, so I will use one example.

Our company will compile documents for submission to the customer on what we
understand they need manufactured. At some point all that information will
be returned. The date in which we receive the returned documents is
represented in the "Complete Returned Approval" Field. My guys want to have
the next field "Cleaned up Shop Drawings" do an "Auto Fill in" with the date
based on our 7 day turn around. In other words =DateAdd("d",7,[Complete
Returned Approval]). My problems: #1. If a customer needs massive changes
it might take 10 days, so this needs to be editable and #2. It needs to write
to the table.

Since, that information is not available at the time of initial project
entry, I knew that it would probably not be possible to have this calculation
in the Field name for the Table, but I had to tried it anyway just to see
what it would do and it gave an error. "The database engine does not
recognize either the field 'Complete Returned Approval' in a validation
expression, or the default value in the table 'Projects'."

Can anyone help me? Is there anyone out there that has had this senario
pop-up before. I did not see a thread for a situation exactly like mine.

Thanks again,

Melanie

 

[KARL DEWEY]

Why did you not use =DateAdd("d",7,[Complete Returned Approval]) in the
default of the text box?

The Control Source is the field of the table it is bound to and it can be
edited and will save to the table.

 

[Melanie H.]

I tried that 1st and it doesn't work. I even tried an After Update code to
set Cleaned up Shop Drawings to Null and Requery it. That did not work. I
need to base the contents of Cleaned up Shop Drawings, off of Complete
Returned Approval. On initial entry you will get a project number, project
name, project manager, project status and project cost. The other fields
like Complete Returned Approval may remain blank for months. I need the
Field Labeled Cleaned Up Shop Drawings to spring into action only when the
Complete Returned Submittal field is populated. It is almost like, because
the Complete Returned Approval field is initially blank that the Cleaned up
Shop Drawings field always defaults to blank.

Melanie H.

Why did you not use =DateAdd("d",7,[Complete Returned Approval]) in the
default of the text box?

The Control Source is the field of the table it is bound to and it can be
edited and will save to the table.

Thank you in advance for your answer.

I have a database that I am constructing for some Project Managers. I
actually downloaded it off of the Microsoft Site, but what I have now
scarcely resembles what I started out with.

I need some help with a calculation.

I have used =DateAdd() in the past in two capacities:

#1. =DateAdd("d",7,Date()) as a Default Value on for a field in my table.
Works Good. You can edit it on any form.

#2. =DateAdd("d",7,[Complete Returned Approval]) in Control Source property
of a text box for a form or report. You cannot edit this date, since it is
bound. It does not save to the table either. For obvious reasons, you don't
have a field name in the Control Source.

My problem is my current instance, neither one of these workable cases
apply. As I stated earlier I have added numerous columns and other tables to
the original database. I have a master table titled Projects and it has 35
or so fields. I also have a Form entitled Project Details where a user will
feed the information to the form. All the information can not be known or
entered at the time the project is set up and the data will be entered as it
becomes available. I have multiple instances where I need the same basic
code, so I will use one example.

Our company will compile documents for submission to the customer on what we
understand they need manufactured. At some point all that information will
be returned. The date in which we receive the returned documents is
represented in the "Complete Returned Approval" Field. My guys want to have
the next field "Cleaned up Shop Drawings" do an "Auto Fill in" with the date
based on our 7 day turn around. In other words =DateAdd("d",7,[Complete
Returned Approval]). My problems: #1. If a customer needs massive changes
it might take 10 days, so this needs to be editable and #2. It needs to write
to the table.

Since, that information is not available at the time of initial project
entry, I knew that it would probably not be possible to have this calculation
in the Field name for the Table, but I had to tried it anyway just to see
what it would do and it gave an error. "The database engine does not
recognize either the field 'Complete Returned Approval' in a validation
expression, or the default value in the table 'Projects'."

Can anyone help me? Is there anyone out there that has had this senario
pop-up before. I did not see a thread for a situation exactly like mine.

Thanks again,

Melanie

 

[John Spencer]

You need to use code in the after update event of the control that is
bound to the field [Complete Returned Approval] and set the value of a
control bound to[Cleaned up Shop Drawings].

The code would look something like

Private Sub txtCOMPLETE_RETURNED_APPROVAL_AfterUpdate()
If IsNull(Me.txtCOMPLETE_RETURNED_APPROVAL) = False Then
If IsNull(Me.txtCleanedUpShowDrawings) = True then
Me.txtCleanedUpShowDrawings = _
DateAdd("d",7,Me.TxtComplete_Returned_Approval)
End If

End If
End Sub

 

[Melanie H.]

Thank you so much! That worked like a charm.

Melanie H