Calculations/Forms/Tables

[Nick Toye]

I have a function on a form that calculates a price based on information entered into a weight text box.

The function works well, and the information is calculated in the form, and when I review the records in the forms the information in the price cell is ok.

When I look in the table that the form is based on, it has all the other information that I put in but it doesn't have the price that was calculated.

Does anyone know why.


Many thanks in advance.

 

[Fizzio]

Probably because the textbox is unbound. Do you really need to store the calculated value as you have already shown that you can calculate it.
Unless you need to store an audit trail eg if Tax rate changes etc, it is normally not necessary to store calculated values.

 

[Nick Toye]

Well basically I need to generate an invoice based on all the delivery notes I enter into the form. I have the query set up so I can choose the customer I need to invoice and I can choose the dates, but when I view the report it shows all the delivery notes in the dates I specify but the value is showing zero.

 

[R. Hicks]

You should not store the calculated result ....

The proper thing to do is to store the qty and the "price at the time of the transaction" in your main record ... then you calculate the result when needed dynamically.

This also goes for a "Tax Rate" ... you simply store the "Tax Rate at the time of the transaction" in the main record ... and do not store any calculated result.

HTH
RDH

 

[Nick Toye]

Well where do I store the price, it doesn't show in the table, it only shows on the form, but I want the calculated price to be displayed in the table and the query so I can print it off when I want to. There must be a way

 

[Rich]

Re-create the calculation, either directly on your Report or in the query
=[Weight]*[UnitCost] as the control source for a textbox on the report or TotalCost:[Weight]*[UnitCost] in a query

 

[Nick Toye]

Where do i put it on the query?

The function I use is:

=GetCost([Zone],[Weight])

My Module that I use is:

Function GetCost(ByVal intZone As Integer, ByVal intWeight As Integer) As Currency

If IsNull(intZone) Then
GetCost = 0
Exit Function
End If

If IsNull(intWeight) Then
GetCost = 0
Exit Function
End If


Select Case intZone
Case Is = 1
Select Case intWeight
Case Is <= 30
GetCost = 5.5
Case Is < 100
GetCost = 5.5 + ((intWeight - 30) * 0.2)
Case Else
GetCost = 19.5 + ((intWeight - 99) * 0.18)
End Select

Case Is = 2
Select Case intWeight
Case Is <= 20
GetCost = 5.5
Case Is <= 99
GetCost = 5.5 + ((intWeight - 20) * 0.22)
Case Else
GetCost = 23.1 + ((intWeight - 99) * 0.2)
End Select
Case Else
GetCost = 0
End Select

End Function

Into this function I want to add a pallet cost for each zone, this equates to £50 in zone 1 and £58 in zone 2.

When I put this in my report text box for price, it asked me for what zone and what weight, well I don't want it to ask me that information, just the information i specified in my query, which is the dates beginning and end.

Thanks again for help, much appreciated.

 

[Rich]

You have to include [Zone] and [Weight] along with other relevant information in the Reports underlying query

 

[Nick Toye]

What does Me. mean, what does it refer to exactly, sorry i'm a real beginner at database work. Thanks for your help so far.

 

[Nick Toye]

Well i get the following error.

"The expression BeforeUpdate you entered as the event property setting produced the following error: The object doesn't contain the Automation object 'Me.'."

 

[Nick Toye]

How do I update the query?

 

[Fizzio]

The code Pat posted is meant to go in the VBA sub for that control, not in the actual Before_Update box. Make sure the Before_Update event reads [event procedure] and enter the code in the VBA window.

 

[Nick Toye]

Ok, i'll give that a go.

 

[Nick Toye]

I'm really sorry, you have to tell me like i'm a six year old because I'm having real trouble.

Whats the vba sub?

Where do I enter [event procedure]?

 

[Nick Toye]

Ok, sorry about multiple posts,

Anyway, that seemed to get rid of the error message, but how do I update the query recordset for the form.

Thanks for your patience

 

[Mile-O]

Just a link to the other thread.

 

[Nick Toye]

What does that mean?

 

[Mile-O]

Doesn't do anything - just lets those helping, if they don't know already, get an overview of how the question has developed over the dual posting.

 

[Nick Toye]

Sorry I didn't mean to confuse anyone, I just thought that my question had become relevant to forms so I put in that forum. My mistake. I think that i'm nearly there now, i've learnt quite alot about access. I just don't understand how I update the query.

 

[Nick Toye]

Is it in the SQL View?