change recordsource of a textbox

[larpup]

I need the ability for a user to either accept the calculation into a
texbox, or, enter an number not based on a calculation into the
textbox.

My thinking is,

1. Create a textbox with a calculation
2. Make a checkbox next to it (called Manual Entry)
If the user enters into the textbox he can accept the calculation, If
he clicks on the checkbox, it wil change the recordsource of the
textbox to a field in a table. He can then enter data into it.

The field (or calculated texbox) will be used in many calculation re:
reports. I was thinking that if the calculation is accepted, use the
After_Update event to make the field equal to the textbox.

Is this a viable way for me to resolve my problem.

Lar

 

[Allen Browne]

If the field needs to be editable, it must be stored in a table.

You can use the AfterUdpate event of each of the controls it depends upon to
write the calculated value into the control and then the user can edit the
value if needed.

Example in the second part of:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

 

[larpup]

Allen,

Thank you very much for your reply. Understood. But,, big but...
What happens if the calculation is based on several fields and a
function.

Say
Zip Code (Origin)
Zip Code (Destination)
Weight
Service Level (Overnight, 2Day, 3Day or 3-5Day)

and a function that lookups a table with
Origin&Destination&ServiceLevel as the key.

How would one approach this?

Regards and thanks again,

Lar

 

[larpup]

Allen,

Thank you very much for your reply. Understood. But,, big but...
What happens if the calculation is based on several fields and a
function.

Say
Zip Code (Origin)
Zip Code (Destination)
Weight
Service Level (Overnight, 2Day, 3Day or 3-5Day)

and a function that lookups a table with
Origin&Destination&ServiceLevel as the key and and returns a price to
the field (that may need be changed)?

How would one approach this?

Regards and thanks again,

Lar

 

[Allen Browne]

Call the function in the AfterUpdate event of each control that this
calculation is based on.

 

[larpup]

Thank you very much... and I will!

Lar

 

[larpup]

Allen,

Have a small problem with a ComboBox. (The ComboBox call a function for
the calc in the after event). Upon entering the ComboBox, I receive a
runtime error 94 "Invalid use of null", prior to entering any
information in it. Why do I receive this error. Should I just trap it
and "on error (94) goto next"?

 

[larpup]

The after update event does not work upon entry of a new record. Where
do I call the function to work upon entry of a record? Lost Focus?

Had some other problems re: error 94 but took care of that with
..newrecord.

I have 3 calculated fields. The last is determined by the second. The
second works upon modifying a record, however, since that field is not
"manually" updated, the 3rd calculation won't work. It is burdensome
for the user to manually reenter the data (which does recalculate the
data in the 3rd field. Would a Me.Refresh be appropriate at this time?

A little confused on how to make this work. Would greatly appreciate
your advice.

Lar

 

[larpup]

The after update event does not work upon entry of a new record. Where
do I call the function to work upon entry of a record? Lost Focus?

Had some other problems re: error 94 but took care of that with
..newrecord.

I have 3 calculated fields. The last is determined by the second. The
second works upon modifying a record, however, since that field is not
"manually" updated, the 3rd calculation won't work. It is burdensome
for the user to manually reenter the data (which does recalculate the
data in the 3rd field. Would a Me.Refresh be appropriate at this time?

Should the function all be called from the "lost focus" event as well
as the AfterUpdate?

A little confused on how to make this work. Would greatly appreciate
your advice.

Lar

 

[Allen Browne]

Are you using the AfterUdpate event of the *controls* (not the form)?

The Null error means your function is using data types other than variants.
Only the Variant can be Null.

Refresh will not help: you need to get this calculation written *before* the
record is saved.

 

[larpup]

Allen,

Yes I am using the AfterUpdate on the controls (not the form). I wrote
some code to test for new record and I also have a checkbox which locks
the textboxes (which I test for). That seems to work.

My problem is, is that I need to arrive at the 3 field calculations
upon entry and one the the textboxes (dependent on the calc) is
calculated! (this is not a field, it is just a textbox) so, there is no
AfterUpdate.

Lastly, the 3rd Calculation is based on the second and right after it
so it will only work with the Lost Focus event after tabbing thru the
field prior.

It is not asking too much of the user to tab thru these fields as they
appear "like an invoice, itemized that is".

I read your artice and you "hit the nail on the head". We need these
three fields "to win the bid"...

Thank you for all your help. If you could assist me nailing this down,
will greatly appreciate it.

Lar

 

[Allen Browne]

I am not following you:
- A text box has an AfterUpdate event, regardless of whether it is bound or
unbound.

- The LostFocus event makes no sense to me: if the entry was not changed the
code does not need to run.

- I also don't follow the logic of the assumption the user will never skip a
field by clicking somewhere else with the mouse.

As an example, say you have 4 text boxes bound to fields of type Number.
They are:
- Length
- Width
- Height
- Volume

Assuming that Volume should sometimes NOT be the product of the other 3
(perhaps dented containers), and you want to call a function, the event
procedures would look like this:

Private Sub Length_AfterUpdate()
Me.Volume = CalcVolume(Me.Length.Value, Me.Width.Value, Me.Height.Value)
End Sub

Private Sub Width_AfterUpate()
Call Length_AfterUpdate
End Sub

Private Sub Height_AfterUpate()
Call Length_AfterUpdate
End Sub

Function CalcVolume(varLength As Variant, varWidth As Variant, varHeight As
Variant) As Variant
If IsNull(varLength) Or IsNull(varWidth) Or IsNull(varHeight) Then
CalcVolume = Null
Else
CalcVolume = varLength * varWidth * varHeight
End If
End Function

 

[larpup]

Allen,

I have quite a few "default calculations on the form"

I use the above (similar) function to determine Dimensional Weight.
This can be changed so it must be stored. Lastly we have chargeable
weight which is the greater of the Dimensional and Actual but again,
can often be changed as well.

Utilizing your AfterUpdate, my transporation charges are working
perfectly, however, the FuelSurCharge is not because it is based up the
transportation charges and the CustomerId. So if the Chargeable weight
changes, the transportation charges change, but the FuelSurCharge does
not. (maybe because it is not based upon the Chargeable Weight?)

I will check this out this weekend and reply. Lastly, what is the
purpose of using a variant in your function instead of integers?

Lar

 

[larpup]

Allen,

I isolated my problem. The textbox (unbound) which the calculation is
dependent on is a calculation. The calculation changes when either the
Actual Weight or the Dimensional Weight is changed. Is this still
approachable?

Lar

 

[Allen Browne]

If this text box is a calculation, you mean it is bound to an expression
such as:
=[Quantity] *[PriceEach]

If so, and you are passing the name of this text box to your function, the
calculated value may not be up to date when the function is called. You need
to change the function so that it accepts the original values (Quantity and
PriceEach in this example) instead of the potentially out-of-date calculated
control.

The reason for having the function accept Variants instead of integers is
because some of the controls may not have any value yet. If they are null,
you must use a variant, as we discussed earlier in this thread.

 

[larpup]

Allen,

Thank you for all your help. I really appreciate it and I have applied
what you have taught me.

Lar