STORE CALCULATIONS

[Millisa Eubanks]

Ok I know everyone is going to say you should not store calculations, but I
have no choice!!!!!!!!!! I have the form ORDERFORM (recordsource ORDERS
table) with a subform ORDERFORMsubPROD (recordsource ORDERSPROD table).
Essently theses 2 forms togather allow me to place an order for multiple
products and store them in the ORDERS (key: ORDERID) and ORDERSPROD (foregin
key: ORDERID) tables. The ORDERFORM includes information such as an ORDERDT
and the ORDERFORMsubPROD includes a sum of cost the products ordered which is
shown in ORDERFORM with a boundcontrol = to the ORDERFORMsubPROD control that
does the actual calculations.

Next I need to be able to pull specific informtaion from the ORDERS table
and group it togather by the SALESMANID. To this end I have a form SALESMAN
with a subform SALESMANsubORDERS that displayes the ORDERID, ORDERDT, and the
calculated sum of the products ordered on that ORDERID based on the
SALESMANID of the order.

So far everything stated above is correct and should not require that I
store calculations. My problem comes in when I am trying to make a form that
will filter this information based on user supplied criteria (my user does
not want to have to use the filer buttions supplied by Access). I'm using
DoCmd.OpenForm and setting my WHERE criteria based on a bunch of If .. Then
statements that check the various filter text/combo boxes to determine if
they are populated and then admend the WHERE statement accordingly. At
least that is how it works on a very similar form that filters ORDERFORM. I
have discovered that because the subform, SALESMANsubORDERS, is based on a
query that includes calculations the WHERE statement needs to be phrased as
HAVING. I can not figure out how to get Access to comply with this so all I
can think of is to store the calculated values (and be able to allow them to
be updated later) then pull them directly from the ORDERS table.

Anyone who can write precise/generic code and most importantly tell me where
(what event) that code should be placed on please let me know. This is the
first time I have run into this problem and it is really my last problem
before completing a DB application that needs to be completed by Wed
1/19/2005. Thanks in advance for any advise/help I receive.

 

[Eric Schittlipz]

Ok I know everyone is going to say you should not store calculations, but
I
have no choice!!!!!!!!!! I have the form ORDERFORM (recordsource ORDERS
table) with a subform ORDERFORMsubPROD (recordsource ORDERSPROD table).
Essently theses 2 forms togather allow me to place an order for multiple
products and store them in the ORDERS (key: ORDERID) and ORDERSPROD
(foregin
key: ORDERID) tables. The ORDERFORM includes information such as an
ORDERDT
and the ORDERFORMsubPROD includes a sum of cost the products ordered which
is
shown in ORDERFORM with a boundcontrol = to the ORDERFORMsubPROD control
that
does the actual calculations.

Next I need to be able to pull specific informtaion from the ORDERS table
and group it togather by the SALESMANID. To this end I have a form
SALESMAN
with a subform SALESMANsubORDERS that displayes the ORDERID, ORDERDT, and
the
calculated sum of the products ordered on that ORDERID based on the
SALESMANID of the order.

So far everything stated above is correct and should not require that I
store calculations. My problem comes in when I am trying to make a form
that
will filter this information based on user supplied criteria (my user does
not want to have to use the filer buttions supplied by Access). I'm using
DoCmd.OpenForm and setting my WHERE criteria based on a bunch of If ..
Then
statements that check the various filter text/combo boxes to determine if
they are populated and then admend the WHERE statement accordingly. At
least that is how it works on a very similar form that filters ORDERFORM.
I
have discovered that because the subform, SALESMANsubORDERS, is based on a
query that includes calculations the WHERE statement needs to be phrased
as
HAVING. I can not figure out how to get Access to comply with this so all
I
can think of is to store the calculated values (and be able to allow them
to
be updated later) then pull them directly from the ORDERS table.

Anyone who can write precise/generic code and most importantly tell me
where
(what event) that code should be placed on please let me know. This is
the
first time I have run into this problem and it is really my last problem
before completing a DB application that needs to be completed by Wed
1/19/2005. Thanks in advance for any advise/help I receive.

I still don't think you've argued the case that you *must* use stored
calculations - in fact, I'm still not sure what sort of question you're
trying to answer. Typically, you might have a query like 'show me all the
orders which have 5 or more line items' which could be done without storing
any calculations - just use a query which you could sort/filter by. What
are you trying to filter?

 

[Millisa Eubanks]

