Calculations/Forms/Tables

Nick Toye

Registered User.
Local time
Today, 22:38
Joined
May 21, 2003
Messages
22
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.
 
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.
 
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.
 
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
 
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
 
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
 
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.
 
You have to include [Zone] and [Weight] along with other relevant information in the Reports underlying query
 
This is one of those calculations that must be stored. The price may change over time if the weight breakdown changes or the price for a given weight changes. Therefore, the price needs to be stored when it is calculated. If it is not stored, reporting will be incorrect if the price changes in the future.

In the BeforeUpdate event of the form:

Me.TotalPrice = GetCost(Me.[Zone], Me.[Weight])

Just add a field called TotalPrice to your table and don't forget to update the query you are using for the form's recordsource.
 
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.
 
Me. is used to refer to the active form or report. By qualifying your field names, you help Access to parse them more quickly.
 
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.'."
 
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.
 
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]?
 
Open the properties dialog for the FORM.
On the Events tab, find the BeforeUpdate event and press the builder button to the right of the field. The three dots show up when you touch the right edge of the box.
You will have three options. Choose the third option which creates a VBA sub.
Type the line of code in the sub that the builder created.
Save and close.

Also, please don't make multiple posts on the same topic. You are confusing yourself and wasting the time of the people trying to help you.
 
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
 

Users who are viewing this thread

Back
Top Bottom