Summing Text boxes in Form (apologies) 2

[WillShakespeare]

All,

I have doene a quick search, and I know a few people have had the same trouble I am having, but a solution is still forthcoming. I wonder if you could help?

I have a DB that stores Products and Jobs (and customers), and on my Job form is a subform for Products. It all works well except that the price on the Job form is actually worked out by multiplying the price field for the product by the Area (or Perimeter, using IIF) field in Jobs.

The price for each record is perfect in the box assigned. However, when I try to SUM the totals from this text box on each record, I get an error.

As far as I can tell, this is because my textbox has a calculation in it. This is fine, but I need to figure this out, and quick!

I tried adding a hidden text box that is simply set as "=[txtPrice]" from the form, and then the total box is "=Sum([txtTempPrice])" but again withthe error.

I have tried adding the IIF nto te sum, and that also doesn't seem to work.

The probelm is the price on the form i not from a DB field, else I could just sum the fields. Plus, I cannot see an easy way to add a new colum to any of the tables to hold the price for a specific job, because its for the products, which each is different for each job!

Any help would be appeciated.

If I could do this with a query or VBA code, fine, but I am fairly new to Access so not sure where to put any code. I have some requery code in one ofmy text boxes (not to d with summing), so have written some code, but that was in a specific AfterUpdate() function.

Please help! I am at a loose end!!

Will

 

[dhookom]

I have tried adding the IIF nto te sum, and that also doesn't seem to work

What did this look like?
What is the actual control source of the Price text box?

Duane
Hook'D on Access
MS Access MVP

 

[WillShakespeare]

Here it is:

=IIf([Job_Prod_Link_Product_ID]>7,[Price]*(1-[dpdDiscount])*[txtUnit],[Price]*[txtUnit])

Will

 

[WillShakespeare]

You should note the If works great in the price box. Essentially only products above ID 7 (8-17) have a discount attached to them. The others don't get multiplied by the discount.

The complexity is the source of my headache! But unfortunately I can't think of another way to work out the price so I can sum the values.

Will

 

[PHV]

What about this ?

=Sum([Price]*IIf([Job_Prod_Link_Product_ID]>7,1-[dpdDiscount],1)*[txtUnit])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WillShakespeare]

Well, while that code is certainly better than my first draft, it essentially does the same thing, I think.

The problem isn't the IIF, it's the fact that for each record on the form, there is a different price in the box. I want to add these prices up in the footer of the form.

I trie dthis, but I don't think I am doing it right or in the right Sub:

Private Sub txtPrice_AfterUpdate()
    total = total + Me.txtPrice.Value
    Me.txtTotalPrice.Value = total
End Sub

 

[WillShakespeare]

Is there a way I can store the contents of txtPrice in a variable for each record (like my code above) and then pass that summed value to the Total textbox on my form?

Will

 

[PHV]

Are you saying that the Price box isn't bound ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[WillShakespeare]

Correct. Because the price shown needs to be "worked on" before displaying it. The price changes depending on the job and measurements.

In the meantime I tired this in Form Load():

Private Sub Form_Load()
    Dim total As Currency
    
    total = total + Me.txtPrice.Value
   
End Sub

This seems to update the "total" variable fine, but I can't find a way to get total to the txtTotalPrice text box on the form...

Will

 

[WillShakespeare]

Hmmm... I think I am getting somewhere, but with the following code I get £0.00 in total price. What is good is I am getting a value! So I wonder if the SumUpPrice() function is flawed. Can functions share variables? Do I need to declare total as Public somehow?

Or could I wrap the whole process into one function. i.e. pass 2 values to the function (current total and new price) add them and return it to a textbox holding current total?

What do you think?

Public Function ReturnTotalPrice() As Currency
    ReturnTotalPrice = total
End Function

Public Function SumUpPrices(Prices As Currency)
    Dim total As Currency
    
    total = total + Prices
    
End Function

 

[WillShakespeare]

Ooh! Making progress! I added the following code, but it oly works as I set focus to each of the records in the products sub form. i.e. the value oftotal is £406.35, and then when I click the next product in the list for that job, the total become 426.35 (assuming 2nd product is £20).

What I want it to do is add them up as they are displayed, not as they get focus. Anyone help?

Public curTotal As Currency

Public Function ReturnTotalPrice() As Currency
    ReturnTotalPrice = curTotal
End Function

Public Function SumUpPrices(Prices As Currency)
        
    curTotal = curTotal + Prices
    
End Function

 

[WillShakespeare]

Anothe interesting affect... if I click on a record in the sub form, the Total becomes the total of every record up to that record. So if I click to the last record inthe subform, I get the correct total. But if I clic on the 1st one, the total is only that record, and then all in between.

How can I get it to be the total of the records without upating!!!

Will

 

[dhookom]

Can you tell us what you mean by

"worked on" before displaying it. The price changes depending on the job and measurements.

Given what is available as fields in your record source and possibly related tables, how do you calculate the price?

BTW: this thread isn't sufficiently dormant that you would need to create a new thread with the same question.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I think you may have missed something in the earlier post.

If you have a continous form with an unbound control, you can not sum the control by name.

so lets say you have
txtTempPrice
and it has control source
=IIf([Job_Prod_Link_Product_ID]>7,[Price]*(1-[dpdDiscount])*[txtUnit],[Price]*[txtUnit])

As already shown you can not:
=Sum([txtTempPrice]).
But instead sum the original function
=Sum([Job_Prod_Link_Product_ID]>7,[Price]*(1-[dpdDiscount])*[txtUnit],[Price]*[txtUnit])

That is your issue.

 

[MajP]

http://support.microsoft.com/kb/207763

 

