Textbox based on Calculation

  • Thread starter Thread starter Joan
  • Start date Start date
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
 
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
 
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
 
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
 
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


.
Click to expand...
Click to expand...


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
 
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


.
Click to expand...
Click to expand...
 
Back
Top