Can I overwrite a calculated query field?

[Robyn H.]

PLEASE HELP ME! I have been tasked with designing an
Access database to track our firm (Broker/Dealer)'s
trading activity. Here's my question:

If you design a query that includes (for example)
an "Execution Charge" expression, (in my case being a
function of the total shares executed in a trade
multiplied by a predetermined default execution charge
rate) is there a way to be able to OVERWRITE the default
value provided by this calculation? In other words, if
you traded 100 shares and the default execution rate was
0.03/share, the expression would automatically calculate
$3.00, but if the ACTUAL charge ended up being $2.50
would you be able to simply go and enter this data in in
lieu of the calculation? I can't seem to figure this
out. Maybe someone can help me?

Thanks!

 

[Ken Snell [MVP]]

You cannot overwrite the value provided by a calculated field in a query (or
a calculated control in a form, for that matter). If you're using the query
to display information on a form, there are ways to let a textbox display
the result from the query's calculated field but let the user type a
different value into the textbox to replace the query's value. You'd need to
use VBA or macro to do this.

Can you give us more info about what you want to do and where?

 

[Robyn H.]

Hi -- thanks for clearing that up for me -- essentially,
here is what I'm trying to do:

When we receive an order to execute a trade, we have I
believe 4 different options as to where we can send the
trade for ultimate execution (e.g. New York Stock Floor,
American Stock Exchange, etc.). We send the trade down
as we determine, and it gets filled. Each of these
places I mentioned charges an "execution charge", which
is usually a set rate per share of stock traded (with
several exceptions.) As such, in Access I was trying to
set it up so that Access could calculate the charge based
upon a pre-entered default rate per share for each
trade. However, as I mentioned, sometimes for various
reasons the rate is either adjusted or no execution
charge is levied at all. In this case I would not be
relying on the calculated field, but simply entering a
value as I had received from the particular entity
handling the trade.

I'm trying to figure out what the best way to go about
this would be in Access, as I am trying to make data
entry as easy and automatic as possible for our traders.
Does this make any more sense to you? Or do you need
more info.? I really appreciate your help.

 

[Ken Snell [MVP]]

You could use either a Default Value for that control (the control that is
bound to the field that will store the "execution charge" in your table)
which could be a calculation based on what type of trade it is, or you could
use VBA code or macro that calculates the value for a new record (you could
use the Current event and test to see if the record is a new record or not)
and writes it into that control. Either way, the user can overwrite the
value.

 

[Robyn H.]

Hi again -- thanks! So I was talking to a colleague that
was familiar in using the database at our former firm --
and he said that the way they did it was that in the form
for Trade Entry there would be text boxes -- one
for "execution" which was linked to a default rate times
the number of shares -- and then there was a "NEW
execution" box, which the user would generally enter
manually, based on the value appearing in the "execution"
box, but that they could CHANGE this box after the fact
if the execution varied from default value. It seems
that THIS was the box that was tied into the execution
charge query.

As such -- how would one go about creating a form -
specific "control box"? (I think?) that would calculate
and show the default amount, without having that tied to
the query to be run for actual execution charges? I'm
not very familiar with VB or macros; is there a "dummy-
friendly" way to accomplish this? I have a Microsoft
Access Fast Track 2003 book but I'm not sure where to
look for this advice.

Again, I really appreciate your help on this!

 

[Ken Snell [MVP]]

If all you want is to set the Default Value of a textbox on a form, open the
form in design view, click on the textbox control that you want to do this
for, click on Properties icon on toolbar, click on Data tab, and enter the
value into the box next to Default Value.

 

[Robyn H.]

Thanks for everything Ken.

-----Original Message-----
If all you want is to set the Default Value of a textbox on a form, open the
form in design view, click on the textbox control that you want to do this
for, click on Properties icon on toolbar, click on Data tab, and enter the
value into the box next to Default Value.

--

Ken Snell
<MS ACCESS MVP>




.