#error on field calculating value

[burrina]

I have 2 fields on my form that perform a add and a multiply. I tried the below code to make them show a zero value, no luck.

SetValue [Text480] = Nz([Text480], "0") ' tried this code to make it be a zero value if is null

=[Disc#]*[SumTotal1] 'multiplies discount by total and it gives me a #error in the field.
=(Nz([Text480],0)) ' Default Value. Tried to make it show zero if no value



Text478 a textbox uses this control source =Sum(Nz([DiscRate],0)*Nz([SumTotal1],0))
Text480 a textbox uses this control source =Nz([DiscRate]*[SumTotal1],0) ' Both show a #error in the fileds

[JoeM]

Are [DiscRate] and [SumTotal1] bound fields or other fields calculated on the Form?
If they are bound fields, is your Form bound to the table/query where they reside?
What happens if your try to just show those fields, as is, on your Form?
If either [DiscRate] or [SumTotal1] return an error by themselves, then you probably have a reference error (unless those fields themselves are calculations with errors in them).
NZ only addresses NULL situations, it does not address errors.

[burrina]

DiscRate is a bound control, SumTotal1 has its control source set to =[SumExpTotCostNoTx]+[SumExpTax]-[Discount]

My freight and Discount Amount fields are unbound fields. I am not sure how to make the unbound field labeled Discount calculate the DiscAmount (which is a per cent amount) I tried using a couple of other unbound controls and then have the unbound field Discount use that as a control source but this is where I get the #error message
If I manually put in an amount everything works fine. Both Freight and Discount have a default amount of 0

What should I do to fix this?

[JoeM]

DiscRate is a bound control, SumTotal1 has its control source set to =[SumExpTotCostNoTx]+[SumExpTax]-[Discount]

So, if you return SumTotal1 by itself to the Form, does that work?
Or does that return an error? If it returns an error, keep on breaking it down until you find which component is causing the error.

In working with unbound fields, you need to be careful and make sure you format them properly, or else they may default to a "Text" format, which would cause all sorts of problems for your calculations.

[burrina]

Ok, Freight is now bound and also the Discount amount. Please see attached database. Look at record source for main form TimeCards and also record source for the subform TimeCardCatAndProdSub

I cant figure a way to make my Discount Amount, i.e. OrderDiscount (bound field) multiply by the total order, i.e. (name of control) SumTotal1 which has its control source set to: =[SumExpTotCostNoTx]+[SumExpTax]-[Freight] coming from the sql statement for the subform TimeAndCatProdSub on main form, TimeCards.

[rpeare]

Can you make a copy of your database, take out any data that's privelidged, just put in a data set that duplicates the error. Compact and repair your copied database, then zip it up and upload it. Without knowing what your fields are it's going to be hard to help.

[burrina]

@Trakr Purchase Order Module.zip: Upload of file failed.


It seems I can't upload the db, maybe I have angered the Access God's! I compiled it, removed all private info and zipped. No Luck ?

It is 4.47mb Maybe that is why???

[burrina]

Reduced size.

[rpeare]

Ok I have the database, tell me what to do to duplicate your error.

[burrina]

I made a crude attempt at fixing it by adding the unbound controls you see on the TimeCards form. They are labeled Text491 and Text493. Since my brain would not let me figure out another way I made them use the value and then refer to the TextBoxes for the sub-total and grand total so it would work. I use a lot of Exp calculations for the subform TimeCardCatAndProdSub The AddProducts form use ONE category for ALL UnTaxable items, all others are taxable.

I want to make sure that when it comes time for my reports to calculate the taxable,non taxable items it is not a nightmare. Such things as freight and discounts will also be needed at some point. Am I doing this right? Do you understand my poor logic? If you remove the unbound textboxes, you WILL get errors.

[rpeare]

ok you're referring to field names but it would be a lot easier if you could tell me what you want your desired result to be. Which fields (use the labeles on the form itself not the design view so I don't have to go hunting for them)

Just a couple of notes here.
1. You're using special characters in your field names one of them is named % 1A on your form. These special characters in ANY object/field name in access are going to cause you problems I would strongly advise you NOT to use them. And where you must use a space use an underscore (_) to indicate a space it will make all your programming a *lot* easier as you move forward.
2. Your orders should actually be 2 tables. One table for the ORDER related information (customer, shipping address, etc). A second table to hold all the products ordered. The way you have it set up you can only have one product per order or you have to repeat the same order information on each line to get it to work properly which is a tremendous waste of space and time.
3. Your bound column for your product (Named PROD I think) should be the PRIMARY KEY of your products table, NOT the product name. You are going to get inconsistent results if you don't use the primary key field.

The good news is that you are indicating in your PRODUCTS which items are taxable and which aren't so it should be simple enough to separate them on any report/form you design. So do you want the discount to only apply to the TAXABLE items or to both taxable and non-taxable products?

[burrina]

YES, Both I think. When on the main form TimeCards and I enter a Discount PerCent via the dropdown combo Disc % then it should add this calculated value to the OrderDiscount, i.e. Text489 right next to the Disc %

Right now I use 2 tables for my product results. table Categories and table Products. So I can see all products for each category. I get the shipping details and so forth from the table TShippingInformation. It is used as a subform on the customers form also. I appreciate the advice though. On your point number 2, this is the reason I chose a continuous form so many products can be added per order, only limited by category. I suppose I could get really tech and have it show me a multi dropdown list and choose many at a time. Dont know if I am smart enough for that. Point 3, Product ID is set as the primary key in the Products table. Categories use Category ID as its primary key.

Hope I am making sense here.

[burrina]

I have given some thought to this Multi Item select and decided to try and make it happen. I dont know if this how it would best be accomplished but it is my thinking at present. I am thinking that I should have a way both to select an entire category or select many items from each product list.

What I have done so far is add a field to the Categories table and the Products table that has a Yes/No value. If this value is set to yes, then you can select multiple products or even an entire category so you can enter them on your Purchase Order form.

I created 4 macros that either select all or deselect all. 2 for the Categories form and 2 for the Products form.

After all this talk, here is my challenge. On my main form named TimeCards my subform exists which is named TimeCardCatAndProdSub. Using this subform is how you add items to your Purchase Order. I have a command button on the form to add each item, it is called Command19 and named Add Item.

Can you help me with this? I have created a popup form to select or deselect all or no Products. I have not tested out the macro on the AddProducts form but know it will work on the popup form.

Thanks,

Dave

[rpeare]

Without seeing what you changed in comparison to your first sample not really. Did you correct your purchase order structure so you had a main table for the purchase order level information and a subtable for the products related to that order?

[burrina]

Not that it will help, but here is the rest of it without the reports, since they are a mess and not finished.