Textbox based on Calculation

J

Joan

Hi,

I seem to have forgotten how to handle a situation on a data entry form. I
have a textbox, Me.[Delivery Date] that is based on a calculation using a
value in the Me.[Whelped Date] textbox. The expression is : =[Whelped
Date]+63-Weekday([Whelped Date]+56,5). Currently, I have the Control source
of Me.[Delivery Date] equal to this expression.

The problem is the user wants to be able to edit the amount that is
automatically entered in the Me.[Delivery Date] control as once in a great
while they must change this date from what is actually calculated. I realize
that one must not store calculated data in a table. Therefore, how do I go
about making this control editable and still keep track of what the
[Delivery Date] value is if it is edited on the form but the value is not
recorded in a table?

Would appreciate any suggestions or advice on this problem.

Thanks,

Joan
 
K

Kelvin Lu

How about making [Delivery Date] linked to the field in the table and push
the value of the calculation from the afterupdate event of [Whelped Date].

Another option would be to leave the form as is and add a yes/no field and
another date field to your table, something like [Use Default] and [alt
Delivery Date]. You can then add these to the form with the yes/no default
set to yes and [alt Delivery Date[ not visible. When the user turns this to
no, have [alt Delivery Date] become visible and [Delivery Date] not visible.
The user can then decide which date to use and it will still be there if
they change their mind. Just have your other queries check for the [Use
Default] value and the corresponding date.

Kelvin Lu
 
J

Joan

Hi Kevin,

Thanks for replying to my post.

I tried your first suggestion. I linked the textbox [Delivery Date] to the
field in the table and wrote the following code on the After Update event of
[Whelped Date]:

Private Sub Whelped_Date_AfterUpdate()
DeliveryDateCalc
End Sub

Private Sub DeliveryDateCalc()
Dim dteDeliver As Date

dteDeliver = Me.[Whelped Date] + 63 - Weekday(Me.[Whelped Date] + 56,
5)
Me.[Delivery Date] = dteDeliver

End Sub


However when running the form, after I enter a whelped date and press tab, I
get the following Runtime Error '3327':
"Field 'Delivery Date' is based on an expression and cannot be edited."

The correct date appears in the [Delivery Date] control but as the message
says, I can't edit it. Why is this? I am pushing the value from the
AfterUpdate event. What am I doing wrong?

Joan

P.S. On the AfterUpdate event of [Delivery Date] I have the following code
to indicate if the amount is the default or user entered:

Private Sub Delivery_Date_AfterUpdate()
' if amount in control is entered by user change background color to white
If Me![Delivery Date] <> Nz(([Whelped Date] + 63 - Weekday([Whelped
Date] + 56, 5))) Then
Me![Delivery Date].BackColor = "16777215"
Else ' if amount is calculated, background color is green.
Me![Delivery Date].BackColor = "14744809"
End If
End Sub


Kelvin Lu said:
How about making [Delivery Date] linked to the field in the table and push
the value of the calculation from the afterupdate event of [Whelped Date].

Another option would be to leave the form as is and add a yes/no field and
another date field to your table, something like [Use Default] and [alt
Delivery Date]. You can then add these to the form with the yes/no default
set to yes and [alt Delivery Date[ not visible. When the user turns this to
no, have [alt Delivery Date] become visible and [Delivery Date] not visible.
The user can then decide which date to use and it will still be there if
they change their mind. Just have your other queries check for the [Use
Default] value and the corresponding date.

Kelvin Lu

Joan said:
Hi,

I seem to have forgotten how to handle a situation on a data entry form. I
have a textbox, Me.[Delivery Date] that is based on a calculation using a
value in the Me.[Whelped Date] textbox. The expression is : =[Whelped
Date]+63-Weekday([Whelped Date]+56,5). Currently, I have the Control source
of Me.[Delivery Date] equal to this expression.

The problem is the user wants to be able to edit the amount that is
automatically entered in the Me.[Delivery Date] control as once in a great
while they must change this date from what is actually calculated. I realize
that one must not store calculated data in a table. Therefore, how do I go
about making this control editable and still keep track of what the
[Delivery Date] value is if it is edited on the form but the value is not
recorded in a table?

Would appreciate any suggestions or advice on this problem.

Thanks,

Joan
 
J

Joan

Kevin,

Ignore my just previous post. I figured out why I was getting the runtime
error. My form was based on a query and I had [Delivery Date] as an
expression in my query. Once I made it a field , I no longer get the
runtime error that I wrote you about. Thanks so much for your help.

Joan


Kelvin Lu said:
How about making [Delivery Date] linked to the field in the table and push
the value of the calculation from the afterupdate event of [Whelped Date].

Another option would be to leave the form as is and add a yes/no field and
another date field to your table, something like [Use Default] and [alt
Delivery Date]. You can then add these to the form with the yes/no default
set to yes and [alt Delivery Date[ not visible. When the user turns this to
no, have [alt Delivery Date] become visible and [Delivery Date] not visible.
The user can then decide which date to use and it will still be there if
they change their mind. Just have your other queries check for the [Use
Default] value and the corresponding date.

Kelvin Lu

Joan said:
Hi,

I seem to have forgotten how to handle a situation on a data entry form. I
have a textbox, Me.[Delivery Date] that is based on a calculation using a
value in the Me.[Whelped Date] textbox. The expression is : =[Whelped
Date]+63-Weekday([Whelped Date]+56,5). Currently, I have the Control source
of Me.[Delivery Date] equal to this expression.

The problem is the user wants to be able to edit the amount that is
automatically entered in the Me.[Delivery Date] control as once in a great
while they must change this date from what is actually calculated. I realize
that one must not store calculated data in a table. Therefore, how do I go
about making this control editable and still keep track of what the
[Delivery Date] value is if it is edited on the form but the value is not
recorded in a table?

Would appreciate any suggestions or advice on this problem.

Thanks,

Joan
 
B

Brad McCulloch

Hi Joan.

There is no physical reason why you must not store
calculated fields in a table. It is just usually bad form
to do so, as it is redundant to store information that is
functionally dependent on other fields.

In your case, this guideline does not apply. [Delivery
date] is not determined soley by the value of [whelped
date], as there is the possibility of user override, which
introduces variability into the result for [delivery
date]. In sort, [delivery date] is no longer functionally
dependent on [whelped date].

I would suggest that you calculate the "expected" value of
[delivery date] based on the user's entry of [whelped
date] and the store this value in the text box on the form
that is bound to [whelped date], and hence in the table.
The user is then free to modify.

Regards,

B
 
H

Hugh O'Neill

Brad said:
Hi Joan.

There is no physical reason why you must not store
calculated fields in a table. It is just usually bad form
to do so, as it is redundant to store information that is
functionally dependent on other fields.

In your case, this guideline does not apply. [Delivery
date] is not determined soley by the value of [whelped
date], as there is the possibility of user override, which
introduces variability into the result for [delivery
date]. In sort, [delivery date] is no longer functionally
dependent on [whelped date].

I would suggest that you calculate the "expected" value of
[delivery date] based on the user's entry of [whelped
date] and the store this value in the text box on the form
that is bound to [whelped date], and hence in the table.
The user is then free to modify.

Regards,

B
-----Original Message-----
Hi,

I seem to have forgotten how to handle a situation on a data entry form. I
have a textbox, Me.[Delivery Date] that is based on a calculation using a
value in the Me.[Whelped Date] textbox. The expression is : =[Whelped
Date]+63-Weekday([Whelped Date]+56,5). Currently, I have the Control source
of Me.[Delivery Date] equal to this expression.

The problem is the user wants to be able to edit the amount that is
automatically entered in the Me.[Delivery Date] control as once in a great
while they must change this date from what is actually calculated. I realize
that one must not store calculated data in a table. Therefore, how do I go
about making this control editable and still keep track of what the
[Delivery Date] value is if it is edited on the form but the value is not
recorded in a table?

Would appreciate any suggestions or advice on this problem.

Thanks,

Joan


.


Because of the extra care and code you will need in order to make sure
that a stored result value can be maintained in case a component value
changes, I would not store the calculated result.

In your case I would suggest only storing a value if it is manually
entered in a text box.

On retrieval, if there's a stored (manual) entry then retrieve that,
otherwise use the calculation to retrieve a result.

hth

Hugh
 
J

Joan

Thanks for your reply, Brad. I did just what you described and my form
works great.
Thanks again.
Joan

Brad McCulloch said:
Hi Joan.

There is no physical reason why you must not store
calculated fields in a table. It is just usually bad form
to do so, as it is redundant to store information that is
functionally dependent on other fields.

In your case, this guideline does not apply. [Delivery
date] is not determined soley by the value of [whelped
date], as there is the possibility of user override, which
introduces variability into the result for [delivery
date]. In sort, [delivery date] is no longer functionally
dependent on [whelped date].

I would suggest that you calculate the "expected" value of
[delivery date] based on the user's entry of [whelped
date] and the store this value in the text box on the form
that is bound to [whelped date], and hence in the table.
The user is then free to modify.

Regards,

B
-----Original Message-----
Hi,

I seem to have forgotten how to handle a situation on a data entry form. I
have a textbox, Me.[Delivery Date] that is based on a calculation using a
value in the Me.[Whelped Date] textbox. The expression is : =[Whelped
Date]+63-Weekday([Whelped Date]+56,5). Currently, I have the Control source
of Me.[Delivery Date] equal to this expression.

The problem is the user wants to be able to edit the amount that is
automatically entered in the Me.[Delivery Date] control as once in a great
while they must change this date from what is actually calculated. I realize
that one must not store calculated data in a table. Therefore, how do I go
about making this control editable and still keep track of what the
[Delivery Date] value is if it is edited on the form but the value is not
recorded in a table?

Would appreciate any suggestions or advice on this problem.

Thanks,

Joan


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top