[WillShakespeare]

Hi,

Sorryabout the new thread. I didn't want to muddy the waters with 2 different ideas for soultions. WIth 2 threads I figured it could be attacked on two fronts is all.

Anyway,

The product table has a number of products and their prices.

The Job table has information such as Date, Customer, Sales Rep, Area (with regard to floor space), Permeter (aorund the floor space).

The requirement for the data entry form is to show, for each job, a list of job lines (products) as some customers will have more than others.

The requirement for price is to multpily the price by either Area or Perimeter depending on the type of product (some are priced by metres, some metres squared).

So upon opening my form you see customer and job details an dthen in a subform a list of products for that job. Next to each product is a value for Area or Perimeter. Next to this is a price which is calculated as the unit price * Area (or Perimeter).

A small aside is that if the product is a carpet, then I need to apply a doscount to the price (usually the top product n the list).

So, for example I might have:

Golden Berber Twist 22 £404.25
Gripper Strip 18 £17.98
Underlay 22 £97.86

Here is the code in the text box for the prices above:

=IIf([Job_Prod_Link_Product_ID]>7,[Price]*(1-[dpdDiscount])*[txtUnit],[Price]*[txtUnit])

This says if the product is a carpet, multiply by area and then apply discount. If not, then just multpily by area (or perimeter).

I need a TOTAL text box in the footer of this subform that adds up each of the prices (for example the 3 above).

I can't find a way to do this.

Saying that, the VBA code works a little. I can get a total in my total box using the public function I created, but it only works if you click the last item in the list (Underlay, for example above). If I clik on Golden Berber Twist, thetotal box shows £404.25. If I click on Underlay I get the total of all 3 items. Also, if I click back to Golden Berber Twist after clicking Underlay, the total goes back to £404.25!!

Wha I need is a total that updates on loading a job into the main form, and it stays that wa unless products are added or deleted from the sub-form.

Thanks in advance,

Will

PS - Please feel free to look at the DB:

http://www.mrclements.co.uk/Files/Staff_DB.zip

 

[WillShakespeare]

Hi Maj,

Thanks for your reply, but if I do that in the control I simply get #Error.

What I need is something that works. I am happy to accept you can't sum text boxes, but surely there must be a way of adding these values up.

This DB is part of an A-Level project given to 17 year old ICT students across the UK (two years ago, not current), so it can't be unsolvable.

The problem for me is I am training as a teacher and while I have created DBs in the past, including summing, macros, linked tables, reports and queries, this particular problem has me stumped!!

It is Task 4 in the attached:

http://www.mrclements.co.uk/Files/A-Level.pdf

Will

 

[WillShakespeare]

BTW: This is the reason I started a second thread. Maybe it is not possible the way I am doing it here. I have never had to do this before with Access.

I can sum field values, but I don't know how to update the fields after calculating the Job Price.

 

[WillShakespeare]

Following the link you gave MajP, I tried the following:

TotalPrices: IIf([Job_Prod_Link_Product_ID]>7,[Price]*(1-[Discount])*[Unit_Type],[Price]*[Unit_Type])

But no joy, just #Error on each product.

Any ideas?

 

[MajP]

This is how I did it. To me the calculated controls using complex iif are difficult to use and even harder to error check.

1. Build a query
qryAreaOrPerimeter

SELECT 
 Jobs.Job_ID, 
 Job_Prod_Link.Product_ID, 
 Products.Unit_Type, 
 Jobs.Area, 
 Jobs.Perimeter
FROM 
 Products 
INNER JOIN 
 (Jobs INNER JOIN Job_Prod_Link ON Jobs.Job_ID = Job_Prod_Link.Job_ID) ON Products.Product_ID = Job_Prod_Link.Product_ID;

I use this to get the correct area or perimeter. Your current method is wrong because you do not pass the Job ID into your dlookup. It always returns the first two values.

I build two functions to return the Area/Perimter and one to return the cost.

Notice the use of the jobID

Public Function getAreaOrPerim(areaOrPerimType As Variant, prodID As Variant, jobID As Variant) As Single
  If Not (IsNull(areaOrPerim) Or IsNull(prodID) Or IsNull(jobID)) Then
    Select Case areaOrPerimType
    Case "m"
      getAreaOrPerim = DLookup("Perimeter", "qryAreaOrPerimeter", "Job_ID =" & jobID & " AND product_ID = " & prodID)
    Case "sq m"
      getAreaOrPerim = DLookup("Area", "qryAreaOrPerimeter", "Job_ID =" & jobID & " AND product_ID = " & prodID)
    End Select
  End If
End Function

Public Function getPrice(areaOrPerimType As Variant, prodID As Variant, jobID As Variant, Price As Variant, Discount As Variant)
  Dim areaOrPerim As Single
  If Not (IsNull(areaOrPerim) Or IsNull(prodID) Or IsNull(jobID) Or IsNull(Price) Or IsNull(Discount)) Then
    areaOrPerim = getAreaOrPerim(areaOrPerimType, prodID, jobID)
    If prodID > 7 Then
      getPrice = Price * (1 - Discount) * areaOrPerim
    Else
      getPrice = Price * areaOrPerim
    End If
  End If
End Function

in my area/perimeter box on the form I use the function

=getAreaOrPerim([unit_Type],[Job_Prod_Link_Product_ID],[Job_ID])
This returns the correct area or perimeter

To get the correct price on the form
=getPrice([unit_Type],[Job_Prod_Link_Product_ID],[Job_ID],[price],[Discount])

To sum this up on the form
=Sum(getPrice([unit_Type],[Job_Prod_Link_Product_ID],[Job_ID],[price],[Discount]))