Sorry but your answer was worthless. A) it should not matter why I say I
have to store the calculations either you know how to or you don't and thats
the information I am looking for. B) I explained that inorder to get the
application to work right I need to beable to enter criteria in with one form
and then use the WHERE function of DoCmd.OpenForm to specify the parameters
for the query underlinining the sub form but that is not working because the
subform's query has calculated fields and therefore changes the snytax of
what I am filtering by from WHERE to HAVING which is not supported by
DoCmd.OpenForm.

I'm sorry if this seems a little rude but I am under the gun and have tried
everything I can get my hands on or think about and being questioned as to
why I need to do what I said I NEED to do is frustrating!

 

[Rob Oldfield]

What Eric said is quite right. And given your limited explanation of your
problem it was a sensible question. What I think he's thinking of is to
extend the criteria of your form down to the SQL behind your subform. And
to do that we'd need to know what kind of criteria you're talking about.
Which you don't mention. Not his problem.

The quick and dirty solution to your problem, by the way, is to run a make
table query to create your figures, then open your form based on that table.
Lesson 2 is to not agree to deadlines you can't keep to.....

(Going to bed now. I'll give you my mobile number if I can guarantee $400
per hour... as opposed to the free advice you seem to be expecting.)

 

[Millisa Eubanks]

Like I stated before, I already have a query that creates the sums I need.
What I can't do is filter by the information it produces with a WHERE
statement because it has group by in it (due to calculating sums) and
therefore it requires HAVING to be the limiting statement not where.
Unfortunatly, Access limits the criteria you can place with OpenForm to
WHERE.

Point blank if you can not answer the question on how you store calculations
BY INSERTING THE VALUES THEY RETURN INTO THE UNDERLYING TABLE then please do
not respond to this post. Thank you.

 

[John Vinson]

Point blank if you can not answer the question on how you store calculations
BY INSERTING THE VALUES THEY RETURN INTO THE UNDERLYING TABLE then please do
not respond to this post. Thank you.

Use the BeforeUpdate event of the Form to push the calculated
textboxes' values into bound textboxes:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Me!txtBoundFieldA = Me!CalcFieldA
Me!txtBoundFieldB = Me!CalcFieldB
<etc>


John W. Vinson[MVP]

 

[WSF]

Hello John,
You are too kind.
This fellow seems not to understand or appreciate the charity of this
newsgroup - a wonderful resource given to by people like you.

Impatience with those freely offering their contributions in good faith
is very bad manners!

Why reward bad behaviour I seem continually to ask myself these days.
But then I'm no new age sensitive guy - bit of an old fart I suppose.

WSF

 

[rkc]

(Going to bed now. I'll give you my mobile number if I can guarantee $400
per hour... as opposed to the free advice you seem to be expecting.)

At $400 per hour I can guarantee he'd be getting f*cked.
Everyone who posts a question here is expecting free advice.

 

[Eric Schittlipz]

Sorry but your answer was worthless. A) it should not matter why I say I
have to store the calculations either you know how to or you don't and
thats
the information I am looking for. B) I explained that inorder to get the
application to work right I need to beable to enter criteria in with one
form
and then use the WHERE function of DoCmd.OpenForm to specify the
parameters
for the query underlinining the sub form but that is not working because
the
subform's query has calculated fields and therefore changes the snytax of
what I am filtering by from WHERE to HAVING which is not supported by
DoCmd.OpenForm.

I'm sorry if this seems a little rude but I am under the gun and have
tried
everything I can get my hands on or think about and being questioned as to
why I need to do what I said I NEED to do is frustrating!

You can use 'having' as part of your 'where' clause. For example, in the
Northwind example, if you wanted to open the orders form showing only orders
with more than 5 lines, then you could use the code shown below. It would
be silly to store a field Orders.LineQty to hold the number so it could be
used to filter/sort.

strWhere = "[Orders].[OrderID] IN " & _
"(SELECT [Order Details].[OrderID] FROM [Order Details]" & _
"GROUP BY [Order Details].[OrderID]" & _
"HAVING Count([Order Details].[ProductID])>5)"
DoCmd.OpenForm "Orders", , , strWhere

If I had to store a calculated number in the orders table, then I might have
to put code in more than one place since I could be editing, inserting and
deleting records and all of these separate events may need to update the
calculation.

By the way, if you are unhappy with the standard of answer you receive in
this group, please feel free to e-mail (e-mail address removed).
Alternatively you could go screw yourself.

 

[John Vinson]

Why reward bad behaviour I seem continually to ask myself these days.
But then I'm no new age sensitive guy - bit of an old fart I suppose.

Well, I'm a bit of an old fart myself (58) - in fact my attitude (I
think) predates the tectonic social shift in the last half of the 20th
century. My attitude is more based on my upbringing: "a soft answer
turneth away wrath" comes to mind.

John W. Vinson[MVP]